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
– Verifica quem tem o sobrenome Gonçalves
SELECT * FROM Pessoas WHERE SobreNome = ‘Gonçalves’
– Verifica quem tem o sobrenome Mendes e nasceu em 1979
SELECT * FROM Pessoas WHERE SobreNome = ‘Mendes’ AND AnoNascimento = 1979
– Verifica quem tem 1 filho e nasceu em Brasília
SELECT * FROM Pessoas WHERE Filhos = 1 AND Cidade = ‘Brasília’
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
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
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:
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
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
