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

Funções Escalares – Resusabilidade, Formatação, Preguiça vs Desempenho

Bom Dia Pessoal,

Já faz algum tempo que queria escrever sobre funções escalares e recentemente com algumas threads no MSDN, me senti ainda mais motivado a escrever algumas palavrinhas a respeito. Vejo muita gente falando, usando e recomendando mesmo sem saber exatamente as penalidades envolvidas no uso de funções escalares.

O que são funções escalares ?

Função escalares são funções que recebem um ou mais valores retornam um valor. São exemplos de funções escalares naturais LTRIM, LEFT, CAST. A partir do SQL Server 2000 é possível a construção de funções escalares customizadas (User Defined Scalar Functions) obedecendo a mesma definição, ou seja, devem receber um conjunto de valores e retornar um valor. O uso das funções escalares está em consonância com um dos principais conceitos básicos da orientação, o encapsulamento. Uma vez que seja possível codificar uma lógica dentro da função (cálculo da taxa de juros, números de pedidos por empregado, ou ainda a formatação de uma data) é possível reaproveitá-la em vários trechos do códigos. O fato da função residir dentro do banco de dados também pode evitar a troca de contexto entre banco e aplicação.

Funções escalares e desempenho

Quando funções escalares customizadas são utilizadas em queries tendo como parâmetros de entradas valores das colunas referenciadas na instrução SELECT, normalmente há um alto custo de desempenho associado, pois, essas funções tem de ser chamadas uma vez para cada linha retornada (Row based) já que esse tipo de chamada não é otimizado pelo otimizador de consulta, pois, necessita de várias checagens adicionais (permissão, análise sintática, análise semântica, etc). Se uma função escalar necessita ser utilizada para conjuntos com poucos linhas, esse custo é imperceptível, mas quando grandes quantidade de linhas são necessárias, o custo da utilização das funções escalares torna-se cada mais visível.

Para demonstrar a perda de desempenho provocada pelo uso de funções escalares, o teste a seguir mostra duas consultas: uma com o uso de uma função escalar em conjunto com uma instrução SELECT e outra com a mesma lógica direta no SELECT sem a utilização de uma função:

Para demonstrar a perda de desempenho provocada pelo uso de funções escalares, o teste a seguir mostra duas consultas: uma com o uso de uma função escalar em conjunto com uma instrução SELECT e outra com a mesma lógica direta no SELECT sem a utilização de uma função:

— Cria uma tabela com 1 milhão de números
CREATE TABLE tNums (Num INT NOT NULL)

DECLARE @i INT
SET @i = 1

WHILE @i <= 1000000
BEGIN
    INSERT INTO tNums VALUES (@i)
    SET @i = @i + 1
END

— Marca a coluna Num como chave primária
ALTER TABLE tNums ADD CONSTRAINT PK_Num PRIMARY KEY (Num)

— Cria uma função que recebe um número e mostra o mesmo número mais um
CREATE FUNCTION fnAumenta1 (@Num INT)
RETURNS INT
As
BEGIN
    RETURN(@Num + 1)
END

— Retorna os 10 primeiros números com uso da função
SELECT Num, dbo.fnAumenta1(Num) FROM tNums
WHERE Num <= 10

— Retorna os 10 primeiros números seu o uso da função
SELECT Num, Num + 1 FROM tNums
WHERE Num <= 10

Se compararmos o plano de execução, as consultas são idênticas.

Consulta com função vs sem função

Entretanto, o Profiler nos revela algumas diferenças:

Trace (Consultas e Funções)

A execução do primeiro comando mostra que a função fnAumenta1 foi chamada 10 vezes (uma para cada linha do SELECT) e que a execução total teve de efetuar 7 leituras. A execução do segundo comando foi mais direta e resultou em apenas 3 leituras. Como a quantidade linhas envolvidas é bem pequena (apenas 10 registros), a recuperação dos dados foi praticamente instântanea em ambos os casos. A tabela abaixo mostra a evolução à medida que a quantidade de registros aumenta. Para essa tabela foi considerada apenas a execução do comando discartando-se os resultados (Tools, Options, Query Results, Results to Grid, Discard Results After Execution)

Função ? Registros CPU Leitura Tempo(ms)
Sim 100 0 5 3
Não 100 0 3 0
Sim 1.000 16 4 6
Não 1.000 0 4 2
Sim 10.000 31 20 53
Não 10.000 0 20 5
Sim 100.000 375 164 449
Não 100.000 31 164 31
Sim 1.000.000 4102 1615 4491
Não 1.000.000 219 1615 259

As primeiras diferenças começam a aparecer acima de 1.000 registros quando o uso da função começa a gastar ciclos de CPU mais significativos (em torno de 16).  Para a quantidade 10.000 registros percebe-se que a duração do comando com a função foi 10 vezes maior (53 /5). Para os volumes de 100.000 registros e 1.000.000 registros o processamento CPU com a solução baseada em function fica entre 10 a 20 vezes maior. O tempo de execução do comando também é aumentado nessa proporção.

Alguns irão argumentar que a diferença é imperceptível já que uma consulta normalmente retorna poucos registros. Sim, é verdade que para poucos registros, a diferença é mínima, mas é preciso lembrar que o uso da função não se aplica somente ao resultado da consulta. Se funções são utilizadas em tabelas com muitos registros, joins, etc, elas irão degradar o desempenho mesmo que o resultado final seja apenas algumas poucas linhas.

Funções Escalares e Formatação

Sempre defendi que a formatação dos dados para exibição não deve ficar no banco de dados. Qualquer aplicação corretamente modulada jamais deixaria as tarefas de formatação no banco de dados já que a camada de persistência tem como objetivo gravar e recuperar dados e a formatação fica para uma camada de exibição (ou no máximo de aplicação) e não em uma camada de persistência. Ainda assim, não é difícil encontrar consultas que formatam dados. Vejamos como o uso de funções escalares possibilita essa tarefa.

— Cria uma coluna de data
ALTER TABLE tNums ADD Data DATE NULL

— Atualiza a coluna de datas
UPDATE tNums SET Data = DATEADD(D,Num % 20,‘20110522’)

— Cria uma função para formatar a data
CREATE FUNCTION dbo.fnFormataData (@Data DATE)
RETURNS VARCHAR(30)
As
BEGIN
    DECLARE @DataRet VARCHAR(30)
    SET @DataRet = CONVERT(CHAR(10),@Data,103)
    SET @DataRet = @DataRet + ‘ (‘ +
        CASE @@DATEFIRST
            WHEN 1 THEN ‘Segunda-Feira’
            WHEN 2 THEN ‘Terça-Feira’
            WHEN 3 THEN ‘Quarta-Feira’
            WHEN 4 THEN ‘Quinta-Feira’
            WHEN 5 THEN ‘Sexta-Feira’
            WHEN 6 THEN ‘Sábado’
            WHEN 7 THEN ‘Domingo’
        END + ‘ )’
    RETURN (@DataRet)
END

A função dbo.fnFormataData converte uma data para o formato 103 (dd/mm/yyyy) e em seguida adiciona um sufixo com o dia da semana. Vejamos o desempenho com o uso de uma consulta sem formatação, com formatação na consulta e com o uso da função de formatação:

— Retorna as datas distintamente
SELECT DISTINCT Data FROM tNums

