Exportando consultas para documentos XML

Boa Noite Pessoal,

Essa será possivelmente uma das minhas últimas postagens de 2011, então vamos para uma dica rápida. Hoje falarei sobre como exportar uma tabela ou consulta para um documento XML através do BCP e do CLR. Já vi algumas postagens que tratam esse assunto, mas proponho um pouco mais que isso. Demonstrarei a exportação mantendo a identação dos elementos no documento XML. Para começar nada melhor que algumas tabelas de exemplo

— Cria um banco para testes
CREATE DATABASE ExportXML
GO

— Muda o contexto
USE ExportXML
GO

— Cria as tabelas
CREATE TABLE Clientes (
    ClienteID INT NOT NULL,
    ClienteNome VARCHAR(100) NOT NULL)

CREATE TABLE Contas (
    ClienteID INT NOT NULL,
    ContaID INT NOT NULL,
    ContaAgencia VARCHAR(6) NOT NULL,
    ContaNumero VARCHAR(9) NOT NULL)

CREATE TABLE Lancamentos (
    ClienteID INT NOT NULL,
    ContaID INT NOT NULL,
    Valor SMALLMONEY NOT NULL,
    Tipo CHAR(1) NOT NULL,
    Data DATE NOT NULL)

— Insere os registros
INSERT INTO Clientes VALUES (1,‘Ronaldo’)
INSERT INTO Clientes VALUES (2,‘Adalton’)
INSERT INTO Clientes VALUES (3,‘Daniela’)
INSERT INTO Clientes VALUES (4,‘Roberta’)

INSERT INTO Contas VALUES (1,1,‘3590-4’,‘97532-8’)
INSERT INTO Contas VALUES (1,2,‘3590-4’,‘856413-0’)
INSERT INTO Contas VALUES (1,3,‘3590-4’,‘948766-0’)
INSERT INTO Contas VALUES (2,1,‘1004-9’,‘974322-8’)
INSERT INTO Contas VALUES (2,2,‘1004-9’,‘15649-X’)
INSERT INTO Contas VALUES (3,1,‘2944-0’,‘7561-2’)

INSERT INTO Lancamentos VALUES (1,1,6500,‘C’,‘20111220’)
INSERT INTO Lancamentos VALUES (1,1,1200,‘D’,‘20111221’)
INSERT INTO Lancamentos VALUES (1,1,2500,‘D’,‘20111221’)
INSERT INTO Lancamentos VALUES (1,2,3500,‘C’,‘20111221’)
INSERT INTO Lancamentos VALUES (1,2,2600,‘C’,‘20111221’)
INSERT INTO Lancamentos VALUES (2,1,3200,‘D’,‘20111222’)
INSERT INTO Lancamentos VALUES (2,1,1100,‘D’,‘20111222’)
INSERT INTO Lancamentos VALUES (2,1,1900,‘C’,‘20111222’)
INSERT INTO Lancamentos VALUES (2,2,5800,‘D’,‘20111223’)
INSERT INTO Lancamentos VALUES (2,2,3200,‘D’,‘20111223’)
INSERT INTO Lancamentos VALUES (2,2,9800,‘C’,‘20111223’)

Agora que temos uma massa de testes, segue um consulta para estruturar esses dados em XML.

SELECT
    Cliente.ClienteNome As Nome,Conta.ContaAgencia As Agencia,
    Conta.ContaNumero As Numero,Lancamento.Data As Data,
    Lancamento.Valor, Lancamento.Tipo
FROM Clientes As Cliente
    LEFT OUTER JOIN Contas As Conta ON Cliente.ClienteID = Conta.ClienteID
    LEFT OUTER JOIN Lancamentos As Lancamento
        ON Conta.ClienteID = Lancamento.ClienteID AND Conta.ContaID = Lancamento.ContaID
FOR XML AUTO, ROOT(‘Lancamentos’)

Posto apenas um parte do documento XML produzido com esse comando:

<Lancamentos>
  <Cliente Nome=Ronaldo>
    <Conta Agencia=3590-4Numero=97532-8>
      <Lancamento Data=2011-12-20Valor=6500.0000Tipo=C/>
      <Lancamento Data=2011-12-21Valor=1200.0000Tipo=D/>
      <Lancamento Data=2011-12-21Valor=2500.0000Tipo=D/>
    </Conta>
    <Conta Agencia=3590-4Numero=856413-0“>
      <Lancamento Data=2011-12-21Valor=3500.0000Tipo=C/>
      <Lancamento Data=2011-12-21Valor=2600.0000Tipo=C/>
    </Conta>
    <Conta Agencia=3590-4Numero=948766-0>
      <Lancamento />
    </Conta>
  </Cliente>
  <Cliente Nome=Adalton>
    <Conta Agencia=1004-9Numero=974322-8>

Para facilitar os trabalhos, vou encapsultar a instrução SELECT em uma View.

CREATE VIEW vLancamentoXML (ResultadoXML) As
SELECT
    Cliente.ClienteNome As Nome,Conta.ContaAgencia As Agencia,
    Conta.ContaNumero As Numero,Lancamento.Data As Data,
    Lancamento.Valor, Lancamento.Tipo
FROM Clientes As Cliente
    LEFT OUTER JOIN Contas As Conta ON Cliente.ClienteID = Conta.ClienteID
    LEFT OUTER JOIN Lancamentos As Lancamento
        ON Conta.ClienteID = Lancamento.ClienteID AND Conta.ContaID = Lancamento.ContaID
FOR XML AUTO, ROOT(‘Lancamentos’)

Agora façamos uma tentativa de exporar esse conteúdo para um arquivo através do BCP. A instrução abaixo em um PROMPT de comando é capaz de fazê-lo.

BCP “ExportXML.dbo.vLancamentoXML” out “C:\Lancamentos.xml” -T -c

Eis uma parte do resultado do arquivo em XML

<Lancamentos><Cliente Nome=”Ronaldo”><Conta Agencia=”3590-4″ Numero=”97532-8″><Lancamento Data=”2011-12-20″ Valor=”6500.0000″ Tipo=”C”/>

O documento XML foi criado e pode ser plenamente utilizado, pois, é um XML bem formado, mas a identação foi completamente perdida na geração do documento. Se esse XML for processado por uma aplicação, não faz diferença ele estar identado ou não, mas se uma pessoa for abrí-lo, a falta de identação pode ser um grande inconviente.

Uma vez que o BCP foi projetado para exportar texto e não XML, não há parâmetros que possam fazer a identação ficar correta. Então precisaremos dar um jeito do texto já ser exportado de forma identada. Criei aqui uma função capaz de fazê-lo.

CREATE FUNCTION dbo.RetornaXMLGRID (@XML XML)
RETURNS @Registros TABLE (Registro VARCHAR(MAX))
As
BEGIN
    DECLARE
@strXML VARCHAR(MAX)
    SET @strXML = CAST(@XML As VARCHAR(MAX))
    SET @strXML = REPLACE(@strXML,‘<‘,‘+|’)
    SET @strXML = REPLACE(@strXML,‘<‘,‘-|’)

    DECLARE @T TABLE (
        Pos INT IDENTITY(1,1), Elemento VARCHAR(1000))

    INSERT INTO @T SELECT
        node.value(N’fn:local-name(.[1])’, N’varchar(1000)’)
    FROM @xml.nodes(N’//*’) T(Node)

    ;WITH Niveis (Elemento, Nivel) As (
        SELECT Elemento, MIN(Pos)
        FROM @T GROUP BY Elemento)

    DELETE FROM @T
    FROM @T As T
    INNER JOIN Niveis As N ON
        T.Elemento = N.Elemento AND T.Pos > N.Nivel

    DECLARE @i INT = 0
    DECLARE @f INT = (SELECT MAX(Pos) FROM @T)
    DECLARE @Elemento VARCHAR(1000) =

    WHILE @i <= @f – 1
    BEGIN
        SET
@Elemento = (
            SELECT Elemento FROM @T WHERE Pos = @i + 1)

        SET @strXML = REPLACE(@strXML,‘+|’ + @Elemento +
‘ ‘,CHAR(10) + REPLICATE(‘ ‘,@i) + ‘+|’ + @Elemento + ‘ ‘)
        SET @strXML = REPLACE(@strXML,‘+|’ + @Elemento + ‘-|’,CHAR(10) + REPLICATE(‘ ‘,@i) + ‘+|’ + @Elemento + ‘-|’)
        SET @strXML = REPLACE(@strXML,‘+|’ + @Elemento + ‘/-|‘,CHAR(10) + REPLICATE(‘ ‘,@i) + ‘+|’ + @Elemento + ‘/-|’)
        SET @strXML = REPLACE(@strXML,‘+|/’ + @Elemento + ‘-|’,CHAR(10) + REPLICATE(‘ ‘,@i) + ‘+|/’ + @Elemento + ‘-|’)
        SET @i += 1
    END
   
    SET @strXML = REPLACE(@strxml,CHAR(10),‘</e><e>’)
    SET @strXML = ‘<E><e>’ + @strXML + ‘</e></E>’
   
    SET @XML = CAST(@strXML As XML)
   
    INSERT INTO @Registros
    SELECT REPLACE(REPLACE(E.e.value(‘.’,‘VARCHAR(MAX)’),‘+|’,‘<‘),‘-|’,‘>’)
    FROM @XML.nodes(‘/E/e’) E(e)
   
    DELETE FROM @Registros WHERE Registro =
   
    RETURN
END

Agora tentemos o BCP novamente com algumas adaptações:

BCP “SELECT * FROM dbo.RetornaXMLGRID((SELECT ResultadoXML FROM vLancamentoXML))” queryout “C:\Lancamentos.xml” -T -c -d ExportXML

O comando SELECT ResultaadoXML FROM vLancamentoXML é apenas para retornar um texto (ou um tipo XML) no formato de um documento XML. É possível substituir essa parte do comando por qualquer consulta XML. O documento agora veio formatado após abrir o arquivo XML:

<Lancamentos>
<Cliente Nome=”Ronaldo”>
  <Conta Agencia=”3590-4″ Numero=”97532-8″>
   <Lancamento Data=”2011-12-20″ Valor=”6500.0000″ Tipo=”C”/>
   <Lancamento Data=”2011-12-21″ Valor=”1200.0000″ Tipo=”D”/>
   <Lancamento Data=”2011-12-21″ Valor=”2500.0000″ Tipo=”D”/>
  </Conta>
  <Conta Agencia=”3590-4″ Numero=”856413-0″>
   <Lancamento Data=”2011-12-21″ Valor=”3500.0000″ Tipo=”C”/>
   <Lancamento Data=”2011-12-21″ Valor=”2600.0000″ Tipo=”C”/>
  </Conta>
  <Conta Agencia=”3590-4″ Numero=”948766-0″>
   <Lancamento/>
  </Conta>
</Cliente>
<Cliente Nome=”Adalton”>
  <Conta Agencia=”1004-9″ Numero=”974322-8″>

Um teste para garantir que o XML só precisa ser váido por ser feito com outra consulta:

SELECT * FROM dbo.RetornaXMLGRID((
SELECT db.name As banco, t.name As tabela, c.name As coluna FROM sys.databases As db INNER JOIN sys.tables As t ON db.database_id = db_id() INNER JOIN sys.columns As c ON t.object_id = c.object_id for xml auto, elements))

Parte do resultado é exibido abaixo:

<db>
<banco>ExportXML
</banco>
  <t>
   <tabela>Clientes
   </tabela>
    <c>
     <coluna>ClienteID
     </coluna>
    </c>
    <c>
     <coluna>ClienteNome
     </coluna>
    </c>
  </t>
  <t>
   <tabela>Contas
   </tabela>
    <c>
     <coluna>ClienteID
     </coluna>

Acho que já são visíveis algumas limitações desse método. Primeiro porque com tantas conversões, essa alternativa não será escalável quando houver documentos XML muito grandes. Segundo, porque se houver elementos com o mesmo nome pertencentes a níveis diferentes, essa função também irá falhar. Isso torna claro que o T-SQL é poderoso, mas não foi feito para resolver todos os problemas. Tentemos uma abordagem com o CLR para esse mesmo fim.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Xml;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void UspGerarArquivoXML(SqlString cmdSQL, SqlString local)
    {
        SqlConnection sc = new SqlConnection(“Context Connection=true”);
        SqlCommand sCmd = new SqlCommand(cmdSQL.ToString(), sc);

        // Abre a conexão
        sc.Open();

        // Declara um objeto XmlReader
        XmlReader xr = sCmd.ExecuteXmlReader();

        // Fecha a conexão
        sc.Close();

        // Declara um documento XML
        XmlDocument xd = new XmlDocument();

        // Inicia o documento
        xd.Load(xr);

        // Salva o documento
        xd.Save(local.ToString());
    }
};

