De: Medina, Edvaldo
Enviada em: terça-feira, 27 de outubro de 2009 09:33
Para: Amorim, Fabiano
Assunto: ENC: Cuidado com o IF EXISTS…
Prioridade: Alta

Fabiano, uma pergunta.

Se tivermos um IF com vários EXISTS com AND o SQL vai realizar todas as consultas ou no primeiro que for falso o sistema já finaliza a condição, exemplo:

IF (@B = ‘S’) AND

     (EXISTS(SELECT)) AND

     (EXISTS(SELECT)) AND

     (EXISTS(SELECT)) AND

     (@A = ‘S’)

BEGIN

END

-----------------------------------------------

De: Amorim, Fabiano
Enviada em: terça-feira, 27 de outubro de 2009 10:11
Para: Medina, Edvaldo
Assunto: RES: Cuidado com o IF EXISTS…

Cara, infelizmente ele vai avaliar todas as consultas, para depois pegar o resultado delas e fazer o IF. Por ex:

DECLARE @A VarChar(1)

SET @A = 'X'

IF (@A = 'X')

AND EXISTS (SELECT 1 FROM CONVE002 WHERE ID_Cota = 1)

AND EXISTS (SELECT 1 FROM CONVE002 WHERE ID_Cota = 2)

AND EXISTS (SELECT 1 FROM CONVE002 WHERE ID_Cota = 3)

AND EXISTS (SELECT 1 FROM CONVE002 WHERE ID_Cota = 4)

AND EXISTS (SELECT 1 FROM CONVE002 WHERE ID_Cota = -1)

BEGIN

SELECT 'Existe'

END

Ele vai executar todos os selects na CONVE002 guardar o resultado em memória e usar um operador de compute scalar para validar o IF.

Veja no plano de execução, que o operador de compute scalar é o último a ser executado... Não sei não mais acho que o ideal seria ter uma validação para cada consulta...

Pessoal parei para pensar um pouco e aquele loop, virou isso,

CREATE TABLE CONCRAUX (ID Int,
                       CONSTRAINT XPKCONCRAUX PRIMARY KEY(ID))
GO
DECLARE @I Int
SET @I = 1
WHILE @I < 1000
BEGIN
  INSERT INTO CONCRAUX(ID) VALUES(@I);
  SET @I = @I + 1
END

GO

SELECT @ID_Grupo,
       @CD_Grupo,
       CONCRAUX.ID AS CD_Cota,
       ISNULL(CONVE046.SN_Utilizada,'N') AS ST_Reserva
  FROM CONCRAUX
  LEFT OUTER JOIN CONVE002
    ON CONCRAUX.ID = CONVE002.CD_Cota
   AND CONVE002.ID_Grupo = @ID_Grupo
   AND CONVE002.Versao   = 0
  LEFT OUTER JOIN CONVE046
    ON CONVE046.ID_Grupo     = @ID_Grupo
   AND CONVE046.CD_Cota      = CONCRAUX.ID
   AND CONVE046.DT_Validade >= @DT_Base
   AND CONVE046.SN_Utilizada = 'N'
WHERE CONCRAUX.ID <= @NO_Maximo_Cota
   AND CONVE002.ID_Cota IS NULL
   AND (@ST_Reserva = 'T' OR ISNULL(CONVE046.SN_Utilizada,'N') = @ST_Reserva)

Ou seja, criei uma tabela sequencial, e fiz o join com ela.

Aqui estou em Buenos Aires trabalhando na otimização de algumas rotinas…. e fica uma dica rápida,

Pessoal quando criarem código SQL, tentem pensar como o banco de dados, por exemplo, cuidado com instruções do tipo:

  WHILE @CD_Cota <= @NO_Maximo_Cota
  BEGIN
    IF NOT EXISTS (SELECT ID_Cota
                     FROM CONVE002 (NOLOCK)
                    WHERE CD_Cota  = @CD_Cota
                      AND ID_Grupo = @ID_Grupo
                      AND Versao = 0)
    AND ((@ST_Reserva = 'T') OR (dbo.fn_VeSNCotaReservada(@ID_Grupo, @CD_Cota, @DT_Base) = @ST_Reserva))
    BEGIN
      SET @ST_Reserva_Cota = dbo.fn_VeSNCotaReservada(@ID_Grupo, @CD_Cota, @DT_Base)

      INSERT INTO @tb_Reserva (ID_Grupo,
                               CD_Grupo,
                               CD_Cota,
                               ST_Reserva)
      VALUES (@ID_Grupo,
              @CD_Grupo,
              @CD_Cota,
              @ST_Reserva_Cota)
    END

    SET @CD_Cota = @CD_Cota + 1;
  END