— Retorna as datas distintamente com formatação
SELECT DISTINCT
        CONVERT(CHAR(10),Data,103) + ‘ (‘ +
        CASE @@DATEFIRST
            WHEN 1 THEN ‘Segunda-Feira’
            WHEN 2 THEN
‘Terça-Feira’
            WHEN 3 THEN
‘Quarta-Feira’
            WHEN 4 THEN
‘Quinta-Feira’
            WHEN 5 THEN
‘Sexta-Feira’
            WHEN 6 THEN
‘Sábado’
            WHEN 7 THEN
‘Domingo’
        END + ‘ )’ FROM tNums

— Retorna as datas distintamente com função
SELECT DISTINCT dbo.fnFormataData(Data) FROM tNums

Trace (Formatação)

Como podemos ver, só a consulta pura gastou 200ms. Se colocarmos a formatação na consulta, esse tempo vai para 809ms. Ao meu ver é um enorme desperdício formatar dados na camada de banco. Veja que a consulta formatada teve 609ms (809 – 200) só para formatar dados, ou seja, do seu tempo total de execução, 25% do tempo foi para recuperar os dados e 75% do tempo para formatá-los. O uso da CPU para essa formatação foi quase 5 vezes maior (e olha que ainda fizemos um SCAN em uma tabela com 1.000.000 de linhas que representam apenas 3256 blocos nesse exemplo). Se os dados fossem devolvidos para que a aplicação formatasse, teríamos gastado menos recursos do SGBD e mais recursos do servidor de aplicação. Não há como “sumir” com o processamento, mas convenhamos que é mais correto deixar a formatação onde ela deve realmente ficar e vale a pena lembrar que servidores de aplicação escalam mais facilmente que um servidor de banco de dados, pois, dispõe de facilidades como NLB, Web Farm, Web Garden, etc.

A última consulta que usa a função escalar para formatar nem merece muita apreciação. Gastou mais de 20 vezes o total de CPU e quase 50 vezes mais lenta que a consulta de dados sem formatação. Notoriamente um grande desperdício. É comum reproduzí-lo quando se usa o DISTINCT em conjunto com formatação. Se for realmente imprescindível o uso da formatação, opte por fazê-la após o DISTINCT e não antes.

— Efetua o DISTINCT antes, formata depois
;WITH DadosDistintos As (
    SELECT DISTINCT Data FROM tNums)
SELECT DISTINCT dbo.fnFormataData(Data) FROM DadosDistintos

— Retorna as datas distintamente com função
SELECT DISTINCT dbo.fnFormataData(Data) FROM tNums

 Trace, DISTINCT e Formatação de Dados

Nesse caso, mesmo a UDF que era o pior dos casos teve seu desempenho muito reduzido tanto em termos de CPU quanto de duração do comando (algo em torno de 30 vezes mais rápido). Quando se usa DISTINCT, filtrar antes e formatar depois é uma excelente prática para ganhar desempenho (embora pouco conhecida e pouco utilizada).

Funções Escalares e Pregüiça

Alguns optam pelo uso de funções escalares para “escapar” de fazer um JOIN ou ainda “fugir” de um GROUP BY. Acredito que essa seja uma das piores utilizações para funções escalares.

— Cria uma função para contar quantas ocorrências existem por data
CREATE FUNCTION dbo.fnContaOcorrenciasPorData (@Data DATE)
RETURNS INT
As
BEGIN
    DECLARE @Total INT
    SET @Total = (
        SELECT COUNT(*) FROM tNums
        WHERE Data = @Data)
    RETURN(@Total)
END

— Cria uma tabela de eventos
CREATE TABLE Eventos (
    Data DATE,
    TipoEvento VARCHAR(50))
INSERT INTO Eventos VALUES (‘20110522’,‘Palestra Estagiários’)
INSERT INTO Eventos VALUES (‘20110523’,‘Café de Boas Vindas’)
INSERT INTO Eventos VALUES (‘20110524’,‘Oficina de Contabilidade’)
INSERT INTO Eventos VALUES (‘20110525’,‘Oficina de Contabilidade’)
INSERT INTO Eventos VALUES (‘20110526’,‘Oficina de Contabilidade’)
INSERT INTO Eventos VALUES (‘20110527’,‘Visita à Matriz’)
INSERT INTO Eventos VALUES (‘20110528’,‘Oficina de Risco Operacional’)
INSERT INTO Eventos VALUES (‘20110529’,‘Oficina de Risco Operacional’)
INSERT INTO Eventos VALUES (‘20110530’,‘Oficina de Risco Operacional’)
INSERT INTO Eventos VALUES (‘20110531’,‘Encerramento do Mês’)

— Usa a função para contar quantas ocorrências existem para cada evento
SELECT TipoEvento, dbo.fnContaOcorrenciasPorData(Data) FROM Eventos

— Faz a mesma consulta com um JOIN e um GROUP BY
SELECT TipoEvento, COUNT(Num) FROM Eventos
INNER JOIN tNums ON Eventos.Data = tNums.Data
GROUP BY TipoEvento

Trace, Joins e GROUP BY

E novamente as funções escalares perdem no quesito desempenho. O uso da função gastou quase cinco vezes mais ciclos de CPU e a duração do comando foi quase dez vezes maior. O detalhe mais interessante está na coluna Reads. A tabela tNums possui 3256 blocos e a tabela Eventos possui apenas dois blocos. O JOIN faz um SCAN em ambas e por isso o total de leituras na implementação baseada em JOIN e GROUP BY foi de 3258 blocos. Já a função, como precisa fazer a contagem para cada linha, ela leu várias vezes o mesmo bloco fazendo com que a quantidade de leituras fosse dez vezes maior. Esse é o custo da “pregüiça”.

Mantendo a reusabilidade

Se apenas o desempenho for levado em consideração, provavelmente a recomendação geral seria não utilizar funções escalares e deixar a lógica na própria consulta ou ainda na aplicação. Entretanto, as funções escalares provêm o benefício da reusabilidade, pois, uma vez criadas, podem ser reaproveitadas em várias consultas sem a necessidade de remotar sua lógica em cada consulta. É visível que isso custa desempenho, mas é possível manter o benefício da reusabilidade reduzindo essa penalidade. Vejamos como fazê-lo.

— Cria uma tabela de cotações
CREATE TABLE Cotacoes (
    DataRef DATE NOT NULL,
    Taxa DECIMAL(7,4))

— Cadastra algumas taxas
INSERT INTO Cotacoes VALUES (‘20110501’,0.0142)
INSERT INTO Cotacoes VALUES (‘20110601’,0.0157)
INSERT INTO Cotacoes VALUES (‘20110701’,0.0167)
INSERT INTO Cotacoes VALUES (‘20110801’,0.0135)
INSERT INTO Cotacoes VALUES (‘20110901’,0.0103)
INSERT INTO Cotacoes VALUES (‘20111001’,0.0121)
INSERT INTO Cotacoes VALUES (‘20111101’,0.0158)
INSERT INTO Cotacoes VALUES (‘20111201’,0.0174)

— Monta um função de cálculo de montante final
CREATE FUNCTION dbo.FnRetornaRentabilidade (@DataInicio DATE, @Dias SMALLINT)
RETURNS DECIMAL(9,6)
As
BEGIN

— Inicializa a variável para calcular a taxa
DECLARE @Taxa DECIMAL(9,6)

— Calcula o mês de referência
DECLARE @DataRef DATE
SET @DataRef = (DATEADD(D,1-DAY(@DataInicio),@DataInicio))

— Calcula a taxa proporcional em relação ao início do mês de referência
DECLARE @DiasFimMes SMALLINT
SET @DiasFimMes = DATEDIFF(D,@DataInicio,DATEADD(M,1,@DataRef))

SET @Taxa = (SELECT Taxa FROM Cotacoes WHERE DataRef = @DataRef)
SET @Taxa = POWER(1 + @Taxa,@DiasFimMes / CAST(DATEDIFF(D,@DataRef,DATEADD(M,1,@DataRef)) As Decimal(7,4))) – 1

— Calcula o restante dos dias úteis
DECLARE @DiasRestantes SMALLINT
SET @DiasRestantes = @Dias – @DiasFimMes

— Aumenta a data de referência em um mês
SET @DataRef = DATEADD(M,1,@DataRef)

WHILE (@DiasRestantes > 0)
BEGIN
    — Verifica se irá fechar o próximo mês
    IF @DiasRestantes > DATEDIFF(D,@DataRef,DATEADD(M,1,@DataRef))
    BEGIN
        SET @Taxa = ((1 + @Taxa) * (1 + (SELECT Taxa FROM Cotacoes WHERE DataRef = @DataRef))) – 1
        SET @DiasRestantes = @DiasRestantes – DATEDIFF(D,@DataRef,DATEADD(M,1,@DataRef))
        SET @DataRef = DATEADD(M,1,@DataRef)
    END
    ELSE
    BEGIN
        SET @Taxa = (1 + @Taxa) * POWER(1 + (SELECT Taxa FROM Cotacoes WHERE DataRef = @DataRef),
        @DiasRestantes / CAST(DATEDIFF(D,@DataRef,DATEADD(M,1,@DataRef)) As Decimal(7,4)))
        SET @DiasRestantes = 0
    END
END

— Se for menos de 40 dias, não há rendimento
SET @Taxa = CASE WHEN @Dias < 40 THEN 0 ELSE @Taxa – 1 END

RETURN(@Taxa)

END

— Testa a função
SELECT dbo.FnRetornaRentabilidade(‘20110511’,40)

De acordo com as taxas oferecidas, se um investimento for feito 11/05/2011 e durar 40 dias, ele irá render 1,9592% que correspondem a 20 dias na rentabilidade de maio 1,42% e 20 dias na rentabilidade junho (1,57%). Não vou entrar nos detalhes desse cálculo (isso fica para uma outra oportunidade), mas ao contrário das abordagens anteriores, esse é o tipo de lógica que, embora possível de ser incorporada a uma consulta diretamente, normalmente ficará dentro de uma função escalar, pois, será bem oportuno que esse cálculo possa ser encapsulado e reutilizado. Será que é possível amenizar a perda de desempenho provocada pelo uso da função linha a linha ?

— Cria uma tabela de aplicações financeiras
CREATE TABLE AppFin (
    ClienteID INT NOT NULL,
    DataApp DATE NOT NULL,
    Valor MONEY NOT NULL,
    DiasCorridos SMALLINT NOT NULL)

— Restringe o investimento a no mínimo 40 dias
ALTER TABLE AppFin ADD CONSTRAINT CK_DiasCorridos CHECK (DiasCorridos >= 40)

— Insere alguns investimentos de forma aleatória
DECLARE @qtdInvestimentos INT
DECLARE @MaxClienteID INT
DECLARE @MaxDataApp TINYINT
DECLARE @MaxValor MONEY
DECLARE @MaxDiasCorridos TINYINT

SET @qtdInvestimentos = 500000
SET @MaxClienteID = 1000
SET @MaxDataApp = 5
SET @MaxValor = 1000000
SET @MaxDiasCorridos = 2

DECLARE @i INT
SET @i = 1

WHILE @i <= @qtdInvestimentos
BEGIN
    INSERT INTO
AppFin VALUES (
        ABS(CHECKSUM(NEWID())) % @MaxClienteID,
        DATEADD(M,ABS(CHECKSUM(NEWID())) % 5,‘20110511’),
        ABS(CHECKSUM(NEWID())) % @MaxValor / 100.0000,
        ((ABS(CHECKSUM(NEWID())) % @MaxDiasCorridos + 1) * 30) + 10)
    SET @i = @i + 1
END

— Efetua a consulta para retornar a rentabilidade
SELECT
    ClienteID, DataApp, Valor, DiasCorridos,
    dbo.FnRetornaRentabilidade(DataApp, DiasCorridos)
FROM AppFin

Trace, UDF e Reusabilidade

Considerando que a tabela AppFin possui 1697 páginas, os resultados do cálculo da do foram expressivos. Para realizar a consulta em questão foram necessárias 3752278 páginas. Isso significa mais de 2.200 vezes o total de páginas da tabela além de um gasto de CPU e tempo muito significativos (quase 30 segundos). Será que há otimização possível ?

No exemplo, a rentabilidade é influenciada basicamente pelos parâmetros DataApp e DiasCorridos dispensado as demais colunas da tabela AppFin. Possivelmente haverá várias repetições dessa combinação. Vejamos quantas combinações distintas existem nessas duas colunas.

— Verifica quantas combinações distintas existem para o cálculo da rentabilidade
SELECT DISTINCT DataApp, DiasCorridos FROM AppFin

No meu exemplo foram retornadas apenas 10 ocorrências. Penso que calcular a rentabilidade para 10 ocorrências é possivelmente mais eficiente que calcular a rentabilidade para 500.000 ocorrências principalmente porque das 500.000 temos apenas dez tipos. Minha proposta seria efetuar o cálculo separadamente para essas dez e posteriormente efetuar um JOIN com as outras 500.000. Vejamos se essa abordagem funciona.

— Calcula as taxas distintamente para reduzir o número de cálculos
— Posteriormente efetua um JOIN do Resultados
;WITH
    AppDistintas (DataApp, DiasCorridos) As
        (SELECT DISTINCT DataApp, DiasCorridos FROM AppFin),
    RentabilidadeCalculada (DataApp, DiasCorridos, Rentabilidade) As
        (SELECT
            DataApp, DiasCorridos, dbo.FnRetornaRentabilidade(DataApp, DiasCorridos)
            FROM AppDistintas)

SELECT
    A.ClienteID, A.DataApp, A.Valor, A.DiasCorridos, R.Rentabilidade
FROM AppFin As A
INNER JOIN RentabilidadeCalculada As R ON A.DataApp = R.DataApp AND A.DiasCorridos = R.DiasCorridos

Trace, UDF e Reusabilidade

Embora as CTEs tenham me ajudado no exemplo da formatação, dessa vez elas me deixaram na mão. Os indicadores (CPU, tempo e leituras) são muito próximos com uma leve desvantagens para as CTEs. Ainda assim, a idéia de fazer 10 vezes e não 500.000 ainda me parece mais inteligente. Vejamos agora o resultado com uma tabela temporária.

— Calcula as taxas distintamente para reduzir o número de cálculos
— Posteriormente efetua um JOIN do Resultados
— Utiliza uma TMP para persistir os resultados temporariamente
;WITH
    AppDistintas (DataApp, DiasCorridos) As
        (SELECT DISTINCT DataApp, DiasCorridos FROM AppFin),
    RentabilidadeCalculada (DataApp, DiasCorridos, Rentabilidade) As
        (SELECT
            DataApp, DiasCorridos, dbo.FnRetornaRentabilidade(DataApp, DiasCorridos)
            FROM AppDistintas)

SELECT DataApp, DiasCorridos, Rentabilidade INTO #TMP FROM RentabilidadeCalculada

SELECT
    A.ClienteID, A.DataApp, A.Valor, A.DiasCorridos, R.Rentabilidade
FROM AppFin As A
INNER JOIN #TMP As R ON A.DataApp = R.DataApp AND A.DiasCorridos = R.DiasCorridos

Trace, UDF e Reusabilidade

Dessa vez os resultados foram bastante significativos, além de reduzir as consultas anteriores de 27 segundos para pouco mais de meio segundo, tivemos um ganho expressivo em termos de CPU (menos de 3% do consumo anterior) e uma economia de leituras na casa de 99% (3602 conta 3752276). Definitivamente, fico com essa implementação. Ela mantem a resusabilidade sem comprometer o desempenho. A lógica é simples, se a função precisa ser feita linha a linha, é melhor fazer o menor número de vezes possível.

Confesso que ainda não descobri o porquê das CTEs terem falhado nesse caso. Normalmente são alternativas mais interessantes que o uso de tabelas temporárias, mas enfim… Isso é assunto para uma nova pesquisa e aprendizado.

O uso de funções escalares customizadas não é ruim no SQL Server. Pode encapsular lógicas complexas, facilitar a manutenção do código, prover mecanismos de segurança entre várias outras utilidades. Entretanto, uma boa implementação não consiste no seu uso indiscriminado, mas sim na sua aplicação de forma planejada conhecendo suas vantagens, as penalidades associadas e a forma de lidar com elas.

[ ]s,

Gustavo

Consultas parametrizadas, ISNULL e SQL dinâmica

Bom Dia Pessoal,

Por várias vezes nos fóruns e em algumas situações que presencio na vida real, vejo implementações para execução de consultas parametrizadas com base em vários parâmetros. É sem dúvida uma necessidade muito comum, pois, se uma determinada entidade (tabela) possui várias características (colunas), é quase natural que se pesquise as instâncias (registros) dessa entidade com base em uma, ou duas, ou N características de forma individual ou simultânea. Não é errado necessitar de tal mecanismo de pesquisa, mas o que vejo é que muitas implementações o fazem de forma completamente equivocada e muitas vezes pouco performática. Como poucos registros em um ambiente de desenvolvimento e homologação a diferença é imperceptível, mas à medida em que o volume em produção aumenta, a dificuldade de escalar é nata e muitas vezes a solução não é aumentar a infraestrutura. Vejamos então o que as implementações de consultas parametrizadas devem e não devem fazer.

Inicialmente criarei uma tabela com várias colunas que são potenciais características de pesquisa.

— Cria uma tabela de Pessoas
CREATE TABLE Pessoas (
    ID INT, Nome VARCHAR(100), SobreNome VARCHAR(100),
    Filhos TINYINT, AnoNascimento SMALLINT,
    Cidade VARCHAR(100), UF CHAR(2))

— Insere quinze registros
INSERT INTO Pessoas VALUES (01,‘João’,‘Silva’,2,1950,‘São Paulo’,‘SP’)
INSERT INTO Pessoas VALUES (02,‘João’,‘Gonçalves’,1,1990,‘Porto Alegre’,‘RS’)
INSERT INTO Pessoas VALUES (03,‘João’,‘Leite’,0,1992,‘Natal’,‘RN’)
INSERT INTO Pessoas VALUES (04,‘Daniel’,‘Antunes’,0,1986,‘Diadema’,‘SP’)
INSERT INTO Pessoas VALUES (05,‘Daniel’,‘Mendes’,1,1979,‘Manaus’,‘AM’)
INSERT INTO Pessoas VALUES (06,‘Daniela’,‘Petrovisk’,1,1976,‘Salvador’,‘BA’)
INSERT INTO Pessoas VALUES (07,‘Danilo’,‘Silva’,3,1965,‘Brasília’,‘DF’)
INSERT INTO Pessoas VALUES (08,‘Peter’,‘Parker’,0,1989,‘Fortaleza’,‘CE’)
INSERT INTO Pessoas VALUES (09,‘Isabela’,‘Costa’,2,1984,‘São Paulo’,‘SP’)
INSERT INTO Pessoas VALUES (10,‘Regiane’,‘Meira’,5,1945,‘Recife’,‘PE’)
INSERT INTO Pessoas VALUES (11,‘Maíra’,‘Gonçalves’,4,1982,‘São Paulo’,‘SP’)
INSERT INTO Pessoas VALUES (12,‘Nayara’,‘Silva’,2,1950,‘Brasília’,‘DF’)
INSERT INTO Pessoas VALUES (13,‘Patríca’,‘Gomides’,2,1950,‘São Paulo’,‘SP’)
INSERT INTO Pessoas VALUES (14,‘Natália’,‘Arruda’,2,1950,‘Rio de Janeiro’,‘RJ’)
INSERT INTO Pessoas VALUES (15,‘Márcia’,‘Alves’,0,1983,‘Brasília’,‘DF’)

— Completa até 10.000 de registros fixos de São Paulo
DECLARE @i INT
SET @i = 16

WHILE @i <= 10000
BEGIN
    INSERT INTO Pessoas VALUES (@i,‘Fulano’,‘Silva’,0,1969,‘São Paulo’,‘SP’)
    SET @i = @i + 1
END

— Insere 200 registros com sobrenome “Mendes”
— Insere 200 pessoas que nasceram em 1979
DECLARE @i INT
SET @i = 10001

WHILE @i <= 10200
BEGIN
    INSERT INTO Pessoas VALUES (@i,‘Osvaldo’,‘Mendes’,1,1987,‘Brasília’,‘DF’)
    INSERT INTO Pessoas VALUES (@i + 200,‘Fabiano’,‘Rocha’,0,1979,‘Rio de Janeiro’,‘RJ’)
    SET @i = @i + 1
END

— Cria índices nas colunas potencialmente pesquisáveis e com seletividade
CREATE INDEX IX_SobreNome ON Pessoas (SobreNome)
CREATE INDEX IX_Filhos ON Pessoas (Filhos)
CREATE INDEX IX_AnoNascimento ON Pessoas (AnoNascimento)
CREATE INDEX IX_Cidade ON Pessoas (Cidade)
CREATE INDEX IX_UF ON Pessoas (UF)

O exemplo não retrata 100% da vida real, pois, o cadastro não seria tão concentrado assim. Entretanto, meu objetivo não é bem reproduzir um cadastro, mas sim mostrar que a eficiência dos índices pode ser comprometida por uma determinada implementação (mesmo que ele possua seletividade). Vejamos algumas consultas que possam usufruir da seletividade desses índices:

— Verifica quem nasceu em 1965
SELECT * FROM Pessoas WHERE AnoNascimento = 1965

Consulta Ano de Nascimento

— Verifica quem tem o sobrenome Gonçalves
SELECT * FROM Pessoas WHERE SobreNome = ‘Gonçalves’

Consulta Sobrenome

— Verifica quem tem o sobrenome Mendes e nasceu em 1979
SELECT * FROM Pessoas WHERE SobreNome = ‘Mendes’ AND AnoNascimento = 1979

Consulta SobreNome e Ano Nascimento