Agora que o código em C# está pronto, basta colocá-lo em uma pasta qualquer (C:\SQLCLR\UspGeraArquivoXML.cs) e compilá-lo com o comando de PROMPT

“C:\Windows\Microsoft.NET\Framework\V2.0.50727\csc.exe” /out:C:\SQLCLR\UspGeraArquivoXML.dll /target:library C:\SQLCLR\UspGeraArquivoXML.cs

Os próximos passos são habilitar o CLR e importar o ASSEMBLY:

EXEC sp_configure ‘clr enabled’,1
GO

RECONFIGURE WITH OVERRIDE
GO

USE ExportXML
GO

ALTER DATABASE ExportXML SET TRUSTWORTHY ON

CREATE ASSEMBLY ExportarArquivosXML
FROM ‘C:\SQLCLR\UspGeraArquivoXML.dll’
WITH PERMISSION_SET = EXTERNAL_ACCESS
GO

Agora que o ASSEMBLY foi gerado, basta criar a procedure e chamá-la:

— Cria a stored procedure
CREATE PROCEDURE UspGerarArquivoXML
    @cmdSQL NVARCHAR(4000),
    @local NVARCHAR(4000)
As
EXTERNAL
NAME [ExportarArquivosXML].[StoredProcedures].[UspGerarArquivoXML]
GO

Por fim, podemos executar a SP com a consulta e o local do arquivo (é importante que a consulta tenha o nó ROOT e que o local tenha as devidas permissões).

— Exporta o Resultado para XML
EXEC dbo.UspGerarArquivoXML
    @cmdSQL =
    SELECT
        Cliente.ClienteNome As Nome,Conta.ContaAgencia As Agencia,
        Conta.ContaNumero As Numero,Lancamento.Data As Data,
        Lancamento.Valor, Lancamento.Tipo
    FROM Clientes As Cliente
        LEFT OUTER JOIN Contas As Conta ON Cliente.ClienteID = Conta.ClienteID
        LEFT OUTER JOIN Lancamentos As Lancamento
        ON Conta.ClienteID = Lancamento.ClienteID AND Conta.ContaID = Lancamento.ContaID
        FOR XML AUTO, ROOT(”Lancamentos”)’
,
    @local = ‘C:\SQLCLR\LancamentosCLR.xml’

O documento já vem identado, mas visivelmente o esforço é muito menor do que escrever código T-SQL para isso.

[ ]s,

Gustavo

Mapeando dependências de objetos para colunas

Boa Noite Pessoal,

Hoje vi no fórum de SQL Server da Microsoft uma necessidade relativamente comum que é a identificação de que objetos referenciam determinadas colunas de determinadas tabelas. É bem comum perguntarmos sobre essa dependências especialmente quando precisamos eliminar ou alterar uma ou outra coluna e sabemos que essas colunas são referenciadas por diversas views, procedures, functions, etc. O melhor para evitar tais situações é usar a opção SCHEMA BINDING sempre que possível, pois, ela evitará que uma ou outra coluna seja excluída e prejudique os objetos que delas dependam. É uma opção mais segura, mas ainda assim, para uma mudança é necessário mapear e isso o SCHEMA BINDING não faz.

Há muito tempo atrás, publiquei os artigos “Mapeando dependências entre tabelas” e “Mapeando dependências entre objetos” que trata sobre dependências com compatibilidade para o SQL Server 2005 (no 2008, os scripts funcionam, mas há outros objetos mais diretos). Ainda assim, não me atentei para uma necessidade de mapeamento para colunas. Vejamos então como podemos fazer isso:

CREATE TABLE Categorias (
    CodigoCategoria INT NOT NULL,
    NomeCategoria VARCHAR(15) NOT NULL,
    Descricao VARCHAR(MAX) NULL
    CONSTRAINT PK_Categorias PRIMARY KEY (CodigoCategoria))

CREATE TABLE Produtos (
    CodigoProduto INT NOT NULL,
    NomeProduto VARCHAR(40) NOT NULL,
    CodigoFornecedor INT NULL,
    CodigoCategoria INT NULL,
    QuantidadePorUnidade VARCHAR(25) NULL,
    PrecoUnitario MONEY NULL,
    UnidadesEstoque SMALLINT NULL,
    UnidadesPedidas SMALLINT NULL,
    NivelDeReposicao SMALLINT NULL,
    Descontinuado BIT NOT NULL,
    CONSTRAINT PK_Produtos PRIMARY KEY (CodigoProduto))

CREATE TABLE DetalhesPedido (
    NumeroPedido INT NOT NULL,
    CodigoProduto INT NOT NULL,
    PrecoUnitario MONEY NOT NULL,
    Quantidade SMALLINT NOT NULL,
    Desconto REAL NOT NULL,
    CONSTRAINT PK_DetalhesPedido PRIMARY KEY (NumeroPedido,CodigoProduto))

CREATE TABLE Pedidos (
    NumeroPedido INT NOT NULL,
    CodigoCliente INT NULL,
    CodigoFuncionario INT NULL,
    DataPedido DATE NULL,
    DataEntrega DATE NULL,
    DataEnvio DATE NULL,
    Frete MONEY NULL,
    CONSTRAINT PK_Pedidos PRIMARY KEY (NumeroPedido))

CREATE TABLE Clientes (
    CodigoCliente INT NOT NULL,
    NomeEmpresa VARCHAR(40) NOT NULL,
    NomeContato VARCHAR(30) NULL,
    CargoContato VARCHAR(30) NULL,
    Endereco VARCHAR(60) NULL,
    Cidade VARCHAR(15) NULL,
    Regiao VARCHAR(15) NULL,
    CEP CHAR(8) NULL,
    Pais VARCHAR(15) NULL,
    Telefone VARCHAR(24) NULL,
    Fax VARCHAR(24) NULL,
CONSTRAINT PK_Clientes PRIMARY KEY (CodigoCliente))

Agora que já criamos algumas tabelas, o próximo passo é criar algumas views, procedures, functions e triggers para criar o mapeamento dos objetos e das colunas desses objetos.

CREATE VIEW vProdutos As
SELECT NomeCategoria, NomeProduto
FROM Categorias As C
INNER JOIN Produtos As P ON C.CodigoCategoria = P.CodigoCategoria
GO

CREATE FUNCTION dbo.CalculaTotalPedido (@NumeroPedido INT)
RETURNS MONEY
As
BEGIN
DECLARE
@Ret MONEY
SET @Ret = (SELECT Frete FROM Pedidos WHERE NumeroPedido = @NumeroPedido)
SET @Ret = @Ret + (
    SELECT SUM(Quantidade * PrecoUnitario * (1 – Desconto))
    FROM DetalhesPedido WHERE NumeroPedido = @NumeroPedido)

RETURN (@Ret)
END
GO

