Ozimar Henrique
Consultas dinâmicas
Formas diferentes de consultas dinâmicas
Neste post mostrarei algumas formas diferentes de fazer consultas dinâmicas.
Em muitos relatórios temos diversos filtros possÃveis e temos que criar procedures flexÃveis para este cenário.
Vale ressaltar que cada forma tem que ser analisada pro o caso em questão.
Criação do ambiente:
create table #Cliente
(
ID int identity(1,1) primary key,
Nome varchar(50),
Idade int,
Vendas int
)
GO
SET NOCOUNT ON
INSERT INTO #Cliente
VALUES(NEWID(), convert(int,RAND()*100), convert(int,RAND()*100))
GO 100000
SET NOCOUNT OFF
CREATE NONCLUSTERED INDEX IX_CLIENTE_Idade ON #Cliente([Idade]) INCLUDE([Nome])
GO
OPÇÃO 1) EXECUTE - Esta é a mais utilizada
CREATE PROCEDURE usp_ConsultaCliente
@pn_Nome varchar(50)=NULL, @pn_Idade int=NULL, @pn_Vendas int=NULL
AS
BEGIN
DECLARE @SQL varchar(1000)
SET @SQL = 'Select Nome from #Cliente where 1=1'
IF @pn_Idade is not null
SET @SQL = @SQL + ' and Idade=' + convert(varchar(5),@pn_Idade)
IF @pn_Vendas is not null
SET @SQL = @SQL + ' and Vendas=' + convert(varchar(5),@pn_Vendas)
IF @pn_Nome is not null
SET @SQL = @SQL + ' and Nome like ''' + convert(varchar(50),@pn_Nome) + '%'''
EXECUTE(@SQL)
END
GO
OPÇÃO 2)SP_EXECUTESQL
Definição da Microsoft: "Para executar uma cadeia de caracteres, recomendamos que você use o procedimento armazenado sp_executesql em vez da instrução EXECUTE."
O sp_executesql permite definirmos o que é parâmetro dentro da string da consulta e passar estes parâmetros com tipos bem definidos e separadamente, o que facilita ao SQL Server de criar um plano efetivo.
Com isto a probabilidade do SQL Server criar um plano de execução mais efetivo e reutiliza-lo depois é maior.
CREATE PROCEDURE usp_ConsultaCliente2
@pn_Nome varchar(50)=NULL, @pn_Idade int=NULL, @pn_Vendas int=NULL
AS
BEGIN
DECLARE @SQL nvarchar(1000)
DECLARE @Param nvarchar(1000)
SET @SQL = 'Select Nome from #Cliente where 1=1'
IF @pn_Idade is not null
SET @SQL = @SQL + ' and Idade=' + convert(varchar(5),@pn_Idade)
IF @pn_Vendas is not null
SET @SQL = @SQL + ' and Vendas=' + convert(varchar(5),@pn_Vendas)
IF @pn_Nome is not null
SET @SQL = @SQL + ' and Nome like ''' + convert(varchar(50),@pn_Nome) + '%'''
Set @Param='@pn_Nome varchar(50), @pn_Idade int, @pn_Vendas int'
EXECUTE sp_executesql @SQL, @Param, @pn_Nome, @pn_Idade, @pn_Vendas
END
GO
OPÇÃO 3) Utilizando OR e NULL
CREATE PROCEDURE usp_ConsultaCliente3
@pn_Nome varchar(50)=NULL, @pn_Idade int=NULL, @pn_Vendas int=NULL
AS
BEGIN
Select Nome from #Cliente
where (Idade=@pn_Idade or @pn_Idade is null)
AND (Vendas=@pn_Vendas or @pn_Vendas is null)
AND (Nome like @pn_Nome + '%' or @pn_Nome is null)
END
GO
Vamos executar as 3 procedures:
exec usp_ConsultaCliente @pn_Idade=55
exec usp_ConsultaCliente2 @pn_Idade=55
exec usp_ConsultaCliente3 @pn_Idade=55
Analisando as estatÃsticas de IO temos o seguinte:
(932 row(s) affected)
Table 'Worktable'. Scan count 1, logical reads 10
(932 row(s) affected)
Table 'Worktable'. Scan count 1, logical reads 10
(932 row(s) affected)
Table 'Worktable'. Scan count 1, logical reads 761
Veja que a opção 3 é a que utilizou mais leitura de páginas.
Vamos ver o plano de execução:
Veja que a opção 3 é a que gera mais custos pro servidor.
Coloque a opção OPTION(RECOMPILE) no final da consulta da opção 3 e veja a diferença que ela gera.
O objetivo deste Post é apenas mostrar as 3 opções, num post futuro irei explicar mais internamente cada um destes comandos.
Related articles
SQL Update using Try Catch
Na prática com exemplo real
Comando SQL Update usando Try Catch
Na prática e no database
SELECT Format Date Time for many countries
It is a good tip to use day by day
Membership $5/mo
|
✓ Full articles Subscribe ✓ Write forums ✓ Access podcast ✓ Full jobs opportunities ✓ Access eBooks ✓ Access magazine ✓ Access videos |
Free
|
✓ Open articles ✓ Read forums ✓ Access podcast ｘ Full jobs opportunities ｘ Access eBooks ｘ Access magazine ｘ Access videos |