— Verifica quem tem 1 filho e nasceu em Brasília
SELECT * FROM Pessoas WHERE Filhos = 1 AND Cidade = ‘Brasília’

Consulta Filhos e Cidade

Como é possível perceber, todas as consultas utilizaram os índices. Algumas precisaram combiná-los (Hash Match Inner Join), mas no geral, nenhuma fez nenhuma varredura.

Tradicionalmente, uma tabela de pessoas possui muitas características que servem de argumento de pesquisa e será bem comum que uma aplicação recebe parâmetros para uma, duas ou até todas as características. O comportamento esperado é bem simples. Se a característica for informada, ela deve ser considerada como filtro. Se a característica não for informada, então ela não deve ser considerada como filtro. A seguir uma implementação típica para atender essa necessidade.

— Cria uma procedure de pesquisa
CREATE PROCEDURE UspRecuperaPessoas
    @ID INT = NULL,
    @Nome VARCHAR(100) = NULL,
    @SobreNome VARCHAR(100) = NULL,
    @Filhos TINYINT = NULL,
    @AnoNascimento SMALLINT = NULL,
    @Cidade VARCHAR(100) = NULL,
    @UF CHAR(2) = NULL
As

— Recupera Pessoas
SELECT * FROM Pessoas
WHERE
    ID = ISNULL(@ID,ID) AND
    Nome = ISNULL(@Nome,Nome) AND
    SobreNome = ISNULL(@SobreNome,SobreNome) AND
    Filhos = ISNULL(@Filhos,Filhos) AND
    AnoNascimento = ISNULL(@AnoNascimento,AnoNascimento) AND
    Cidade = ISNULL(@Cidade,Cidade) AND
    UF = ISNULL(@UF,UF)

Essa é uma implementação típica de consultas parametrizadas. Simples de construir e muito fácil de manter. Basta colocar cada coluna com a função ISNULL e a comparação com o valor da variável correspondente. Caso um coluna seja alterada, retirada ou adicionada basta mudar a cláusula WHERE para refletir essa alteração. Vejamos agora se a eficiência na criação e manutenção se mantém no desempenho.

— Verifica quem nasceu em 1965
EXEC UspRecuperaPessoas @AnoNascimento = 1965

— Verifica quem tem o sobrenome Gonçalves
EXEC UspRecuperaPessoas @SobreNome = ‘Gonçalves’

— Verifica quem tem o sobrenome Mendes e nasceu em 1979
EXEC UspRecuperaPessoas @SobreNome = ‘Mendes’, @AnoNascimento = 1979

— Verifica quem tem 0 filhos e nasceu em Brasília
EXEC UspRecuperaPessoas @Filhos = 1, @Cidade = ‘Brasília’

Como podemos ver, todas as execuções tem o mesmo plano

TABLE SCAN

Ao contrário do que poderia parecer, nenhuma das consultas que anteriormente usavam índices e eram eficientes comportou-se da mesma forma dentro da Stored Procedure. Não é difícil imaginar o porquê disso acontecer. Já é conhecido que a utilização de funções sobre colunas força tende a mudar a execução para um SCAN. Ainda assim, vejamos a reescrita da procedure com uma semântica parecida, mas sem o ISNULL.

— Cria uma procedure de pesquisa
ALTER PROCEDURE UspRecuperaPessoas
    @ID INT = NULL,
    @Nome VARCHAR(100) = NULL,
    @SobreNome VARCHAR(100) = NULL,
    @Filhos TINYINT = NULL,
    @AnoNascimento SMALLINT = NULL,
    @Cidade VARCHAR(100) = NULL,
    @UF CHAR(2) = NULL
As

— Recupera Pessoas
SELECT * FROM Pessoas
WHERE
    (ID = @ID OR @ID IS NULL) AND
    (Nome = @Nome OR @Nome IS NULL) AND
    (SobreNome = @SobreNome OR @SobreNome IS NULL) AND
    (Filhos = @Filhos OR @Filhos IS NULL) AND
    (AnoNascimento = @AnoNascimento OR @AnoNascimento IS NULL) AND
    (Cidade = @Cidade OR @Cidade IS NULL) AND
    (UF = @UF OR @UF IS NULL)

A semântica dessa versão da procedure é exatamente igual à versão anterior, mas os resultados são um pouco diferentes:

— Verifica quem nasceu em 1965
EXEC UspRecuperaPessoas @AnoNascimento = 1965

— Verifica quem tem o sobrenome Gonçalves
EXEC UspRecuperaPessoas @SobreNome = ‘Gonçalves’

— Verifica quem tem o sobrenome Mendes e nasceu em 1979
EXEC UspRecuperaPessoas @SobreNome = ‘Mendes’, @AnoNascimento = 1979

— Verifica quem tem 0 filhos e nasceu em Brasília
EXEC UspRecuperaPessoas @Filhos = 1, @Cidade = ‘Brasília’

De uma forma geral, todas as execuções tiveram o mesmo plano e dessa vez não foi um TABLE SCAN

Consultas via SP

A primeira execução utilizava como argumento de pesquisa a data de nascimento. O índice IX_AnoNascimento foi utilizado, mas ao contrário da consulta, a execução da SP fez um SCAN no índice e não um Seek no índice como feito anteriormente. Já a segunda execução utiliza como argumento de pesquisa o sobrenome e não o endereço, mas mesmo assim, a procedure mostrou o plano de execução com o índice IX_AnoNascimento. Isso deve-se ao fato de que a stored procedure compilou um plano de execução e o utilizou sucessivamente para cada uma das quatro execuções. Para uma consulta trivial, esse comportamento é até benéfico, mas para consultas parametrizadas, fica evidente que essa não é uma boa implementação. Para forçar com que a stored procedure produza um novo plano a cada execução devemos usar o WITH RECOMPILE. Normalmente essa é uma cláusula esquecida no momento de se construir uma procedure, mas veremos que ela pode fazer muita diferença.

— Cria uma procedure de pesquisa
ALTER PROCEDURE UspRecuperaPessoas
    @ID INT = NULL,
    @Nome VARCHAR(100) = NULL,
    @SobreNome VARCHAR(100) = NULL,
    @Filhos TINYINT = NULL,
    @AnoNascimento SMALLINT = NULL,
    @Cidade VARCHAR(100) = NULL,
    @UF CHAR(2) = NULL
WITH RECOMPILE
As

— Recupera Pessoas
SELECT * FROM Pessoas
WHERE
    (ID = @ID OR @ID IS NULL) AND
    (Nome = @Nome OR @Nome IS NULL) AND
    (SobreNome = @SobreNome OR @SobreNome IS NULL) AND
    (Filhos = @Filhos OR @Filhos IS NULL) AND
    (AnoNascimento = @AnoNascimento OR @AnoNascimento IS NULL) AND
    (Cidade = @Cidade OR @Cidade IS NULL) AND
    (UF = @UF OR @UF IS NULL)

Agora que a stored procedure foi alterada, vamos a uma nova tentativa:

— Verifica quem nasceu em 1965
EXEC UspRecuperaPessoas @AnoNascimento = 1965

— Verifica quem tem o sobrenome Gonçalves
EXEC UspRecuperaPessoas @SobreNome = ‘Gonçalves’

— Verifica quem tem o sobrenome Mendes e nasceu em 1979
EXEC UspRecuperaPessoas @SobreNome = ‘Mendes’, @AnoNascimento = 1979

— Verifica quem tem 0 filhos e nasceu em Brasília
EXEC UspRecuperaPessoas @Filhos = 1, @Cidade = ‘Brasília’

Com exceção das duas últimas execuções que resultam em TABLE SCAN, parece que o RECOMPILE gerou um plano mais adequado para as duas primeiras execuções:

Consultas via SP com o RECOMPILE

Ainda que os índices corretos tenham sido utilizados, eles ainda não foram usados de forma performática, pois, ao invés do Seek (pesquisa) utilizada nas consultas, a stored procedure preferiu fazer um Scan (varredura) tornando o resultado menos eficiente. Podemos ver isso muito claramente com o uso do SET STATISTICS IO.

— Ativa as estatísticas de IO
SET STATISTICS IO ON

— Faz a consulta via Query
SELECT * FROM Pessoas WHERE AnoNascimento = 1965

— Executa a SP
EXEC UspRecuperaPessoas @AnoNascimento = 1965

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

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

Os resultados mostram o quanto a stored procedure parametrizada foi menos eficiente. Na consulta são feitas três leituras lógicas, ou seja, três páginas de dados contra 24 páginas de dados no uso da stored procedure. O Seek no índice é 8 vezes mais eficiente em termos de I/O do que o SCAN. Vejamos agora o uso de uma procedure que usa SQL dinâmica.

ALTER PROCEDURE UspRecuperaPessoas
    @ID INT = NULL,
    @Nome VARCHAR(100) = NULL,
    @SobreNome VARCHAR(100) = NULL,
    @Filhos TINYINT = NULL,
    @AnoNascimento SMALLINT = NULL,
    @Cidade VARCHAR(100) = NULL,
    @UF CHAR(2) = NULL
As

DECLARE @cmdsql As NVARCHAR(4000)

SET @cmdsql = N’SELECT * FROM Pessoas’ +
                N’ WHERE 1 = 1′
      + CASE WHEN @ID IS NOT NULL THEN
          N’ AND ID = @pID’ ELSE N” END
      + CASE WHEN @Nome IS NOT NULL THEN
          N’ AND Nome = @pNome’ ELSE N” END
      + CASE WHEN @SobreNome IS NOT NULL THEN
          N’ AND SobreNome = @pSobreNome’ ELSE N” END
      + CASE WHEN @Filhos IS NOT NULL THEN
          N’ AND Filhos = @pFilhos’ ELSE N” END
      + CASE WHEN @AnoNascimento IS NOT NULL THEN
          N’ AND AnoNascimento = @pAnoNascimento’ ELSE N” END
      + CASE WHEN @Cidade IS NOT NULL THEN
          N’ AND Cidade = @pCidade’ ELSE N” END
      + CASE WHEN @UF IS NOT NULL THEN
          N’ AND UF = @pUF’ ELSE N” END;

EXEC sp_executesql @cmdsql,
N’@pID As INT, @pNome As VARCHAR(100), @pSobreNome VARCHAR(100), @pFilhos TINYINT, @pAnoNascimento SMALLINT, @pCidade VARCHAR(100), @pUF CHAR(2)’,
    @pID = @ID, @pNome = @Nome, @pSobreNome = @SobreNome,
    @pFilhos  = @Filhos, @pAnoNascimento = @AnoNascimento,
    @pCidade = @Cidade, @pUF = @UF

O uso da SQL dinâmica faz a montagem prévia do comando como um texto e posteriormente dispara sua execução através da instrução sp_executesql ou o EXEC(). Vejamos agora os resultados:

— Ativa as estatísticas de IO
SET STATISTICS IO ON

— Faz a consulta via Query
SELECT * FROM Pessoas WHERE AnoNascimento = 1965

— Executa a SP
EXEC UspRecuperaPessoas @AnoNascimento = 1965

Uso da SQL dinâmica

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

Table ‘Pessoas’. Scan count 1, logical reads 3, 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 consulta direta e a stored procedure tiveram exatamente o mesmo plano e comportaram-se de forma eficiente, ou seja, o índice foi utilizado e foi realizado um Seek e não um Scan demandando a leitura de apenas três páginas de dados. As demais execuções também terão os mesmos resultados das consultas. Isso ocorre porque a instrução é montada e executada dinamicamente e nessa montagem, a estrutura do comando é eficiente sem fazer uso de funções como o ISNULL ou operadores como o OR. O único efeito colateral dessa implementação é que o fato da montagem ser baseada em strings pode potencialmente introduzir riscos de SQL Injection e isso demandará do implementador a cautela em validar corretamente seus parâmetros de entrada bem alguns IFs para verificar se há alguma string maliciosa dentro do código. Acredito que seja um trabalho mínimo e realizado uma única vez em benefício das milhares de execuções de uma SP.

Após a demonstração dos cuidados que uma consulta parametrizada dentro de uma SP deva ter, indico as seguintes recomendações:

  • Não utilize funções ISNULL contra colunas em uma cláusula WHERE
  • Não utilize comparações do tipo Coluna = @Variavel OR @Variavel IS NULL
  • Se sua procedure possibilita várias combinações, coloque o WITH RECOMPILE para não gerar planos inadequados
  • Dê preferência a stored procedures dinâmicas nesse tipo de situação, mas previna-se contra o SQL Injection

Há muitas informações adicionais e uma explicação mais detalhada no livro do Itzik Ben Gan (Inside SQL Server 2005: T-SQL Programming). Há inclusive uma versão desse livro para SQL Server 2008. Recomendo a leitura.

[ ]s,

Gustavo

Service Broker – Depois da apresentação (SQL Server DF)

Bom Dia Pessoal,

O tempo não permitiu, mas queria agradecer a todos que ficaram até o final das apresentações de Service Broker realizadas no auditório da Microsoft com o apoio do grupo SQL Server DF. Primeiramente me surpreendeu a presença de pessoas de outro estado vindo só para essa apresentação. Sempre ficamos restritos ao público Brasília e acho que essa foi a primeira vez que isso acontece. Fico muito lisonjeado com a visita e aguardo mais pessoas para os próximos encontros.

Como eu já imaginava, falei um pouco mais do que o tempo permitia, mas o que eu não imaginava é que a parte de tópicos avançados rendesse tanta discussão. Eu acho ótimo apresentações que rendem discussões, pois, sinto que assim elas tornam-se no mínimo interessante e gostei muito do nível das perguntas.

Pretendo usar o Camtasia para fazer uma gravação em breve, pois, muitos me enviaram emails pedindo para que a apresentação fosse gravada, mas infelizmente a logística não permitiu dessa vez. Ao interessados em pegar as apresentações e os scripts utilizados, disponibilizei no meu SkyDrive. Segue o link para download:

http://cid-f4f5c630410b9865.office.live.com/self.aspx/ProjetosSQLServer/20110427%5E_20110510%5E_Service%20Broker.rar

[ ]s,

Gustavo

Service Broker – Conhecendo um pouco mais…

Boa Tarde Pessoal,

Já faz um tempo que não posto nada por aqui. Após a mudança do Live para o WordPress, eu achei que as coisas iam melhorar, mas não saíram bem como eu imaginava. Por uma lado, o WordPress tem uma infinidade de recursos que o Live estava muito longe de ter. Não posso me queixar, pois, mesmo sem postar nada, o índice de visitas e comentários do blog aumentou consideravelmente. Por outro lado, eu gostava muito da simplicidade e flexibilidade do live para mudar a apresentação final. Não sei se o WordPress permite a simplicidade que eu tinha antes, mas vou continuar procurando.

Hoje o post é dedicado aos meus alunos, amigos de profissão, fãs de SQL Server que estejam na área e principalmente aos participantes do grupo SQL Server DF. No dia 27/04 (próxima quarta-feira), estarei no auditório da Microsoft em Brasília falando sobre o Service Broker. Já há alguns meses estou envolvido em um projeto grande que usa essa tecnologia, além de ter outros projetos ainda maiores envolvendo o Service Broker por vir. Quanto mais mexo, mais me interesso por essa feature que embora pouco conhecida, é sem dúvida poderosa em diversos cenários e não sei porque é tão pouco explorada.

Já apresentei dois Webcasts sobre o assunto, dois vídeos no MSDN Experience e publiquei um artigo sobre o assunto na revista Mundo .NET. Após revisar alguns desses materiais (em especial os primeiros) percebi que não explorei da melhor forma o Service Broker e de fato ele pode ser muito útil. Pois bem, resolvi começar do zero e fazer uma nova apresentação, como novos exemplos e cenários mais aplicáveis. Teremos dois encontros sobre o tema:

Encontro X – Introdução ao Service Broker
Local:
Auditório da Microsoft – Edifício Corporate Financial Center, sala 302
Data e horário: 27/04/2011, 17:00h ~ 19:00h
Tema: Introdução ao Service Broker
Agenda:

  • Introdução ao SQL Service Broker e Mensageria de dados no SQL Server
  • Uma aplicação prática do Service Broker em cenários de vários bancos de dados na mesma instância

Descrição: Essa é uma apresentação sobre o Service Broker e aplicações de mensageria utilizando o SQL Server onde serão apresentados os fundamentos de aplicações baseadas em mensageria, conceitos e benefícios do Service Broker bem como um cenário prático de utilização do mesmo. Essa sessão irá servir de introdução para o próximo encontro, onde serão apresentados recursos e implementações envolvendo instâncias diferentes.

Encontro XI – Recursos avançados do Service Broker
Local:
Auditório da Microsoft – Edifício Corporate Financial Center, sala 302
Data e horário: 10/05/2011, 17:00h ~ 19:00h
Tema: Recursos avançados do Service Broker
Agenda:

  • Utilizando o SQL Service Broker e Mensageria de dados no SQL Server em cenários distribuídos
  • Uma aplicação prática do Service Broker em cenários de múltiplos bancos em instâncias diferentes

Descrição: Essa é uma apresentação sobre implementações avançadas do Service Broker que apresentará o uso dessa feature em cenários complexos envolvendo processamento distribuído em várias instâncias. Trata-se de uma continuação da sessão do encontro anterior que fez uma apresentação do Service Broker.

Não sei se o evento será transmitido como os demais, mas os interessados em assistí-lo presencialmente não deixem de enviar um email para luan.moreno@srnimbus.com.br, pois a capacidade do auditório não é ilimitado.

Vejo vocês lá…

Abs,

Gustavo

Exclusões em cascata e auto-referência no SQL Server

Oi Pessoal,

Ainda estou me acostumando com a mudança do blog (algumas coisas melhoraram muito, mas estou sentindo muita falta de outras). Iniciando o primeiro post do ano de 2011 (e o primeiro que não foi importado do Live), vou apontar uma rápida solução para uma thread que vi no fórum do MSDN. A thread referia-se a impossibilidade de utilizar a exclusão em cascata para auto-relacionamentos. Admito que nunca precisei fazer algo desse tipo, pois, normalmente a exclusão em cascata não é utilizada e quase sempre tabelas que se auto-relacionam são tabelas de referência e não são apagadas. Ainda assim, vejamos o que pode ser feito nessa situação.

— Cria uma tabela de Empregados
CREATE TABLE Empregados (
    EmpregadoID INT NOT NULL,
    EmpregadoNome VARCHAR(50) NOT NULL,
    SuperiorID INT NULL)

— Cria as Constraints
ALTER TABLE Empregados ADD CONSTRAINT PK_Empregado PRIMARY KEY (EmpregadoID)
ALTER TABLE Empregados ADD CONSTRAINT FK_Empregado_Empregado
    FOREIGN KEY (SuperiorID) REFERENCES Empregados (EmpregadoID)

— Insere alguns registros
INSERT INTO Empregados (EmpregadoID, EmpregadoNome, SuperiorID) VALUES (1,‘Sálvio’,NULL)
INSERT INTO Empregados (EmpregadoID, EmpregadoNome, SuperiorID) VALUES (2,‘Hélio’,1)
INSERT INTO Empregados (EmpregadoID, EmpregadoNome, SuperiorID) VALUES (3,‘Dênis’,2)
INSERT INTO Empregados (EmpregadoID, EmpregadoNome, SuperiorID) VALUES (4,‘Júnior’,2)
INSERT INTO Empregados (EmpregadoID, EmpregadoNome, SuperiorID) VALUES (5,‘Marcos’,3)
INSERT INTO Empregados (EmpregadoID, EmpregadoNome, SuperiorID) VALUES (6,‘Bruno’,3)
INSERT INTO Empregados (EmpregadoID, EmpregadoNome, SuperiorID) VALUES (7,‘Paulo’,5)

Podemos ver as seguintes relações hierárquicas:

  • Sálvio -> Hélio
    Sálvio -> Hélio -> Dênis
    Sálvio -> Hélio -> Dênis -> Marcos
    Sálvio -> Hélio -> Dênis -> Marcos -> Paulo
    Sálvio -> Hélio -> Júnior
  • Hélio -> Dênis
    Hélio -> Dênis -> Marcos
    Hélio -> Dênis -> Marcos -> Paulo
    Hélio -> Júnior
  • Dênis -> Marcos
    Dênis -> Marcos -> Paulo
  • Marcos -> Paulo

Agora que os registros estão populados, vamos tentar efetuar uma exclusão:

— Tenta excluir o registro do empregado Hélio
DELETE FROM Empregados WHERE EmpregadoID = 2

Como era de se esperar, a existência de subordinados ao empregado Hélio representadas pelo auto-relacionamento gerou um erro na exclusão:

Msg 547, Level 16, State 0, Line 2
The DELETE statement conflicted with the SAME TABLE REFERENCE constraint “FK_Empregado_Empregado”. The conflict occurred in database “tempdb”, table “dbo.Empregados”, column ‘SuperiorID’.
The statement has been terminated.

Admitindo que a exclusão de um empregado deva excluir também os seus subordinados, poderíamos ativar a exclusão em cascata.

— Remove a constraint FK anterior
ALTER TABLE Empregados DROP CONSTRAINT FK_Empregado_Empregado

— Recria a constraint com a exclusão em cascata
ALTER TABLE Empregados ADD CONSTRAINT FK_Empregado_Empregado
    FOREIGN KEY (SuperiorID) REFERENCES Empregados (EmpregadoID)
    ON DELETE CASCADE

A exclusão da constraint ocorre com sucesso, mas a criação com a exclusão em cascata e a auto-referência gera um erro:

Msg 1785, Level 16, State 0, Line 2
Introducing FOREIGN KEY constraint ‘FK_Empregado_Empregado’ on table ‘Empregados’ may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.

No SQL Server, o recurso de exclusão em cascata possui algumas restrições e uma delas é não poder ser utilizado para constraints que envolvam o auto-relacionamento. Não cheguei a testar esse comportamento em outros SGBDs como o ORACLE, DB2 e afins, mas entendo que essa restrição do SQL Server tenha certo fundamento. A exclusão em cascata com o auto-relacionamento pode levar a situações complexas de gerenciar bem como efeitos catastróficos. Excluir o registro do Sálvio por exemplo, incorreria em praticamente limpar a tabela de empregados se a exclusão em cascata funcionasse com o auto-relacionamento. Outra situação envolve referências circulares. Digamos que não estejamos tratando de uma relação de subordinação, mas de indicações ou até um amigo oculto. Em tais situações é bem fácil de acontecer uma referência circular do tipo K -> X -> Y -> Z -> K. A exclusão em cascata em uma situação dessas iria provocar um loop infinito.

Admitindo-se que não haja um ciclo, como fazer então para excluir em cascata registro de uma tabela que possui auto-relacionamento se a FK não dá essa possibilidade ? A solução que me veio a cabeça para resolver a situação é combinar a chave estrangeira com uma trigger de Instead Of. Só que há mais um detalhe, se a FK irá bloquear as exclusões que a violem, é preciso excluir os registros na ordem correta. Para fazer isso utilizei-me de uma CTE recursiva.

— Captura o ID do empregado Hélio
DECLARE @EmpregadoID INT
SET @EmpregadoID = (
    SELECT EmpregadoID FROM Empregados
    WHERE EmpregadoNome = ‘Hélio’)

— Lista todos os subordinados ao Hélio com o nível
;WITH Emp (EmpregadoID, EmpregadoNome, SuperiorID, Nivel)
As (
    SELECT EmpregadoID, EmpregadoNome, SuperiorID, 1 As Nivel
    FROM Empregados
    WHERE EmpregadoID = @EmpregadoID
    UNION ALL
    SELECT E.EmpregadoID, E.EmpregadoNome, E.SuperiorID, Nivel + 1
    FROM Empregados As E
    INNER JOIN Emp ON E.SuperiorID = Emp.EmpregadoID)

— Recupera os subordinados ao Hélio
SELECT EmpregadoID, EmpregadoNome, SuperiorID, Nivel FROM Emp

EmpregadoID EmpregadoNome SuperiorID Nivel
2 Hélio 1 1
3 Dênis 2 2
4 Júnior 2 2
5 Marcos 3 3
6 Bruno 3 3
7 Paulo 5 4

Agora ficou bem mais fácil. A CTE recursiva retorna o registro e todos os subordinados. Para evitar violação de chave estrangeira, basta excluir do maior nível para o menor nível. Adicionei a lógica dentro da trigger.

— Cria uma trigger Instead Of Delete
CREATE TRIGGER trgIDEmpregados ON Empregados
INSTEAD OF DELETE
As

— Cria uma tabela temporária para armazenar os registros a excluir
DECLARE @Empregados TABLE (EmpregadoID INT, Nivel INT)

— Monta a CTE recursiva
;WITH Emp (EmpregadoID, SuperiorID, Nivel)
As (
    SELECT EmpregadoID, EmpregadoNome, SuperiorID, 1 As Nivel
    FROM Empregados
    WHERE EmpregadoID IN (SELECT EmpregadoID FROM Deleted)
    UNION ALL
    SELECT E.EmpregadoID, E.SuperiorID, Nivel + 1
    FROM Empregados As E
    INNER JOIN Emp ON E.SuperiorID = Emp.EmpregadoID)

— Insere os IDs dos registros a excluir
INSERT INTO @Empregados SELECT EmpregadoID, Nivel FROM Emp
ORDER BY Nivel DESC

— Exclui os registros
DECLARE @MaiorNivel INT
SET @MaiorNivel = (SELECT MAX(Nivel) FROM @Empregados)

WHILE EXISTS (SELECT * FROM @Empregados)
BEGIN
    — Exclui os empregados
    DELETE FROM Empregados WHERE EmpregadoID IN
        (SELECT EmpregadoID FROM @Empregados WHERE Nivel = @MaiorNivel)

    — Exclui os empregados da tabela temporária
    DELETE FROM @Empregados WHERE Nivel = @MaiorNivel

    — Decrementa um nível
    SET @MaiorNivel = @MaiorNivel – 1

END

Agora vamos ver alguns testes:

— Exclui o Hélio
— Só deve sobrar o Sálvio
BEGIN TRAN
    DELETE FROM Empregados WHERE EmpregadoID = 2
    SELECT EmpregadoID, EmpregadoNome, SuperiorID FROM Empregados
ROLLBACK

— Exclui o Dênis
— Só deve sobrar o Sálvio, Hélio e Júnior
BEGIN TRAN
    DELETE FROM Empregados WHERE EmpregadoID = 3
    SELECT EmpregadoID, EmpregadoNome, SuperiorID FROM Empregados
ROLLBACK

— Exclui o Marcos e o Júnior
— Só deve sobrar o Sálvio, Dênis e Bruno
BEGIN TRAN
    DELETE FROM Empregados WHERE EmpregadoID IN (3,5)
    SELECT EmpregadoID, EmpregadoNome, SuperiorID FROM Empregados
ROLLBACK

— Exclui o Sálvio
— Não sobra ninguém
BEGIN TRAN
    DELETE FROM Empregados WHERE EmpregadoID = 1
    SELECT EmpregadoID, EmpregadoNome, SuperiorID FROM Empregados
ROLLBACK

Como podemos ver, a trigger inclusive já é orientada a conjuntos permitindo múltiplas exclusões. Confesso que tirando a thread eu nunca tinha tido tal necessidade. Quem precisar é só adaptar.

[ ]s,

Gustavo

Novos rumos para o Blog (http://gustavomaiaaguiar.spaces.live.com) – Mudança de Endereço

Olá Pessoal,

Esse provavelmente será meu último post no blog (Calma, Calma). Não vou deixar de postar e nem de compartilhar conhecimentos, experiência e artigos como tradicionalmente eu faço. Apenas quero dizer que terei de trocar de endereço por contas das mudanças que o Windows Live vem fazendo. Após estar presente no Spaces há quase dois anos e meio, terei que trocar o endereço.

Por um lado é bem gratificante, pois, imagino que a WordPress tenha mais recursos que o Spaces em relação a blogs, mas por outro lado arrumar todos os links, referências, etc que fizeram com que esse blog fosse encontrado é que é complicado, mas enfim, evoluir é preciso. Eu gostava muito da praticidade da plataforma do Spaces, mas a ausência de estatísticas era uma coisa que me fazia muita falta. Acredito que a nova plataforma seja muito boa, pois, diversos amigos já fizeram a troca.

Espero continuar contando com todos os que visitam o blog, fazem comentários, indicações, etc. A maior fonte de inspiração é justamente saber que existem pessoas que o apreciam esse trabalho. A partir de 2011, lembrem-se de atualizar e mudar o link trocando spaces.live.com por wordpress.com. Ao invés de ser encontrado em http://gustavomaiaaguiar.spaces.live.com serei encontrado em https://gustavomaiaaguiar.wordpress.com

Aproveito aqui para felicitar todos os que lêem essa postagem. Desejo a todos um feliz 2011. Que tenhamos um ano melhor que 2010 com muita alegria, saúde, felicidade, prosperidade e vários artigos de SQL Server.

[ ]s,

Gustavo