CREATE PROCEDURE UspRetornaClientesPedidos
As
SELECT NomeEmpresa, NomeContato, Cidade, Pais, CEP
FROM Clientes As C
LEFT OUTER JOIN Pedidos As P ON C.CodigoCliente = P.CodigoCliente
GO

CREATE TRIGGER trgEstoque ON DetalhesPedido
FOR INSERT, UPDATE, DELETE
As
BEGIN

    — Efetua os incrementos
    UPDATE Produtos SET UnidadesEstoque += D.Quantidade
    FROM Produtos As P
    INNER JOIN DELETED As D ON P.CodigoProduto = D.CodigoProduto

    — Efetua os decrementos
    UPDATE Produtos SET UnidadesEstoque -= I.Quantidade
    FROM Produtos As P
    INNER JOIN INSERTED As I ON P.CodigoProduto = I.CodigoProduto

END

Visualmente podemos ver que a view, a function, a procedure e a trigger fazem referência para algumas colunas de algumas triggers. Utilizando a view de catálogo sys.sql_dependencies podemos mapear as dependências entre os objetos.

SELECT
    OBJECT_NAME(referenced_major_id) As ObjetoReferenciado,
    OBJECT_NAME(object_id) As Objeto
FROM sys.sql_dependencies
ORDER BY ObjetoReferenciado

O resultado de fato mostra as dependências:

Objeto Referenciado Objeto
Categorias vProdutos
Categorias vProdutos
Clientes UspRetornaClientesPedidos
Clientes UspRetornaClientesPedidos
Clientes UspRetornaClientesPedidos
Clientes UspRetornaClientesPedidos
Clientes UspRetornaClientesPedidos
Clientes UspRetornaClientesPedidos
DetalhesPedido CalculaTotalPedido
DetalhesPedido CalculaTotalPedido
DetalhesPedido CalculaTotalPedido
DetalhesPedido CalculaTotalPedido
Pedidos CalculaTotalPedido
Pedidos UspRetornaClientesPedidos
Pedidos CalculaTotalPedido
Produtos trgEstoque
Produtos vProdutos
Produtos vProdutos
Produtos trgEstoque

Podemos ver todos os mapeamentos, mas o fato de haver duplicidades é no mínimo intrigante. Usar o DISTINCT resolveria isso fácil, mas se há repetição há alguma razão para isso. Vamos pesquisar algumas outras colunas que provocam essa repetição:

SELECT
    OBJECT_NAME(referenced_major_id) As ObjetoReferenciado,
    OBJECT_NAME(object_id) As Objeto,
    column_id, referenced_minor_id

FROM sys.sql_dependencies
ORDER BY ObjetoReferenciado

Eis o resultado:

Objeto Referenciado Objeto Column_ID Referenced_minor_id
Categorias vProdutos 0 1
Categorias vProdutos 0 2
Clientes UspRetornaClientesPedidos 0 1
Clientes UspRetornaClientesPedidos 0 2
Clientes UspRetornaClientesPedidos 0 3
Clientes UspRetornaClientesPedidos 0 6
Clientes UspRetornaClientesPedidos 0 8
Clientes UspRetornaClientesPedidos 0 9
DetalhesPedido CalculaTotalPedido 0 1
DetalhesPedido CalculaTotalPedido 0 3
DetalhesPedido CalculaTotalPedido 0 4
DetalhesPedido CalculaTotalPedido 0 5
Pedidos CalculaTotalPedido 0 1
Pedidos UspRetornaClientesPedidos 0 2
Pedidos CalculaTotalPedido 0 8
Produtos trgEstoque 0 1
Produtos vProdutos 0 2
Produtos vProdutos 0 4
Produtos trgEstoque 0 7

O column_id não ajudou muito, mas a coluna Referenced_minor_id já é uma boa pista, pois, é visível que ela provoca a duplicidade. Se olharmos o Books OnLine, essa coluna é mais específica (Referenced_minor_id – 1, referenced_minor_id is a column ID; or if not a column, it is 0.). Só precisamos então fazer um JOIN com a sys.columns e um filtro para garantir que estamos sempre falando de colunas.

SELECT
    OBJECT_NAME(referenced_major_id) As ObjetoReferenciado,
    OBJECT_NAME(D.object_id) As Objeto,
    C.name As Coluna
FROM sys.sql_dependencies As D
INNER JOIN sys.columns As C ON
    D.referenced_major_id = C.object_id AND D.referenced_minor_id = C.column_id
WHERE class_desc = ‘OBJECT_OR_COLUMN_REFERENCE_NON_SCHEMA_BOUND’
ORDER BY ObjetoReferenciado

Eis o resultado:

Objeto Referenciado Objeto Coluna
Categorias vProdutos CodigoCategoria
Categorias vProdutos NomeCategoria
Clientes UspRetornaClientesPedidos CodigoCliente
Clientes UspRetornaClientesPedidos NomeEmpresa
Clientes UspRetornaClientesPedidos NomeContato
Clientes UspRetornaClientesPedidos Cidade
Clientes UspRetornaClientesPedidos CEP
Clientes UspRetornaClientesPedidos Pais
DetalhesPedido CalculaTotalPedido NumeroPedido
DetalhesPedido CalculaTotalPedido PrecoUnitario
DetalhesPedido CalculaTotalPedido Quantidade
DetalhesPedido CalculaTotalPedido Desconto
Pedidos CalculaTotalPedido NumeroPedido
Pedidos UspRetornaClientesPedidos CodigoCliente
Pedidos CalculaTotalPedido Frete
Produtos trgEstoque CodigoProduto
Produtos vProdutos NomeProduto
Produtos vProdutos CodigoCategoria
Produtos trgEstoque UnidadesEstoque

Podemos ver que todas as colunas foram retornadas conforme o esperado. Não somente as colunas usadas em SELECTs e UPDATEs, mas também as colunas utilizadas em JOINs (e como isso faz diferença). Será que realmente funciona para outros casos ? Vejamos um objeto com a opção SCHEMA_BINDING (requerida em views indexadas inclusive).

CREATE VIEW vProdutos2
WITH SCHEMABINDING
As
SELECT
NomeCategoria, NomeProduto
FROM dbo.Categorias As C
INNER JOIN dbo.Produtos As P ON C.CodigoCategoria = P.CodigoCategoria
GO

Se tentarmos a consulta anterior, infelizmente a view vProdutos2 não será retornada. A razão é que o vínculo é com schema binding e aí precisamos alterar o predicado.

SELECT
    OBJECT_NAME(referenced_major_id) As ObjetoReferenciado,
    OBJECT_NAME(D.object_id) As Objeto,
    C.name As Coluna
FROM sys.sql_dependencies As D
INNER JOIN sys.columns As C ON

    D.referenced_major_id = C.object_id AND D.referenced_minor_id = C.column_id
WHERE class_desc IN (
    ‘OBJECT_OR_COLUMN_REFERENCE_NON_SCHEMA_BOUND’,
    ‘OBJECT_OR_COLUMN_REFERENCE_SCHEMA_BOUND’)
ORDER BY ObjetoReferenciado

Eis o resultado:

Objeto Referenciado Objeto Coluna
Categorias vProdutos CodigoCategoria
Categorias vProdutos NomeCategoria
Categorias vProdutos2 CodigoCategoria
Categorias vProdutos2 NomeCategoria
Clientes UspRetornaClientesPedidos CodigoCliente
Clientes UspRetornaClientesPedidos NomeEmpresa
Clientes UspRetornaClientesPedidos NomeContato
Clientes UspRetornaClientesPedidos Cidade
Clientes UspRetornaClientesPedidos CEP
Clientes UspRetornaClientesPedidos Pais
DetalhesPedido CalculaTotalPedido NumeroPedido
DetalhesPedido CalculaTotalPedido PrecoUnitario
DetalhesPedido CalculaTotalPedido Quantidade
DetalhesPedido CalculaTotalPedido Desconto
Pedidos CalculaTotalPedido NumeroPedido
Pedidos UspRetornaClientesPedidos CodigoCliente
Pedidos CalculaTotalPedido Frete
Produtos trgEstoque CodigoProduto
Produtos vProdutos NomeProduto
Produtos vProdutos CodigoCategoria
Produtos trgEstoque UnidadesEstoque
Produtos vProdutos2 CodigoProduto
Produtos vProdutos2 NomeProduto

Agora sim funcionou. Para mapeamentos simples, essa view é uma mão na roda. Fica aí a dica.

[ ]s,

Gustavo

MVP Open Day, TechED & Community Zone

Oi Pessoal,

Após duas semanas da finalização desses três eventos, finalmente consegui um tempinho para contar um pouquinho o resultado desses três excelentes eventos. Dia 28/09 tivemos o MVP Open Day com uma grade de palestras muito interessantes sobre vários produtos Microsoft (Visual Studio, SQL Server, ALM, Windows Phone, etc). Em virtude do NDA não posso abrir muitos detalhes, mas eu diria apenas que o ano de 2012 é um ano bastante promissor para a plataforma de servidores (Windows Server, Hyper-V, etc). Fora as palestras foi ótimo encontrar alguns amigos no evento e compartilhar o happy hour com alguns dos profissionais que tanto contribuem para a comunidade. Não sei o que o futuro reserva, mas espero poder estar no MVP Open Day de 2012, 2013 e por aí vai. Só não aproveitei mais o evento, porque em virtude do TechED tive que ir embora cedo já que precisava dar uma boa revisada nas demos da palestra.

MVPs Brazucas

