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

12 Respostas para “Consultas parametrizadas, ISNULL e SQL dinâmica

  1. Marcelo Godoy

    Parabéns Gustavo, ótima publicação, vai me ajudar muito.

  2. Ótimo artigo, me ajudou muito. Como você citou, quando estamos em um ambiente de produção manipulando uma base de dados “pequena” não faz diferença, mas quando o volume é muito grande você tem que pensar bastante antes de escrever uma query e utilizar corretamente os artificios de desempenho disponíveis.
    Parabêns!

    • Oi Diego,

      O baixo volume muitas vezes oculta nossos erros, mas quando as coisas aumentam e aparecem temos de nos preocupar para que nossos erros também não apareçam.

      [ ]s,

      Gustavo

  3. Rapaz… ontem eu exatamente isso… o desenvolvedor usou um

    coluna in ( CASE @variavel WHEN 0 then COLUNA else @VARIAVEL END )

    Use query dinamica ( que antes eu apredejava, mas depois do internals eu gosto dela ) …
    A query caiu de 3 segundos para alguns milesimos… nem olhei plano, nem STATISTICS IO… jah era de se esperar…

    []’s
    Rodrigo
    Microsoft MTA

    • Show de bola Rodrigo.
      Já apedrejei muito a SQL dinâmica por conta da segurança, mas há situações em que ela é inevitável e a partir do 2005 a questão segurança foi significativamente melhorada.

      [ ]s,

      Gustavo

  4. Gustavo,

    Muito bom o artigo, parabéns…

  5. Gustavo, estive usando SQL dinâmico e é perceptível a melhoria e poder que temos ao utilizar esse recurso. Fiquei com uma dúvida sobre melhores práticas e queria sua opinião para saber qual a melhor forma de resolver. Veja os exemplos SQLs abaixo:

    use master
    go

    /*EXEMPLO 1: nesse caso estou colocando todos os parametros dentro da string SQL e vou substitui-los na chamada da sp_executesql…*/
    declare @sql nvarchar(100),
    @ret int,
    @id int = 1

    set @sql = ‘select @p_contagemOUT = count(*)
    from sys.tables
    where schema_id = @p_id’

    exec sp_executesql
    @sql,
    N’@p_id int,@p_contagemOUT int out’,
    @p_id = @id,@p_contagemOUT = @ret out

    print @ret
    go

    /*EXEMPLO 2: nesse caso apenas o retorno declaro na sp_executesql, o outro parâmetro preparo antes na montagem da string SQL…*/
    declare @sql nvarchar(100),
    @ret int,
    @id int = 1

    set @sql = ‘select @p_contagemOUT = count(*)
    from sys.tables
    where schema_id = ‘+cast(@id as varchar)

    exec sp_executesql
    @sql,
    N’@p_contagemOUT int out’,
    @p_contagemOUT = @ret out

    print @ret

    Claro que no meu exemplo estamos com um SQL muito simples, mas quando temos 20 parâmetro de entrada por exemplo, qual melhor prática:
    1 – Declará-los na sp_executesql?
    2 – Montar o SQL com eles já resolvidos?

    Aguardo sua resposta…

    • Oi Alex,

      Parametrizar a sp_executesql costuma ser mais performático, mas inegavelmente montar o SQL com eles já resolvidos é mais fácil. Não sei dizer se a diferença é tão perceptível assim. Aí caberia um teste.

      [ ]s,

  6. Muito obrigado pela resposta Gustavo. No meu caso, acabei utilizando a sp_exexutesql…

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s