segunda-feira, 2 de abril de 2012

Query Dinâmica - SQLServer

Uma boa prática na criação de consultas dinâmicas quando criamos telas de pesquisa onde o usuário cria a combinação de critérios que serão aplicadas no banco de dados é a query dinâmica, geralmente mal vista pelos desenvolvedores, mas uma análise breve nos resultados de performance comparado à outros métodos mostra que essa é uma boa solução para pesquisas que geram critérios também dinâmicos.

Essas pesquisas geralmente dificultam o banco de dados na criação de bons planos de execução dependendo de como a solução é desenvolvida, utilizando-se queries dinâmicas como no exemplo abaixo os planos de execução são criados corretamente graças à chamada com a sp_executesql.

Para funcionar melhor ainda é bastante recomendável a criação dos parâmetros que serão passados para a sp_executesql.

1 - Exemplo:
create procedure sp_modelo_pesquisa_paginacao (
@cpf varchar(14) = null,
@codigo int = null,
@nome varchar(55) = null,
@email varchar(45) = null)
as
begin

declare @sql nvarchar(4000)
declare @error int
declare @rowcount int

/* Seleciona os campos. */
select @sql = N'select codigo, nome, cpf '

/* Seleciona as tabelas. */
select @sql = @sql + N'from cliente '

/* Monta os critrios para a seleo. */
select @sql = @sql + N'where 1=1 '
+ case when @cpf is not null then N'and cpf = @pcpf ' else N'' end
+ case when @codigo is not null then N'and codigo = @pcodigo ' else N'' end
+ case when @nome is not null then N'and nome = @pnome' else N'' end
+ case when @email is not null then N'and email = @pemail ' else N'' end

/* Faz a chamada da query utilizando a sp_executesql. */
exec sp_executesql @sql,
N'@pcpf varchar(14),
@pcodigo int,
@pnome varchar(55),
@pemail varchar(45)'
,
@pcpf = @cpf,
@pcodigo = @codigo,
@pnome = @nome,
@pemail = @email

/* Armazena as variveis globais de erro e linhas afetadas. */
select @error = @@ERROR, @rowcount = @@ROWCOUNT

/* Verifica se houve erro na execução da query dinâmica. */
if @error <> 0
begin
select coderro = -1, msgerro = 'Erro ao executar procedure'
return -1
end

/* Verifica se a consulta retornou algum registro. */
if @rowcount = 0
begin
select coderro = -2, msgerro = 'Consulta não retornou nenhum registro com os critérios informados'
return -2
end

end

Muitas soluções econtradas na web sugerem a utilização desses métodos abaixo, mas evite-os, são péssimos no quesito performance.

  - Não utilize funções ISNULL contra colunas em uma cláusula WHERE.
  - Não utilize comparações do tipo (Coluna = @Variavel OR @Variavel = ‘’).

Se quiser entender melhor, recomendo uma visita à matéria do Gustavo Maia Aguiar, que explica muito bem, com análises de performance, sobre esse assunto:
- http://gustavomaiaaguiar.wordpress.com/2011/05/19/consultas-parametrizadas-isnull-e-sql-dinmica/

Abraços!
Diego Feliciano

Nenhum comentário:

Postar um comentário