Dia 29/09 começava o primeiro dia do TechED. A grade de palestras estava interessante, mas com alguns conflitos que não pude evitar. Fiquei chateado de não poder prestigiar vários amigos com palestras muito interessante (BI, Azure, etc). Bem, infelizmente a vida é feita de escolhas e tive que optar pelas sessões que mais agregavam no momento. O grande destaque desse dia para mim foi a palestra do Nilton Pinheiro sobre HADR no Denali. O HADR é sem dúvida uma implementação fantástica e finalmente a Microsoft colocou algo para competir com o Oracle Data Guard no cenário de HA. O DB2 ficou para trás, pois, o HADR dele ainda não possui a funcionalidade de múltiplos sites Standby. Estou na torcida para que o próximo SQL Server depois do Denali finalmente traga algo para competir com o Oracle RAC e o Pure Scale do DB2. O tema foi abordado pelo Nilton com maestria, de forma clara e muito bem apresentada além de responder minhas dúvidas sobre o sistema de quórum do HADR. Tive a oportunidade de conversar com ele na hora do almoço antes da palestra para saber o que viria, e realmente atendeu bem minhas expectativas. Também pude aproveitar para assistir a palestra de troubleshooting do Fabio Gentile e da Renata Festa. Já conhecia o Gentile de alguns chamados no Premier e o assunto muito me interessava. Destaque para as novas DMVs do 2008R2 (SP1) que finalmente possibilitam obter dados de discos Mount Point (não aguentava mais a xp_fixeddrives). Muito boa, mas não espera menos do time de PFEs. Foi bom para atualizar algumas coisas que eu ainda não tinha fuçado.

Dia 30/09 era o grande dia. Eu já tinha participado do TechED em 2010 na mesa do Ask The Experts (que lamento inclusive ter sido retirada do evento em 2011), mas nunca tinha participado como palestrante. Na noite anterior havia finalizado todas as minhas demos e em termos de conteúdo estava tudo certo. Cheguei na sala 30 minutos antes, conversei com os operadores, fiz os testes de som, etc. Minha sessão iniciava às 10:10 e às dez horas eu já estava a postos. Naquela hora contando por alto tinha umas 50 pessoas na sala só. Por um lado fiquei meio chateado, pois, a capacidade da sala era no mínimo cinco vezes maior, mas por outro lado, até que o frio na barriga diminui com pouca gente. Quando foi 10:05 (cinco minutos antes), de repente a sala começa a encher e rapidamente os lugares são ocupados e aí sim fiquei mais feliz por ter mais interessados, mas sem dúvida comecei a ficar inquieto, pois, nunca havia feito uma sessão presencial para tantas pessoas (nas minhas contas deu umas 260, mas acho que foi mais). Pois bem, iniciada as formalidades, comecei a falar do ISO, evolução do T-SQL, novidades do 2008 R2 e chegamos na parte do Denali. Controlei o tempo e consegui apresentar tudo o que eu gostaria. Faltou só mais 5 minutinhos para falar das referências, etc, mas de qualquer forma gostei muito da experiência.

DBP302 - T-SQL: O que você deve saber do Microsoft SQL Server 2008 R2 e as novidades do SQL Server Code-Named Denali

Aproveito para agradecer a todos os presentes na sessão que participaram e aqueles que contribuiram com perguntas, sugestões, etc. A todos os que compareceram o meu muito obrigado. Aqueles que não puderam estar presentes, o PPT com os scripts pode ser baixado no meu Skydrive (20111017_TechED_DBP302_TSQL_2008R2_NewFeaturesDenali.rar)

DBP302 – T-SQL: O que você deve saber do Microsoft SQL Server 2008 R2 e as novidades do SQL Server Code-Named “Denali”
https://skydrive.live.com/?cid=F4F5C630410B9865&id=F4F5C630410B9865%21148

Após a minha sessão, mal saí da sala e já tive de voltar para assistir a sessão do Fabiano e do Luti sobre Cenários de otimização com o SQL Server “Denali” e 2008. Sessão bem interessante sobre estatísticas, cache bloat, parameter sniffing, etc. Foi um belo apanhado geral sobre alguns detalhes do otimizador de consultas além de uma excelente sessão. Na seqüência (e na mesma sala inclusive), veio a palestra do Catae e Pimenta sobre Raio-X do SQL Server: Arquitetura interna do gerenciador de banco de dados. No ano passado eu assisti uma palestra do Catae e do Gentile sobre troubleshooting e gostei bastante, pois, sempre se aprende algo novo além de conhecer alguns detalhes nas entranhas do SQL Server. O que mais gosto mesmo é das perguntas pós apresentação. Essa sessão falou bastante sobre as decisões que otimizador tem de tomar para montar um plano de execução (em especial na avaliação de índices). Se tivessem trocado a ordem da sessão do Fabiano e do Luti com a do Catae e do Pimenta eu acho que teria ficado perfeito, pois, os temas eram complementares, mas a sessão do Fabiano e do Luti entrou mais a fundo no otimizador. Para finalizar bem o evento, assisti a palestra da Viviane Ribeiro e do Ruy Pimentel sobre Soluções de Alta disponibilidade e Disaster Recovery para o SQL Server. Essa já é uma sessão “permanente” no TechED, pois, já a vi na agenda no anos anteriores, mas esse ano com outros apresentadores. Embora a sessão tenha abordado as implementações de alta disponibilidade de uma forma geral, houve um grande foco da apresentação no Failover Clustering. Achei interessante, pois, das técnicas de alta disponibilidade, a que mais trabalho no dia a dia é o Failover Clustering. Foi muito proveitoso ver como é gerenciado a parte de clustering de uma grande empresa como a DELL. Tive a oportunidade de ficar no mesmo quarto que o Ruy durante o evento e trocamos várias figurinhas sobre o assunto (DTC, distribuição de instâncias, divisão de memória, etc). O que mais valeu a pena pra mim foram os detalhes de como “emular” múltiplas instâncias Default no mesmo cluster. Esse macete já tinha valido a sessão e espero implementar no ambiente de um dos clientes muito em breve.

Palestrantes do TechED (boa parte de SQL Server)

No dia 01/10, terminei minha saga em São Paulo e eventos Microsoft com o Community Zone. Nem preciso dizer que eu adoro participar e contribuir com a comunidade Microsoft e esse evento foi bem direcionado para contribuidores. Foi ótimo para conhecer algumas pessoas do fórum que a gente conversa, lê blog, vê webcast, mas nunca conhece pessoalmente (de uma forma geral, os três dias foram assim). Aproveitei a ocasião e pude para discutir algumas iniciativas e eventos futuros para a comunidade de SQL Server. Alinhamentos gerais e boas coisas por vir…

DSC02622

De volta a Brasília e muita coisa pra estudar, organizar e entregar, mas o próximo encontro já está marcado. Dia 26/11 teremos o SQL Saturday que promete bastante. Mais um evento presencial com vários especialistas em SQL Server (e o melhor que é gratuito). Vou marcar presença. Se você quer se inscrever não perca tempo. Nos vemos lá.

Welcome to SQLSaturday #100  – Bem Vindo ao SQLSaturday #100
http://www.sqlsaturday.com/100/eventhome.aspx

[ ]s,

Gustavo

Ampliando a comunicação – Mais um canal para falar de SQL Server e Banco de Dados

Bom Dia Pessoal,

Após a correria com as certificações e a semana do SQL Server, estou aqui para mais um rápido Post.

Primeiramente eu quero agradecer a todos que participaram da Semana do SQL Server assistindo aos eventos, postando dúvidas, divulgando o evento e contribuindo de uma forma geral. Infelizmente não pude apresentar por um problema com o aúdio do microfone. Mesmo chegando praticamente uma hora antes da sessão começar, não consegui configurar o microfone e acabou que não pude fazer o Webcast. Eu aproveito para agradecer também a Andressa que foi muito prestativa e me deu todo o suporte nesse evento e ao Felipe pela oportunidade.

Ainda assim, não poderia deixar de disponibilizar o conteúdo de alguma forma. Andei pensando numa forma de não somente disponibilizar esse vídeo especificamente, mas outros vídeos de SQL Server em geral que eu gravei e que por ventura venha a gravar. Aproveitei para abrir uma conta no Youtube e começarei a divulgar os vídeos das apresentações que faço (Webcasts, SQL Server DF, Virtual Pass, etc). Aos interessados, segue o link para assistir alguns dos vídeos que já disponibilizei (17 até agora):

Canal de gmasql
http://www.youtube.com/user/gmasql

Como fiz a gravação posteriormente, não fiquei limitado ao tempo de uma hora e acabei fazendo quase duas horas de gravação do Webcast “Evitando Erros Comuns na Elaboração de Código T-SQL”. Por uma questão de regras no Youtube, tive que quebrar o Webcast em várias partes:

Quem quiser o vídeo inteiro, segue o link para efetuar o download:

https://skydrive.live.com/redir.aspx?cid=f4f5c630410b9865&resid=F4F5C630410B9865!119

Dividi em dois arquivos, pois, excedeu o tamanho de 100MB

20110805_Evitando_Erros_Comuns_na_Elaboracao_de_Codigo_TSQL.part1
20110805_Evitando_Erros_Comuns_na_Elaboracao_de_Codigo_TSQL.part2

[ ]s,

Gustavo

MCTS & MCITP – Business Intelligence Developer

Olá Pessoal,

Acabo de sair da prova PRO: Designing a Business Intelligence Infrastructure Using Microsoft SQL Server 2008 finalizando assim uma maratona de 4 meses de prova de certificação. Ao longo desse ano, concluí a 70-445 (MCTS BI 2005), 70-446 (MCITP BI 2005), 70-448 (MCTS BI 2008) e agora a 70-452 (MCITP BI 2008). Juntando com as certificações anteriores, finalmente completei a coleção de certificações de SQL Server MCTS e MCITP. Já era uma meta antiga, mas como as provas de SQL Server 2005 estão sendo descontinuadas, não ia ter mais jeito de adiar e aproveitei para fechar logo as certificações de SQL Server 2008.

Após ter concluído todas essas provas, é muito mais perceptível pra mim o quanto a suíte SQL Server evoluiu desde a versão 7 & 2000 (quando comecei a conhecer o produto). Antigamente era bem mais fácil realmente conhecer o SQL Server. Havia apenas o Database Engine com alguns serviços acessórios como o DTS e Analysis Services (sucessor do Olap Services). O Reporting Services sequer vinha junto com o CD de instalação, pois, só foi lançado em 2003 acompanhado de dois service packs. Reconheço que era difícil, mas de certa forma, não era tão complicado conhecer a fundo o SQL Server e por isso eu sempre tentei conhecer o máximo possível de todas as suas features.

Já no SQL Server 2005, eu percebi que manter esse ritmo e adentrar feature a feature seria uma tarefa hercúlia, mas nem por isso impossível. O Database Engine sofreu várias melhorias, o DTS evoluiu para o Integration Services e o Analysis Services e o Reporting Services foram praticamente reinventados. Era um desafio difícil, mas sempre tentei acompanhar. Com o SQL Server 2008, o que era difícil ficou quase impossível, pois, as evoluções dos produtos foram ainda maiores. Hoje com o SQL Server 2008 R2 e o Denali eu concluo que realmente perseguir feature a feature do SQL Server para tentar dominá-la beira o impossível.

Consegui tirar todas as certificações até aqui, mas o que isso realmente significa ? Absolutamente nada além de uma satisfação pessoal, pois, sinceramente falando não creio que obter todas as certificações significa realmente conhecer todas as features do SQL Server. Acho que significa apenas conhecê-las em um nível superficial e dominar algumas poucas. Não creio que conseguirei manter o ritmo quando sair o Denali, mas estudar para todas essas provas amplia os horizontes. Espero agora poder retornar às comunidades, aos estudos no Database Engine e aos projetos pessoais.

[ ]s,

Gustavo

Semana do SQL Server – Virtual Pass

Oi Pessoal,

Estou escrevendo esse rápido post para avisá-los sobre mais um grande evento de SQL Server. Nessa semana do dia 01/08 teremos o evento Semana do SQL Server que representa mais uma iniciativa do Virtual Pass BR. Boa parte dos melhores profissionais de SQL Server estarão palestrando além de algumas novidades referentes à nova versão do SQL Server (Denali). É uma ótima oportunidade para quem é fã de SQL Server e haverá palestras para todos os níveis. Várias palestras na área de BI (Power Pivot, SSAS, SSIS, SSRS), além de outras relacionadas ao Database Engine (Data Collector, Resource Governor, etc). Como não poderia deixar de ser há também palestras sobre o Denali e o SQL Azure. As inscrições são gratuitas e podem ser feitas pelo link abaixo:

https://www323.livemeeting.com/lrs/8000181573/Registration.aspx?pageName=h53mgg49dj267dkk

Eu estou participando do evento e irei palestrar sobre como evitar erros comuns na elaboração de código T-SQL. Não estou focando no Denali ou novidades relacionadas (essas eu deixo pro TechED), mas sim elecando vários erros que presencio nas aulas, consultorias, fóruns e no dia-a-dia cometido pelos desenvolvedores. Alguns erros são mais básicos (OUTER JOINs, NOT IN, etc), mas há alguns outros erros que passam completamente desapercebidos mesmo por profissionais experientes (Triggers, Snapshot Isolation, Merge, etc). Se você deseja precaver-se contra erros e BUGs e construir um código T-SQL mais refinado recomendo comparecer. Vou apresentar no dia 05/08 às 21h e desde já agradeço aos 260 inscritos até então. Farei uma palestra à altura da expectativa de vocês. Nos encontramos lá.

[ ]s,

Gustavo

DATEDIFF, CONVERT, SUBSTRING e funções sobre colunas na cláusula WHERE e JOINs

Boa Noite,

Já há muito tempo vejo uma prática recorrente na elaboração, manutenção e otimização de consultas presente em fóruns, aulas, softwares de terceiros enfim… Trata-se do uso de funções sobre colunas em cláusula WHERE que normalmente leva a consulta com desempenho abaixo do esperado. Para ilustrar, imaginemos a seguinte pergunta que demanda um instrução SELECT.

– Quais são os clientes que foram cadastrados há menos de trinta dias ?

Por intuição e tradução literal da pergunta para uma cláusula SQL, é bem comum que essa pergunta seja respondida com a seguinte consulta:

SELECT * FROM Clientes WHERE DateDIFF(dd,DataCadastro,GETDATE()) < 30

Se executássemos essa hipotética consulta, ela certamente retornaria os dados necessários. A função DATEDIFF calculará a diferença entre a data de cadastro e a data atual e aqueles registros que tiverem menos de trinta dias de diferença irão ser retornados. Entretanto, retornar pode ser suficiente para aplicações com poucos usuários e tabelas com pouco volume, mas aplicações críticas e alta concorrência, não basta “retornar”. É preciso fazer isso com eficiência e a construção apresentada não tem essa característica. Vejamos o porquê.

O uso do DATEDIFF

O uso do DATEDIFF é muito comum para calcular diferenças entre datas e não há nada errado em necessitar do resultado desse cálculo. Podemos calcular a diferença entre a data em que um determinado pedido foi feito e sua entrega, a diferença em horas que um empregado começou a trabalhar e encerrou o seu expediente ou ainda a diferença em segundos do início e do término de um treino de um maratonista. Ainda assim, há situações em que seu uso é indevido, especialmente em algumas situações na cláusula WHERE.

— Cria uma tabela com 2.000.000 registros
CREATE TABLE Clientes (
    ID INT NOT NULL,
    Nome VARCHAR(100) NOT NULL,
    Sexo CHAR(1) NOT NULL,
    DataNascimento DATETIME NOT NULL,
    CPF CHAR(11) NOT NULL,
    Cidade VARCHAR(100) NOT NULL,
    UF CHAR(2) NOT NULL,
    CEP CHAR(9) NOT NULL,
    Renda MONEY NOT NULL,
    DataCadastro DATETIME NOT NULL)

DECLARE @i INT
SET @i = 1

WHILE (@i < 2000000)
BEGIN
    INSERT INTO Clientes VALUES (
        @i,‘João da Cunha Costa e Silva Mendes’,‘M’,
        ‘19780101’,‘79985421240’,‘Brasília’,‘DF’,
        ‘70360-123’,4570.32,‘20110106’)
    SET @i = @i + 1
END

— Insere o cliente 2.000.000
INSERT INTO Clientes VALUES (
    2000000,‘Flávia Gomides Arnaldo Ferreira Costa’,‘F’,
    ‘19790331’,‘70034267890’,‘Brasília’,‘DF’,
    ‘70231030’,7832.46,GETDATE())

— Cria um índice sobre a data de cadastro
CREATE INDEX IX_DataCadastro ON Clientes (DataCadastro)

Esse script cadastrou 2.000.000 de clientes sendo que apenas um desses clientes foi cadastrado no dia de hoje. Se fosse perguntado quais os clientes que foram cadastrados a menos de 30 dias, esse seria o único registro retornado. Vejamos então o desempenho da consulta SQL sugerida anteriormente.

SET STATISTICS IO ON
SELECT * FROM Clientes WHERE DateDIFF(dd,DataCadastro,GETDATE()) < 30

(1 row(s) affected)
Table ‘Clientes’. Scan count 1, logical reads 28170, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Desempenho - Consulta com DATEDIFF

Como podemos perceber, para a recuperação do único registro que foi cadastrado a menos de trinta dias, foi necessário ler 28170 blocos o que em outras palavras nos levou a um TABLE SCAN inevitável. À primeira vista, alguns irão perguntar porque ler todos esses registros se existia um índice sobre a coluna Data ? Não seria melhor usar o índice para localizar o registro ao invés de fazer um SCAN nessa tabela ? Com certeza ler alguns poucos blocos seria muito melhor do que ler 28170 blocos especialmente se houvesse muita concorrência nessa tabela, mas se há um índice porque ele não foi usado ?

O índice IX_DataCadastro foi criado sobre a coluna DataCadastro produzindo uma lista ordernada por essa coluna muito semelhante à tabela abaixo:

DataCadastro ID da Linha Hash
2011-01-06 00:00:00.000 0x002A000001000000 (048da76b633b)
2011-01-06 00:00:00.000 0x002A000001000100 (6fa7aeb4ed68)
2011-01-06 00:00:00.000 0x002A000001000200 (393826e39435)
2011-07-02 11:30:25.390 0x7491000001000000 (3782542520bc)

Como o índice é sobre a coluna DataCadastro, todos os valores possíveis de DataCadastro virão ordenados e os respectivos ponteiros (Id da linha) são referenciados logo em seguida. Podemos ver que a data 06/01/2011 vem repetida diversas vezes, pois, temos quase 2.000.000 de ocorrências para esse valor. A última data 02/07/2011 (que foi o GETDATE() quando escrevi esse artigo) aparece apenas uma única vez.

A pesquisa pelo único registro que foi cadastrado a menos de trinta dias (2011-07-02) deveria pesquisar essa lista, encontrar o última entrada e com base no RowID (Id da linha) localizar o registro correspondente (Flávia Gomides). Entretanto, se observarmos a cláusula WHERE “DateDIFF(dd,DataCadastro,GETDATE()) < 30” podemos observar que nossa pesquisa não refere-se exatamente a DataCadastro, mas sim ao resultado de uma função aplicada sobre a Data de Cadastro. Nesse caso, nossa lista é praticamente inútil, pois, ela é baseada na DataCadastro e não no resultado da função DateDIFF. Teríamos aqui duas possibilidades. A primeira é converter toda a lista e fazer o cálculo produzindo uma nova lista ou simplesmente ignorar a lista e fazer a varredura de tabela. O otimizador optou por ignorar a lista, pois, possivelmente o trabalho de conversão é mais dispendioso que efetuar o TABLE SCAN. Há dois pontos de perda de desempenho aqui. O primeiro é que a lista foi ignorada e um SCAN foi realizado. O segundo é que temos uma lista que precisa ser mantida a cada INSERT, UPDATE (na DataCadastro) e DELETE. Resumidamente, temos um lista que tem um ônus para ser mantida, mas que não trás nenhum benefício para a consulta.

