🔥 Articles, eBooks, Jobs, Columnist, Forum, Podcasts, Courses 🎓
🎓 ecode10.com

NordPass ecode10 blue
Consultas dinâmicas | ecode10.com


Consultas dinâmicas

Formas diferentes de consultas dinâmicas
DATABASE
Wednesday, May 2, 2012
Ozimar Henrique   Ozimar Henrique  SHARE

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.



NordPass ecode10 blue


Related articles




NordPass ecode10 blue
Top