Ou seja, para validar este IF, o SQL vai terá que ler a tabela CONVE002, depois vai ler o resultado da fn_VeSN… para resolver o EXISTS e depois vai ler denovo a fn_VeSN….

Isso funciona bem quando estamos falando de C# ou Pascal, mas como estamos falando de banco, o Ideal seriamos utilizar várias instruções de IF. Por ex:

WHILE @CD_Cota <= @NO_Maximo_Cota
BEGIN
  IF NOT EXISTS (SELECT ID_Cota
                   FROM CONVE002 (NOLOCK)
                  WHERE CD_Cota  = @CD_Cota
                    AND ID_Grupo = @ID_Grupo
                    AND Versao = 0)
  BEGIN
    IF (@ST_Reserva = 'T')
    BEGIN
      INSERT INTO @tb_Reserva (ID_Grupo,
                               CD_Grupo,
                               CD_Cota,
                               ST_Reserva)
      VALUES (@ID_Grupo,
              @CD_Grupo,
              @CD_Cota,
              @ST_Reserva_Cota)
    END
    ELSE
    BEGIN
      SET @ST_Reserva_Cota = dbo.fn_VeSNCotaReservada(@ID_Grupo, @CD_Cota, @DT_Base);
      IF (@ST_Reserva_Cota = @ST_Reserva)
      BEGIN
        INSERT INTO @tb_Reserva (ID_Grupo,
                                 CD_Grupo,
                                 CD_Cota,
                                 ST_Reserva)
        VALUES (@ID_Grupo,
                @CD_Grupo,
                @CD_Cota,
                @ST_Reserva_Cota)
      END
    END
  END   

  SET @CD_Cota = @CD_Cota + 1;
END

Ou seja, removemos o disperdicio …

Pessoal,

Mais 2 de meus artigos foram publicados no Simple Talk.

Novamente convido a todos para ler e se possível votar ( positivamente é claro :-) ).

Seguem os links:

http://www.simple-talk.com/sql/t-sql-programming/data-correlation-optimization-internals/

http://www.simple-talk.com/sql/t-sql-programming/query-optimizer-and-cartesian-products/

Abraço.

Pessoal no sábado dia 07/11 teremos um mega evento com 12 horas de SQL Server, vou ministrar a segunda palestra do dia.

Conto com a presença de vocês,

Abraço.

Community Webcast: SQL Server Day - Parte I 

09:30 - 10:00 - Abertura do Evento
10:00 - 11:00 - Resource Governor e Policy Management - Vitor Fava e Alexandre Lopes
11:00 - 12:00 - Entenda porque o Query Optimizer é mais esperto que você - Fabiano Amorim
12:00 - 13:00 - SQL Azure Database - Diego Nogare
13:00 - 14:00 - Novos Recursos de Desenvolvimento do SQL Server 2008 - Higor Fernandes

Community Webcast: SQL Server Day - Parte II

14:00 - 15:00 - Compressão de Dados e Backup no Microsoft SQL Server 2008 - Pedro A. G. Junior
15:00 - 16:00 - Entendendo TDE (Transparent Data Encryption) - Felipe Ferreira
16:00 - 17:00 - Entendendo as Common Table Expressions (CTE) - Thiago Zavaschi
17:00 - 18:00 - Powershell Coletando e Analisando os Dados -Laerte Junior e Thiago Zavaschi
18:00 - 19:00 - Solução Avançada de Problemas com Extended Events - Vladimir Magalhães        

Community Webcast: SQL Server Day - Parte III

19:00 - 20:00 - Disaster Recovery - Backup, Restore e Tópicos Avançados - Gustavo Maia Aguiar
20:00 - 21:00 - CLR - Roberto Fonseca
21:00  - 22:00 - Analise de Desempenho utilizando as Estatísticas de Espera - Alex Rosa

Introdução

No artigo passado vimos como o Date_Correlation_Optimization funciona, e vimos que se bem utilizado, ele pode proporcionar um ganho considerável de performance em nossas consultas. Desta vez vamos analisar esta feature mais profundamente, para entender melhor a lógica utilizada para conseguir descobrir a correlação entre os campos de data.

A primeira pergunta que devemos fazer é: Como e o que o SQL fez para descobrir os valores a serem utilizados no filtro da coluna Data_Entrega?

Bom vamos começar do começo :-). E separando em partes separadas :-).

Analisando o código gerado pelo Query Optimizer

Lembra que eu disse que o SQL pegava informações sobre as colunas correlatas e guardava como se fossem estatísticas? Pois é, o SQL cria uma view indexada com informações sobre as colunas.

Vamos criar novamente a estrutura apresentada como exemplo, mas desta vez vamos executar o script em partes para ver os comandos gerado pelo SQL Server. Vá para o SSMS, execute apenas a parte de criação da estrutura de tabelas, índice.

IF OBJECT_ID('Pedidos') IS NOT NULL

BEGIN

DROP TABLE Items

DROP TABLE Pedidos

END

GO

CREATE TABLE Pedidos(ID_Pedido Integer Identity(1,1),

Data_Pedido DateTime NOT NULL,--The columns cannot accept null values

Valor Numeric(18,2),

CONSTRAINT xpk_Pedidos PRIMARY KEY (ID_Pedido))

GO

CREATE TABLE Items(ID_Pedido Integer,

ID_Produto Integer,

Data_Entrega DateTime NOT NULL,--The columns cannot accept null values

Quantidade Integer,

CONSTRAINT xpk_Items PRIMARY KEY NONCLUSTERED(ID_Pedido, ID_Produto))

GO

-- At least one of the DATETIME columns, must belong to a cluster index

CREATE CLUSTERED INDEX ix_Data_Entrega ON Items(Data_Entrega)

GO

Após criar as tabelas e o índice clustered sobre a tabela Items, vamos configurar um Trace para capturar algumas informações sobre os comandos que o SQL Server gera internamente para criação e controle da view, e contem as informações sobre as colunas correlatas. Crie um novo trace e selecione os eventos SP: StmtCompleted e SP: StmtStarted, são eles que irão nos mostrar os comandos gerados pelo SQL.

Após iniciar o trace, execute o comando abaixo:

-- There must to be a foreign key relationship between the tables that contain correlation date

ALTER TABLE Items ADD CONSTRAINT fk_Items_Pedidos FOREIGN KEY(ID_Pedido) REFERENCES Pedidos(ID_Pedido)

GO

No Profiler serão capturadas as seguintes linhas:

Segue o código gerado pelo SQL Server.

CREATE UNIQUE CLUSTERED INDEX [i__MPStats_Sys_fk_Items_Pedidos_8e035f2] ON [dbo].[_MPStats_Sys_08E035F2_{39D85729-E411-4A66-BC1E-1184EF97837A}_fk_Items_Pedidos](ParentPID,ChildPID)

insert [dbo].[_MPStats_Sys_08E035F2_{39D85729-E411-4A66-BC1E-1184EF97837A}_fk_Items_Pedidos] select * from [dbo].[_MPStats_Sys_08E035F2_{39D85729-E411-4A66-BC1E-1184EF97837A}_fk_Items_Pedidos]

SELECT @MaxParPID = MAX(ParentPID),

@MinParPID = MIN(ParentPID),

@MaxChdPID = MAX(ChildPID),

@MinChdPID = MIN(ChildPID),

@countPID = COUNT(*)

FROM [dbo].[_MPStats_Sys_08E035F2_{39D85729-E411-4A66-BC1E-1184EF97837A}_fk_Items_Pedidos] WITH(NOEXPAND)

WHERE C > 0

Como podemos ver, o SQL gerou 3 comandos, primeiro um create index em uma view chamada _MPStats..., um insert nesta view e depois um select nesta view. Primeiro vamos entender o seguinte, de onde surgiu esta view?

Entendendo a View criada pelo SQL Server

No profiler não pegamos o código de criação da view mas ela foi criada internamente pelo SQL Server, podemos comprovar isso efetuando um select na sys.views.

Repare que existe uma coluna na sys.views que informa se a view é utilizada pelo date correlation. Neste caso vemos o valor preenchido com 1.

O nome da view é um pouco estranho a primeira vista, mas ele tem uma lógica que é a seguinte:

_MPStats_Sys_<constraint_object_id>_<GUID>_<FK_constraint_name>

Seguindo esta regra temos o seguinte:

_MPStats_Sys_15460CD7_{628BDBBC-4E23-4C9E-A8EA-CE08C7C4F3EA}_fk_Items_Pedidos

Onde:

· Roxo - Valor Fixo.

· Vermelho - Hexadecimal do Object_ID da Foreign Key. Apenas para conhecimento, se você quiser confirmar se o valor esta correto (como eu), você pode transformar o valor de Object_ID em hexadecimal e verificar se ele bate com o nome utilizado pelo SQL Server. Por ex:

· Verde – GUID gerado internamente pelo SQL Server.

· Azul – Nome da foreign key que liga as duas tabelas.

Se você tentar efetuar um select nesta view para ver o que ela retorna, receberá a seguinte mensagem.

Para efetuar o select e verificar os dados que são armazenadas na view vamos fazer o seguinte, criar uma nova view com base no código fonte desta view do sistema.

Pergunta, como saber para ver o código fonte dela? Fácil, só rodar um sp_helptext na view.

Não se preocupe em entender o que a view está fazendo agora, vamos ver daqui a pouco.

Com o código, vamos criar outra view, chamada vw_test.

CREATE VIEW [dbo].vw_test

WITH SCHEMABINDING

AS

SELECT DATEDIFF(day, convert(datetime2, '1900-01-01', 121), LEFT_T.[Data_Pedido]) / 30 as ParentPID,

DATEDIFF(day, convert(datetime2, '1900-01-01', 121), RIGHT_T.[Data_Entrega]) / 30 as ChildPID,

COUNT_BIG(*) AS C

FROM [dbo].[Pedidos] AS LEFT_T

JOIN [dbo].[Items] AS RIGHT_T

ON LEFT_T.[ID_Pedido] = RIGHT_T.[ID_Pedido]

GROUP BY DATEDIFF(day, convert(datetime2, '1900-01-01', 121),LEFT_T.[Data_Pedido]) / 30,

DATEDIFF(day, convert(datetime2, '1900-01-01', 121), RIGHT_T.[Data_Entrega]) / 30

Agora já conseguimos efetuar o select na view, porém as tabelas Pedidos e Items estão vazias, vamos inserir alguns dados nelas.

DECLARE @i Integer

SET @i = 0

WHILE @i < 1000

BEGIN

INSERT INTO Pedidos(Data_Pedido,

Valor)

VALUES(CONVERT(VarChar(10),GetDate() - ABS(CheckSum(NEWID()) / 10000000),112),

ABS(CheckSum(NEWID()) / 1000000))

SET @i = @i + 1

END

GO

INSERT INTO Items(ID_Pedido, ID_Produto, Data_Entrega, Quantidade)

SELECT ID_Pedido,

ABS(CheckSum(NEWID()) / 10000000),

CONVERT(VarChar(10),Data_Pedido + ABS(CheckSum(NEWID()) / 100000000),112),

ABS(CheckSum(NEWID()) / 10000000)

FROM Pedidos

GO

INSERT INTO Items(ID_Pedido, ID_Produto, Data_Entrega, Quantidade)

SELECT ID_Pedido,

ABS(CheckSum(NEWID()) / 10000),

CONVERT(VarChar(10),Data_Pedido + ABS(CheckSum(NEWID()) / 100000000),112),

ABS(CheckSum(NEWID()) / 10000000)

FROM Pedidos

GO

Vamos consultar a view apenas para ver o que o SQL guarda nela. Lembrando apenas que criamos a vw_test para poder ver o que está na view criada internamente pelo SQL Server, como o código de ambas é igual temos exatamente o que esta salvo na view original.

É importante destacar que esta é uma view indexada, ou seja, ela ocupa espaço em disco e tem de ser atualizada durante os inserts, updates e deletes nas tabelas Pedidos e Items. Portanto se você identificar que suas operações de manutenção na tabela estão lentas, você pode analisar se é a view que está causando este problema.

Podemos ver o espaço ocupado pela view utilizando a proc sp_spaceused.

Entendendo a Mágica

Bom, agora que entendemos quando a view é criada e como ela é atualizada, vamos entender o principal. Qual a lógica utilizada para identificar os valores das colunas correlatas.

Na view temos duas colunas uma chamada ParentPID e outra chamada ChildPID, a seguinte regra esta sendo utilizada para retornar seus valores.

ParentPID = DATEDIFF(day, convert(datetime, '1900-01-01', 121), LEFT_T.[Data_Pedido]) / 30

ChildPID = DATEDIFF(day, convert(datetime, '1900-01-01', 121), RIGHT_T.[Data_Entrega]) / 30

Traduzindo:

ParentPID e ChildPID = Me retorne a quantidade de dias que faltam para que 1900-01-01 chegue na data gravada em Data_Pedido, e divida este resultado por 30. Por ex: De 1900-01-01 até 2009-01-01 se passaram 39812 dias. Dividindo estes dias por 30 temos 1327.

A divisão por 30 é efetuada para que os valores agrupados sejam poucos, e neste caso temos sempre uma referência de alguns poucos meses entre uma data e outra.

Quando construímos uma consulta utilizando a coluna Data_Pedido, o SQL pega o valor que usamos no where e aplica a mesma fórmula para poder chegar no valor de dias dividido por 30. O Query Optimizer vai na view e procurando pelo valor de ChildPID(Data_Entrega) passando como filtro o valor de ParentPID (que é a coluna Data_Pedido) que ele calculou. Desta forma o SQL irá retornar o valor coluna ChildPID que esta relacionada com a ParentPID(lembre-se de que o SQL sabe que existe o relacionamento entre ID_Pedido, veja na view que ele faz o join.). Com o valor do ChildPID em mãos o SQL aplica a regra reversa para poder achar as datas que ele vai utilizar como predicate na coluna Data_Entrega. Vamos ver isso na prática e seguindo passo a passo para ficar mais claro.

Suponha que eu crie a seguinte consulta.

SELECT *

FROM Pedidos

INNER JOIN Items

ON Pedidos.ID_Pedido = Items.ID_Pedido

WHERE Pedidos.Data_Pedido = '20090801'

O filtro foi aplicado na coluna Data_Pedido, o SQL precisa identificar quais os valores ele deve informar como predicate na tabela Items.Data_Entrega. Vamos no passo a passo:

1. O Query Optimizer vai na view identificar qual é o maior e menos valor de ChildPID para poder fazer o calculo reverso. Você pode capturar esta consulta na view no profiler.

a. A seguinte consulta foi executada, eu alterei um pouco para ficar mais claro, mas é isso que você irá capturar no profiler.

2. Com os valores de 1334 e 1335 em mãos o SQL aplica a regra inversa para poder obter os valores do filtro por Data_Entrega.

Traduzindo, a partir de 1900-01-01 some (1334 * 30), neste caso teremos o valor de 2009-07-28 como valor mínimo.

Para o valor máximo, o SQL soma mais 1 como margem de segurança para não pegar o valor incorreto.

3. Aplica os valores como filtro na tabela Items. Veja que o plano de execução passou exatamente os valores que calculamos acima.

Conclusão

É pessoal, vimos internamente como a feature funciona, e pode ser que você tenha aprendido alguns truques que ainda não conhecia, como capturar consultas feitas pelo SQL utilizando o Profiler, converter valores de hexa para decimal, sp_helptext, sp_spaceused...

Quero deixar claro que por enquanto não faço parte do desenvolvimento do produto, portanto posso estar equivocado em alguma afirmação que eu tenha feito. Não confirmei com o time de desenvolvimento do SQL se a lógica que apresentei acima está 100% valida, mas se você seguiu meu raciocínio viu que tudo faz pleno sentido.

Pra terminar deixo uma pergunta. Da pra usar esta mesma lógica em outros cenários concorda?... Vale a pena perder um tempo pensando sobre isso.

Espero que tenham gostado todo feedback é bem vindo, estejam a vontade para comentar ou se preferirem me escrever para dar opinião sobre meus artigos, você pode me enviar um e-mail no fabiano_amorim(at)bol.com.br.

That’s all folks.

Pessoal, estou muito feliz em dizer que tive meu primeiro artigo em inglês publicado em um dos maiores sites de tecnologia e SQL Server.

http://www.simple-talk.com/content/article.aspx?article=826

Convido a todos para visitar o artigo, votar nele, e se possível deixar algum comentário (positivo né galera, rsrs, se tiver algum erro me manda no e-mail J).

Agradeço a todos pelo incentivo e apoio.

Abraço.