Se for observada a natureza da consulta, é perfeitamente possível reescrevê-la para usufruir da lista (índice) que foi criado sendo muito mais eficiente. O que se deseja saber é que clientes foram cadastrados a menos de trinta dias. Ao invés de aplicarmos uma função sobre a data e assim desprezarmos a lista, podemos simplesmente calcular a data de trinta dias atrás e fazer uma comparação da DataCadastro com esse valor.

SELECT * FROM Clientes WHERE DateDIFF(dd,DataCadastro,GETDATE()) < 30
SELECT * FROM Clientes WHERE DataCadastro > DateADD(dd,-30,GETDATE())

(1 row(s) affected)
Table ‘Clientes’. Scan count 1, logical reads 28170, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)
Table ‘Clientes’. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Desempenho - Consulta com DATEADD

Nesse caso é muito visível a diferença de desempenho. O uso do DATEDIFF produz um TABLE SCAN que lê 28170 páginas e o uso do DATEADD produz uma pesquisa de índice que lê apenas 4 páginas para trazer o registro. Não é a toa que 100% do esforço foi atribuído ao DATEDIFF já que o DATEADD representa um parcela irrisória do custo do total. Quando utilizamos o DATEADD, aplicamos essa função sobre o GETDATE() para produzir uma data que represente 30 dias atrás. Isso é feito uma única vez, pois, a função não foi aplicada sobre nenhuma coluna. Após obtermos a data de trinta dias atrás, basta compará-la com a coluna DataCadastro. Uma vez que a coluna DataCadastro não sofreu nenhuma modificação, o índice será naturalmente avaliado no plano de execução e nesse caso foi utilizado retornando a consulta de forma muito mais eficiente. A razão do desempenho não é que o DATEADD é mais rápido que DATEDIFF, mas simplesmente o fato de que a coluna DataCadastro não teve nenhuma função aplicada sobre si.

O uso do CONVERT

É bem comum o uso do CONVERT em conjunto com datas na cláusula WHERE especialmente em situações que envolvam horas. Me parece que há uma certa resistência, medo ou alguma razão muito forte para que a maioria dos códigos evite consultas com o uso do BETWEEN, <=, >= em prol de uma igualdade. Talvez seja o fato de que o padrão DMY não é o mundialmente aceito além do que normalmente durante os primeiros contatos com programação é comum o uso e abuso de funções de formatação. Bem, o fato é que o CONVERT em cláusulas WHERE padece do mesmo mal que o DATEDIFF, ou seja, o não aproveitamento pleno dos índices existentes.

No meu cadastro, o último cliente foi cadastrado no dia 02/07/2011. Vejamos se a consulta abaixo consegue recuperá-lo

SELECT * FROM Clientes WHERE DataCadastro = ‘2011-07-02’

Embora o registro “Flávia Gomides” tenha sido cadastrado no dia 02/07/2011, ele foi cadastrado às 11:30 e por isso a consulta acima não o recuperou. O sufixo do horário às vezes é bem inconveniente. Uma forma que é bem utilizada para eliminá-lo é o uso do CONVERT.

SELECT * FROM Clientes WHERE CONVERT(CHAR(10),DataCadastro,103) = ’02/07/2011′

(1 row(s) affected)
Table ‘Clientes’. Scan count 1, logical reads 28170, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Desempenho - Consulta com CONVERT

O TABLE SCAN dispensa muitos comentários. O registro dessa vez foi retornado, mas de forma bem ineficiente, pois, foi necessário ler 2.000.000 de linhas para conseguir retornar um único registro. Toda vez que vejo uma consulta no fórum ou algum aluno sugerindo o uso do CONVERT em uma situação dessas é exatamente nesse plano de execução que eu penso. Vejamos como fazer para não incorrer em tanta ineficiência.

Se a data é acrescentada do horário, podemos tentar uma comparação com base em um intervalo e não em uma data específica. Supondo que o registro tenha sido cadastrado no dia 02/07/2011 em algum horário desconhecido, temos a certeza de que esse registro está em algum momento entre a 0h do dia 02/07/2011 e a 0h do dia 03/07/2011. Só que não podemos incluir a 0h do dia 03/07/2011 para não retonar os registros desse dia. Duas boas sugestões para compararmos com o CONVERT.

SELECT * FROM Clientes WHERE CONVERT(CHAR(10),DataCadastro,103) = ’02/07/2011′
SELECT * FROM Clientes WHERE DataCadastro >= ‘20110702’ And DataCadastro < ‘20110703’
SELECT * FROM Clientes WHERE DataCadastro BETWEEN ‘20110702’ And ‘20110702 23:59.999’

(1 row(s) affected)
Table ‘Clientes’. Scan count 1, logical reads 28170, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 

(1 row(s) affected)
Table ‘Clientes’. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)
Table ‘Clientes’. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Desempenho - Consulta com BETWEEN e intervalos

Como pode-se observar o uso de intervalos >= e < ou o BETWEEN são muito mais eficientes, pois, utilizam o índice corretamente enquanto que o CONVERT opta por um SCAN. Enquanto o CONVERT precisou ler 28710 blocos, as outras construções ficaram com apenas 4 páginas cada uma. Sem dúvida uma incomparável eficiência em relação ao CONVERT.

Uma dúvida é comum é como usar o GETDATE() para capturar os clientes que foram cadastrados no mesmo dia. Eu utilizei o dia 02/07/2011 porque era o meu GETDATE() na época desse artigo, mas como montar uma consulta que varia de acordo com o dia em que for executada sem a necessidade de extrair o dia previamente ? Bem, dá um pouquinho mais de trabalho, mas podemos fazê-lo sem evitar o CONVERT.

SELECT * FROM Clientes
WHERE
    DataCadastro >= CONVERT(CHAR(8),GETDATE(),112) And
    DataCadastro < CONVERT(CHAR(8),GETDATE()+1,112)

O CONVERT com o formato 112, colocará a data atual no formato YYYYMMDD sem a parte do horário retornando no meu caso ‘20110702’. O GETDATE() + 1 retorna a data atual com um dia a mais e com o uso do CONVERT no formato 112, teríamos ‘20110703’. Essa forma de consulta também fará uso eficiente do índice Seek e dispensa o TABLE SCAN.

Em alguns posts, aulas e consultorias já vi algumas outras soluções criativas para resolver esse problema que também merecem ser comentadas: CONVERT para datas, YEAR, MONTH e DAY.

SELECT * FROM Clientes
WHERE
    YEAR(DataCadastro) = YEAR(GETDATE()) And
    MONTH(DataCadastro) = MONTH(GETDATE()) And
    DAY(DataCadastro) = DAY(GETDATE())

(1 row(s) affected)
Table ‘Clientes’. Scan count 3, logical reads 4771, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Desempenho - Consulta com YEAR, MONTH e DAY

É uma alternativa criativa, mas não necessariamente performática. Ao contrário do CONVERT que gerou um SCAN com 28710 blocos, o uso das funções YEAR, MONTH e DAY de forma combinada fez uma leitura de 4771 blocos (pouco mais de 25% da tabela). Esse é o resultado de uma varredura e conversão da lista (índice). Considerando que varrer o índice e convertê-lo para consulta posterior foi mais performático, o otimizador optou por fazê-lo e por isso vimos um Index Scan (varredura do índice) ao invés de um TABLE SCAN (varredura de tabela). Ainda assim é quase 1200 vezes mais lento que a leitura de 4 páginas quando o índice foi usado com eficiência e para pesquisa (Seek). Outros dois pontos a comentar sobre o uso dessas três funções é que foi necessário um tabela temporária (Worktable) e o uso de paralelismo para que ela pudesse funcionar. Em um ambiente com muita concorrência, essas condições podem tornar o resultado um pouco menos performático. Embora interessante, a melhor opção ainda é evitar funções sobre colunas.

SUBSTRING, LEFT, LTRIM e funções textuais

Não raras às vezes é necessário fazer comparações com base em pedaços de uma string quando não o tratamento de dados durante a execução de uma query (como se fosse papel de uma consulta fazer tratamento de dados a cada execução). Para demonstrar que essas funções também não são uma boa idéia, vamos modificar alguns registros e criar novos índices.

— Cria um índice sobre a coluna Nome
CREATE INDEX IX_Nome ON Clientes (Nome)

— Consulta os clientes que começam com a letra F
SELECT * FROM Clientes WHERE SUBSTRING(Nome,1,1) = ‘F’
SELECT * FROM Clientes WHERE LEFT(Nome,1) = ‘F’

Table ‘Clientes’. Scan count 3, logical reads 12543, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table ‘Clientes’. Scan count 3, logical reads 12543, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Desempenho - Consulta com SUBSTRING e LEFT

As funções SUBSTRING e LEFT tiveram o mesmo papel nesse caso, ou seja, apenas retirar a primeira letra de cada nome para descobrir os clientes que iniciam o nome com a letra F. A pesquisa não efetuou um SCAN na tabela, mas sim no índice. Embora o SCAN no índice seja mais eficiente que um SCAN na tabela, isso não significa que a consulta foi eficiente. Para retornar cada uma leu 12543 páginas que representa quase 50% do total dos 28710 blocos da tabela. Vejamos como otimizar essa pesquisa:

— Consulta os clientes que começam com a letra F
SELECT * FROM Clientes WHERE SUBSTRING(Nome,1,1) = ‘F’
SELECT * FROM Clientes WHERE Nome LIKE ‘F%’

Desempenho - Consulta com LIKE

Como o LIKE não aplicou nenhuma função sobre a coluna, o índice pode ser utilizado eficientemente já que não há necessidade de conversão. O índice IX_Nome é uma lista ordenada de nomes e embora o LIKE não seja uma correspondência exata, nesse caso ele pode localizar as entradas da lista que começam com F e varrê-las até que haja um troca de letra (digamos G), pois, a lista não sofreu nenhuma conversão e o LIKE possui um string antes do % (se o LIKE começasse com % não seria possível usar o índice).

Algumas funções textuais costumam ser utilizadas para limpeza de dados como o LTRIM e o RTRIM. A seguir, farei uma atualização para que possámos utilizar essas funções:

— Atualiza o registro “Flávia Gomides”
UPDATE Clientes SET Nome = ‘ Flávia Gomides Arnaldo Ferreira Costa’
WHERE ID = 2000000

— Faz a pesquisa retirando os espaços em branco
SELECT * FROM Clientes WHERE LTRIM(Nome) LIKE ‘F%’

Table ‘Clientes’. Scan count 1, logical reads 28170, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Desempenho - Consulta LTRIM

Dessa vez foi feita uma varredura (TABLE SCAN) lendo 28170 blocos. A varredura não deve-se ao LIKE, mas ao uso do LTRIM sobre a coluna nome. Não há otimização a ser feita na query, pois, de fato o nome está com um espaço antes e normalmente não sabemos quantos espaços existem o que nos leva ao uso do LTRIM. Isso só mostra que se temos problemas de dados de má qualidade, temos que corrigí-los sempre na entrada e não a cada consulta. Um cadastro com milhares de clientes iria sofrer um bocado se cada consulta usasse um LTRIM porque a aplicação não retirou os espaços antes de efetivar o cadastro. Ainda que haja sujeira na base, é muito melhor efetuar um UPDATE para corrigir uma única vez do que usar o LTRIM a cada consulta.

User Defined Functions

A partir da versão 2000, o SQL Server permite a construção de funções próprias ao invés das funções já existentes. Será que essas funções vão utilizar da mesma lógica ?

— Cria uma função para verificar se a renda é maior que 5.000
CREATE FUNCTION dbo.FnRendaMaior5000 (@Renda MONEY)
RETURNS TINYINT
As
BEGIN
    DECLARE
@bRendaMaior5000 TINYINT
    SET @bRendaMaior5000 = 0
    IF @Renda > 5000
        SET @bRendaMaior5000 = 1
    RETURN(@bRendaMaior5000)
END
GO

— Cria um índice sobre a coluna Renda
CREATE INDEX IX_Renda ON Clientes (Renda)

— Consulta os clientes com Renda superior a 5000
SELECT * FROM Clientes WHERE Renda > 5000
SELECT * FROM Clientes WHERE dbo.FnRendaMaior5000(Renda) = 1

(1 row(s) affected)
Table ‘Clientes’. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)
Table ‘Clientes’. Scan count 1, logical reads 28170, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Desempenho - User Defined Functions

O uso de funções escalares teve um péssimo desempenho nesse caso. O TABLE SCAN ocorreu inevitavelmente pelas mesmas razões que as anteriores.

Expressões e colunas

Não é apenas o uso de funções sobre colunas que pode levar a ineficiência. Muitas vezes, utiliza-se colunas em expressões para efetuar um determinado cálculo. Vamos supor que o Marketing está avaliando a possibilidade de selecionar clientes que possuam uma renda capaz de pagar uma parcela de R$ 2.800,00 e ainda possuam R$ 2.000,00 de sobra. Há duas formas de estruturar essa consulta:

— Clientes que possuam uma renda capaz de pagar uma parcela de R$ 2.800,00
— e ainda possuam R$ 2.000,00 de sobra
SELECT * FROM Clientes WHERE Renda – 2800 >= 2000
SELECT * FROM Clientes WHERE Renda >= 4800

(1 row(s) affected)
Table ‘Clientes’. Scan count 3, logical reads 4771, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)
Table ‘Clientes’. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

20110701_10_Expressoes

Na primeira opção, apenas traduzimos o enunciado da consulta, ou seja, é preciso subtrair da renda 2.800 reais e o resultado tem de ser maior que 2.000 (Renda – 2800 > 2000). Na segunda possibilidade, apenas modificamos essa inequação repassando o 2.800 para o lado direito e invertendo o seu sinal somando-o a 2.000 e obtendo 4.800. Ambas as consultas retornam o mesmo resultado, mas a segunda consulta leu apenas 4 blocos (Seek) contra 4771 blocos (SCAN) da primeira consulta além de dispensar a tabela temporária (Work Table) e o paralelismo.

Os impactos na concorrência

Até agora demonstrei os devastadores efeitos das funções em atividades de consulta, mas será que há impactos nas operações de escrita ? E se existem quais seriam eles ? Vejamos então como o uso de funções em uma cláusula WHERE pode afetar atividades concorrentes em operações de escrita. Utilizarei três sessões (cada uma representa uma janela separada do SSMS). A primeira sessão deve ter sua consulta executada antes da segunda que deve ser executada antes da terceira.

— Sessão 1
— Muda o valor da renda do primeiro registro
UPDATE Clientes SET Renda = 1000 WHERE ID = 1

— Sessão 2
— Abre uma transação e não fecha
BEGIN TRAN
UPDATE
Clientes SET Nome = ‘Flávia Gomides Arnaldo Ferreira Costa’
WHERE DateDIFF(dd,DataCadastro,GETDATE()) < 30

— Sessão 3
SELECT * FROM Clientes WHERE Renda = 1000

Sabemos claramente que a segunda sessão irá atualizar o registro com ID 2.000.000 durante uma transação e que a terceira sessão irá ler o registro de ID 1, pois, é o único que possui renda igual a 1.000 logo após o UPDATE da primeira sessão. Entretanto, curiosamente a terceira sessão não conclui e parece estar bloqueada.

De fato a terceira sessão está bloqueada e não é preciso sequer consultar o SQL Server para verificar. Se um UPDATE é uma operação de leitura seguida de uma operação de escrita não é difícil de descobrir porque a terceira sessão ficou bloqueada. Vejamos as operações de leitura associadas às sessões dois e três.

— Sessão 2
SELECT * FROM Clientes WHERE DateDIFF(dd,DataCadastro,GETDATE()) < 30

— Sessão 3
SELECT * FROM Clientes WHERE Renda = 1000

20110701_11_Sessoes

Como a sessão 2 fez uma consulta usando o DATEDIFF sobre a coluna DataCadastro, foi necessário efetuar um TABLE SCAN sobre toda a tabela. Isso significa que toda a tabela foi lida (ainda que uma única linha tenha sido retornada). Da mesma forma que toda a tabela foi lida, toda a tabela foi bloqueada impedindo que a sessão 3 conseguisse fazer o SELECT (ainda que sobre uma linha que nada tinha a ver com a linha da sessão 2). Isso significa que consultas mal escritas que fazem TABLE SCAN são muito danosas não só às operações de consulta, mas também às operações de escrita. Para provar que a sessão 2 de fato bloqueia a sessão 3, basta efetuar um ROLLBACK na transação da sessão 2 que a sessão 3 retorna de imediato.

Há muitos softwares de prateleira medíocres que resolvem isso com o NOLOCK generalizado ao invés de se concentram no real problema e solucioná-lo de fato. O NOLOCK apenas permite que a sessão 3 funcione sem ficar bloqueada pela sessão 2, mas não iria evitar o SCAN ineficiente provocado pelo DATEDIFF. Vejamos como seria uma implementação correta (certifique-se que o ROLLBACK foi feito na sessão 2).

— Sessão 2
— Abre uma transação e não fecha
BEGIN TRAN
UPDATE
Clientes SET Nome =
‘Flávia Gomides Arnaldo Ferreira Costa’
WHERE DataCadastro > DateADD(dd,-30,GETDATE())

— Sessão 3
SELECT * FROM Clientes WHERE Renda = 1000

Como já vimos no início do artigo, a consulta de clientes com base na data de cadastro e no DateADD faz uso eficiente do índice e a consulta com base na renda também. Essa eficiência permite que as sessão 2 e a 3 possam rodar em paralelo sem que a sessão 3 seja bloqueada pela sessão 2. A implementação correta gera vários benefícios como evitar que a sessão 2 faça um SCAN e que a sessão 3 rode em paralelo. O uso do NOLOCK não proporciona os dois benefícios e seria a saída padrão para aqueles que não podem ou não querem elaborar consultas de qualidade. Agora que o efeito na concorrência foi demonstrado, não esqueça de efetuar um COMMIT na sessão 2, pois, esse registro será utilizado mais adiante.

Situações inevitáveis

Acredito que a essa altura do artigo já tenha ficado muito claro que o uso de funções sobre colunas em uma cláusula WHERE pode facilitar a escrita da consulta, mas quase sempre tem efeitos indesejados. Entretanto, algumas situações podem ser realmente difíceis de escapar do uso de funções sobre colunas. Vamos supor que exista a necessidade de consultar todas os clientes cadastrados no mesmo dia do mês da data atual para fazer alguma promoção.

— Consultar todos os cadastros efetuados no mesmo dia do mês
SELECT * FROM Clientes WHERE DAY(DataCadastro) = DAY(GETDATE())

(1 row(s) affected)
Table ‘Clientes’. Scan count 3, logical reads 28170, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Desempenho - Função DAY

Essa é uma situação em que apenas a revisão da consulta não é suficiente. No meu exemplo, o dia da data atual é 02 e a consulta precisa retornar todos os clientes cadastrados no dia 02 independente de que mês ou ano que seja. Nesse caso não há como fazer com que a consulta por si só seja eficiente. Ainda assim, há soluções para resolver o problema com a solução de COMPUTED COLUMNS.

— Altera a estrutura da tabela
ALTER TABLE Clientes ADD Dia As DAY(DataCadastro)

