Inicial > SQL Server > Date Correlation Optimization Internals

Date Correlation Optimization Internals

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.

clip_image001

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:

clip_image002

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.

clip_image003

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:

clip_image004

clip_image006 clip_image008

· 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.

clip_image009

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.

clip_image010

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.

clip_image011

É 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.

clip_image012

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.

clip_image013

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.

clip_image014

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.

clip_image015

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.

Categorias:SQL Server
  1. Laerte
    14 de outubro de 2009 às 16:23

    Grande Fabiano, excelente !!!!

  1. No trackbacks yet.

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s

%d blogueiros gostam disto: