Blog – Fabiano Neves Amorim

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.