— Cria um índice sobre a coluna Dia
CREATE INDEX IX_Dia ON Clientes (DataCadastro)

— Consultar todos os cadastros efetuados no mesmo dia do mês
SELECT * FROM Clientes WHERE Dia = DAY(GETDATE())
SELECT * FROM Clientes WHERE DAY(DataCadastro) = DAY(GETDATE())

(1 row(s) affected)
Table ‘Clientes’. Scan count 3, logical reads 4771, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Desempenho - COMPUTED COLUMNS

A coluna DIA é uma coluna baseada em um cálculo (COMPUTED COLUMNS) e é mantida automaticamente quando há qualquer mudança na coluna DataCadastro. Após a criação da coluna, podemos materializá-la criando um índice sobre ela. Após a criação do índice, pudemos ver a redução de 28710 para 4771 páginas trocando um TABLE SCAN por um Index SCAN. Seria bom mesmo ver a eliminação dos SCANs, mas o otimizador optou por não fazê-lo nessa situação (embora haja várias situações que ele consiga fazer um Seek sobre uma coluna calculada). Um destaque é para que a criação do índice permitiu inclusive que não alterássemos a consulta. Mesmo usando a função DAY sobre a coluna DataCadastro, o otimizador percebeu que poderia utilizar a coluna calculada para otimizar a consulta reduzindo a quantidade de leituras. O uso de colunas calculadas pode otimizar consultas que inclusive não podem ser reescritas :) Um ponto positivo do ORACLE em relação ao SQL Server é que ele permitiria a criação do índice dispensando a criação da coluna calculada com a seguinte sintaxe: CREATE INDEX IX_Dia ON Clientes (DAY(DataCadastro)).

Analogias a JOINs

Embora o artigo tenha focado o uso de funções sobre colunas na cláusula WHERE até aqui, não significa que os JOINs estejam livres desses problemas. Pelo contrário, tudo o que foi dito aplica-se a eles sem exceção. Converter colunas, alterar collation ou ainda colocar expressões em JOINs também fará mal uso dos índices podendo levar a Index Scan ou ainda Table Scans. Vejamos um exemplo:

— Cria uma tabela com a coluna DataCadastro e um código promocional
CREATE TABLE Promocoes (DataCadastro CHAR(10), Desconto DECIMAL(5,2))

— Insere um registro para a promoção de hoje
INSERT INTO Promocoes VALUES (CONVERT(CHAR(10),GETDATE(),103),0.1)

— Verifica os clientes elegíveis para o desconto de hoje
SELECT C.*, P.Desconto FROM Clientes As C
INNER JOIN Promocoes As P
ON CONVERT(CHAR(10),C.DataCadastro,103) = P.DataCadastro

(1 row(s) affected)
Table ‘Promocoes’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Clientes’. Scan count 3, logical reads 28170, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

20110701_14_JoinsConvert

A tabela de Promoções colocou a data como CHAR(10) no formato DD/MM/YYYY enquanto que a tabela de clientes tem a data com o tipo DATETIME. Não consigo imaginar os reais motivos pelos quais alguém colocaria uma data como CHAR(10), mas enfim… Como as tabelas têm tipos de dados diferentes, para uní-las será necessário usar o CONVERT. Como pudemos perceber, foi um desastre que resultou em um TABLE SCAN na tabela de clientes. O correto mesmo seria efetuar um trabalho de limpeza e igualar os tipos de dados, mas admitindo que isso não seja possível, a recomendação é não converter os dados da maior tabela (ou daquelas que farão filto na cláusula WHERE) e sim das menores tabelas. Como ainda temos a questão do horário, o JOIN fica mais complexo porém muito mais performático (muito menos I/O).

— Verifica os clientes elegíveis para o desconto de hoje
SELECT C.*, P.Desconto FROM Clientes As C
INNER JOIN Promocoes As P
ON CONVERT(CHAR(10),C.DataCadastro,103) = P.DataCadastro

SELECT C.*, P.Desconto FROM Clientes As C
INNER JOIN Promocoes As P
ON C.DataCadastro >=
    CAST(
        RIGHT(P.DataCadastro,4) +
        SUBSTRING(P.DataCadastro,4,2) +
        LEFT(P.DataCadastro,2) As DateTime) And
    C.DataCadastro <
    CAST(
        RIGHT(P.DataCadastro,4) +
        SUBSTRING(P.DataCadastro,4,2) +
        LEFT(P.DataCadastro,2) As DateTime) + 1

(1 row(s) affected)
Table ‘Promocoes’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Clientes’. Scan count 3, logical reads 28170, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)
Table ‘Promocoes’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Clientes’. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Como a tabela Clientes possui muito mais registros que a tabela de promoções e um índice sobre a coluna DataCadastro não é uma boa idéia efetuar a conversão na tabela Clientes e sim na tabela de promoções. O problema é que a tabela de clientes não tem só a data, mas também o horário o que atrapalharia o resultado mesmo que convertermos a data da tabela de promoções. Nesse caso, efetuei um comparação com base em um intervalo composto pela conversão da data e do seu dia subsequente de forma semelhante ao início do artigo. O resultado já fala por si próprio. Apenas 5 páginas lidas contra 28710 páginas da primeira implementação. Essa é a diferença entre uma consulta eficiente e uma que retorna um TABLE SCAN ainda que ambas tragam o mesmo resultado.

Por fim, uma última consulta envolvendo JOINs que é uma situação comum quando há collations diferentes no banco de dados.

— Cria uma tabela de Clientes VIPs
CREATE TABLE ClientesVIPs (
    Nome VARCHAR(100)
    COLLATE Latin1_General_CS_AS NOT NULL,
    Desconto DECIMAL(5,2))

— Insere um registro
INSERT INTO ClientesVIPs VALUES (‘Flávia Gomides Arnaldo Ferreira Costa’,0.1)

— Retorna os dados dos Clientes VIPs
SELECT * FROM Clientes As C
INNER JOIN ClientesVIPs As CV ON C.Nome = CV.Nome

Como a collation das colunas é diferente, a consulta não conseguirá ser executada

Msg 468, Level 16, State 9, Line 3
Cannot resolve the collation conflict between “Latin1_General_CS_AS” and “Latin1_General_CI_AI” in the equal to operation.

Aos que quiserem descobrir mais sobre como resolver problemas de Collation eu recomendo o artigo “Como resolver problemas relacionados a conflitos de Collation”, mas vejamos algumas possibilidades de retornar.

— Retorna os dados dos Clientes VIPs (converte a tabela Clientes)
SELECT * FROM Clientes As C
INNER JOIN ClientesVIPs As CV ON C.Nome COLLATE Latin1_General_CS_AS = CV.Nome

— Retorna os dados dos Clientes VIPs (converte a tabela ClientesVIP)
SELECT * FROM Clientes As C
INNER JOIN ClientesVIPs As CV ON C.Nome = CV.Nome COLLATE Latin1_General_CI_AI

— Retorna os dados dos Clientes VIPs (converte a tabela ClientesVIP)
— Força o índice
SELECT * FROM Clientes As C WITH (INDEX=IX_Nome)
INNER JOIN ClientesVIPs As CV ON C.Nome = CV.Nome COLLATE Latin1_General_CI_AI

— Subquery
SELECT * FROM Clientes As C
WHERE Nome IN (
    SELECT CAST(Nome As VARCHAR(100)) COLLATE Latin1_General_CI_AI
    FROM ClientesVIPs)

— Subquery com índice forçado
SELECT * FROM Clientes As C WITH (INDEX=IX_Nome)
WHERE Nome IN (
    SELECT CAST(Nome As VARCHAR(100)) COLLATE Latin1_General_CI_AI
    FROM ClientesVIPs)

Table ‘ClientesVIPs’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Clientes’. Scan count 3, logical reads 28170, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Clientes’. Scan count 1, logical reads 28170, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘ClientesVIPs’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table ‘ClientesVIPs’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Clientes’. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table ‘ClientesVIPs’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Clientes’. Scan count 3, logical reads 28170, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table ‘ClientesVIPs’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Clientes’. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Como podemos ver dessa vez a recomendação de converter a tabela menor sozinha não deu efeito. Mesmo o uso de JOINs contra subqueries não surtiu diferença e foi necessário fazer um SCAN na tabela de clientes (28710 páginas). Se a gente der uma pequena mão para o otimizador e forçarmos o índice podemos reduzir a quantidade de leituras de 28710 páginas para apenas 5 páginas e assim obter muito mais eficiência. Não quero dizer com isso que forçar o índice deva ser prática comum no uso de JOINs com colunas convertidas e collations diferentes, mas sim que quando há funções sobre colunas há muitos efeitos colaterais indesejados. Se todas as colunas tivessem a mesma collation sequer teríamos de fazer essas avaliações. Bastaria que alguém atualizasse a estrutura da tabela uma única vez e a collation estaria corrigida. É muito mais eficiente do que corrigir a collation a cada consulta efetuada.

Conclusão

O uso de funções sobre colunas é prática comum em construção de consultas SQL. Quando há poucos usuários, baixo volume de dados e pouca concorrência, tais práticas podem passar muito desapercebidas principalmente no ambiente de desenvolvimento onde essas condições normalmente são encontradas. No entanto, para que aplicações tenham condições de escalar para um ambiente de missão crítica, tais práticas são proibitivas. Funções em cláusula WHERE e JOINs devem ser uma prática descartável e evitada sempre que possível. Utilizá-las é praticamente anular os benefícios de uma indexação eficiente. Boas consultas podem se beneficiar de bons índices, consultas ruins dificilmente irão fazê-lo. O comportamento aqui apresentado não restringe-se de forma nenhuma ao SQL Server. Mesmo bancos como o ORACLE irão sofrer dos mesmos problemas com o uso de funções como to_char tão utilizada para converter datas na cláusula WHERE.

[ ]s,

Gustavo