Blog – Fabiano Neves Amorim

Aqui estou em São Paulo, me preparando para passar as noites do fim de semana acompanhando uma implantação…. Tempo para um post…

Pessoal, desde o SQL Server 2005, temos um parâmetro que pode ser habilitado no banco de dados chamado DATE_CORRELATION_OPTIMIZATION. Como o padrão é OFF, eu particularmente nunca vi alguem alterar este parâmetro para ON. Eu creio que isso deve se ao fato de termos pouquíssimo material falando sobre esta feature.

É importante que o desenvolvedor ou DBA conheçam o banco de dados, para poder fazer proveito desta otimização, mas você deve estar se pergutando, Mas pra que serve este parâmetro?

Vamos lá:

Quando habilitado o DATE_CORRELATION_OPTIMIZATION coleta informações sobre colunas do tipo datetime de duas tabelas que estão ligadas por uma foreign key. Estas informações são comumentes conhecidas como estatisticas (apesar de não agirem exatamente como uma estatistica), pois elas ajudam o Query Optimizer a identificar uma relação entre as colunas que contém uma data, e assim decidir sobre a criação do plano de execução.

Um bom exemplo de colunas correlatas seria o seguinte: Uma tabela de Pedido que contem uma coluna DataPedido, e uma tabela Itens que contem uma coluna DataEntregaItem.

Estes dias eu fiz uma compra no Submarino e comprei varios produtos, desde livros a bichos de pelucia (deixa eu explicar melhor esse item pra não dar chance de aparecerem comentários sobre isso…rs. Foi pra minha patroa gente… é que ela gosta de pinguin e comprei um de pelucia…). Conclusão, apesar de meu pedido ser apenas um, eu recebi os itens em dias diferentes, isso porque com certeza cada um veio de um lugar. Mas voltanto as tabelas, neste caso teriamos a tabela de pedido com a data de pedido, e a tabela de itens que é relacionada ao pedido e contem a data de entrega de cada item. Podemos dizer que a data de entrega sempre estara bem próxima a data de pedido. Isso representa claramente uma relação entre a data de pedido e a data de entrega.

Após entendido isto, vamos voltar a otimização.

Se você tiver uma consulta onde faz um join entre a tabela de Pedido e Itens fazendo filtro por uma das datas, o SQL Server pode alterar sua consulta a fim de otimiza-la sem você saber disso. Como?… vamos começar com os exemplos.

Primeiro vamos criar uma estrutura de tabelas parecidas com o cenário mencionado acima.

IF OBJECT_ID(‘Pedidos’) IS NOT NULL
BEGIN
  DROP TABLE Itens
  DROP TABLE Pedidos
END
GO
CREATE TABLE Pedidos(ID_Pedido   Integer Identity(1,1),
                     Data_Pedido DateTime NOT NULL, — AS COLUNAS DE DATA NÃO PODEM ACEITAR NULL
                     Valor       Numeric(18,2),
                     CONSTRAINT xpk_Pedidos PRIMARY KEY (ID_Pedido))
GO
CREATE TABLE Itens(ID_Pedido    Integer,
                   ID_Produto   Integer,
                   Data_Entrega DateTime NOT NULL, — AS COLUNAS DE DATA NÃO PODEM ACEITAR NULL
                   Quantidade   Integer,
                   CONSTRAINT xpk_Itens PRIMARY KEY NONCLUSTERED(ID_Pedido, ID_Produto))
GO
— Pelo menos uma das colunas de data, tem que pertencerem a um indice cluster
CREATE CLUSTERED INDEX ix_Data_Entrega ON Itens(Data_Entrega)
GO
— É Obrigatório existir uma foreign key entre as tabelas que contém as datas correlatas
ALTER TABLE Itens ADD CONSTRAINT fk_Itens_Pedidos FOREIGN KEY(ID_Pedido) REFERENCES Pedidos(ID_Pedido)
GO

DECLARE @i Integer
SET @i = 0

WHILE @i < 10000
BEGIN
  INSERT INTO Pedidos(Data_Pedido,
                      Valor)
  VALUES(GetDate() – ABS(CheckSum(NEWID()) / 10000000),
         ABS(CheckSum(NEWID()) / 1000000))
  SET @i = @i + 1
END
GO

INSERT INTO Itens(ID_Pedido, ID_Produto, Data_Entrega, Quantidade)
SELECT ID_Pedido,
       ABS(CheckSum(NEWID()) / 10000000),
       Data_Pedido + ABS(CheckSum(NEWID()) / 100000000),
       ABS(CheckSum(NEWID()) / 10000000)
  FROM Pedidos
GO
INSERT INTO Itens(ID_Pedido, ID_Produto, Data_Entrega, Quantidade)
SELECT ID_Pedido,
       ABS(CheckSum(NEWID()) / 10000),
       Data_Pedido + ABS(CheckSum(NEWID()) / 100000000),
       ABS(CheckSum(NEWID()) / 10000000)
  FROM Pedidos
GO

Após criar as tabelas, teremos uma estrutura parecida com o cenário que mencionei, vamos ver os dados de um pedido.

Agora vamos supor que a seguinte consulta seja construida.

SELECT *
  FROM Pedidos
INNER JOIN Itens
    ON Pedidos.ID_Pedido = Itens.ID_Pedido
WHERE Pedidos.Data_Pedido BETWEEN ‘20090301’ AND ‘20090305’

Traduzindo, eu quero todos os dados de Pedidos e Itens vendidos entre 01/03/2009 até 05/03/2009. Para esta consulta o SQL Server criou o seguinte plano de execução:

SELECT *    FROM Pedidos   INNER JOIN Itens      ON Pedidos.ID_Pedido = Itens.ID_Pedido   WHERE Pedidos.Data_Pedido BETWEEN ‘20090301’ AND ‘20090305’
  |–Hash Match(Inner Join, HASH:([Performance].[dbo].[Pedidos].[ID_Pedido])=([Performance].[dbo].[Itens].[ID_Pedido]))
       |–Clustered Index Scan(OBJECT:([Performance].[dbo].[Pedidos].[xpk_Pedidos]), WHERE:([Performance].[dbo].[Pedidos].[Data_Pedido]>=’2009-03-01′ AND [Performance].[dbo].[Pedidos].[Data_Pedido]<=’2009-03-05′))
       |–Clustered Index Scan(OBJECT:([Performance].[dbo].[Itens].[ix_Data_Entrega]))

No plano em modo texto, podemos facilmente perceber que o filtro especificado no where, foi aplicado durante a leitura do índice xpk_Pedidos e o campo que recebeu o filtro foi justamente o Data_Pedido espeficidado no where.

A primeira coisa que poderiamos fazer para otimizar esta consulta, seria criar um indice na coluna Data_Pedido. Ok, você pode estar pensando, a isso eu sei, isso todo mundo faria logo de cara, você esta certo, mas vamos fazer isso e continuar na otimização.

CREATE INDEX ix_teste ON Pedidos(Data_Pedido) INCLUDE(Valor)

Após criar o índice temos o seguinte plano:

SELECT *    FROM Pedidos   INNER JOIN Itens ON Pedidos.ID_Pedido = Itens.ID_Pedido   WHERE Pedidos.Data_Pedido BETWEEN ‘20090301’ AND ‘20090305’
  |–Hash Match(Inner Join, HASH:([Performance].[dbo].[Pedidos].[ID_Pedido])=([Performance].[dbo].[Itens].[ID_Pedido]))
       |–Index Seek(OBJECT:([dbo].[Pedidos].[ix_teste]), SEEK:([dbo].[Pedidos].[Data_Pedido] >= ‘2009-03-01’ AND [dbo].[Pedidos].[Data_Pedido] <= ‘2009-03-05’) ORDERED FORWARD)
       |–Clustered Index Scan(OBJECT:([Performance].[dbo].[Itens].[ix_Data_Entrega]))

Agora a coisa melhorou um pouco, ao invés de fazer um Clustered Index Scan para ler os dados da tabela Pedidos, o SQL fez um Index Seek baseado no índice que acabamos de criar. Ok o que mais podemos fazer? Vamos começar a ver o Correlation_Optimization.

Vemos que existe um gargalo para ler os dados da tabela Itens, segundo o plano de execução que temos, esta leitura representa 49% de custo da consulta, e ele esta fazendo um Scan, será que da pra fazer um Seek?

Conhecendo o banco de dados e a estrutura das tabelas e negócio, poderiamos alterar a consulta para incluir um filtro na tabela Itens. Mas para isso teremos que ter certeza de que nosso filtro não irá causar uma mudança no resultado dos dados. Uma alternativa seria a seguinte:

1. Baseado nos pedidos que estão entre 2009-03-01 e 2009-03-05, verificar qual é a menor data de entrega e a maior data de entrega dos itens.
2. Com estas informações incluir um filtro por Itens.Data_Entrega.

Com isso teriamos a seguinte consulta:

SELECT *
  FROM Pedidos
INNER JOIN Itens
    ON Pedidos.ID_Pedido = Itens.ID_Pedido
WHERE Pedidos.Data_Pedido BETWEEN ‘20090301’ AND ‘20090305’
   AND Itens.Data_Entrega BETWEEN ‘20090301’ AND ‘20090325 23:59:59.000’

Desta forma eu conseguiria forçar um filtro na tabela Itens, veja o plano:

Mas pergunta? Como fazer para descobrir os valores do item 1, sozinho? Porque normalmente os valores do between são variáveis e não fixos.

Que tal deixar o SQL se virar por você? , Basta habilitar o DATE_CORRELATION_OPTIMIZATION que ele identifica isso pra você, e aplica o filtro no Data_Entrega sozinho, óia que esperto eim! Vamos ver se é verdade….

ALTER DATABASE <NomeDoSeuBanco> SET DATE_CORRELATION_OPTIMIZATION ON;

Vamos rodar a consulta denovo…

SELECT *    FROM Pedidos   INNER JOIN Itens      ON Pedidos.ID_Pedido = Itens.ID_Pedido   WHERE Pedidos.Data_Pedido BETWEEN ‘20090301’ AND ‘20090305’
  |–Hash Match(Inner Join, HASH:([dbo].[Pedidos].[ID_Pedido])=([dbo].[Itens].[ID_Pedido]))
       |–Index Seek(OBJECT:([dbo].[Pedidos].[ix_teste]), SEEK:([dbo].[Pedidos].[Data_Pedido] >= ‘2009-03-01’ AND [dbo].[Pedidos].[Data_Pedido] <= ‘2009-03-05’) ORDERED FORWARD)
       |–Clustered Index Seek(OBJECT:([dbo].[Itens].[ix_Data_Entrega]), SEEK:([dbo].[Itens].[Data_Entrega] >= ‘2009-02-28’ AND [dbo].[Itens].[Data_Entrega] < ‘2009-04-29’) ORDERED FORWARD)

Repare que desta vez, mesmo sem eu colocar o filtro na coluna Data_Entrega o SQL colocou este filtro pra mim.

Conclusão

O DATE_CORRELATION_OPTIMIZATION é uma feature muito boa mas que merece uma analise mais detalhada de onde pode ser utilizada, ela também exige que algumas regras sejam atendidas para que funcione. Por exemplo:

• Obrigatóriamente deve existir uma foreign key entre as tabelas, e ela deve estar ligada por apenas uma coluna.
• Ambas as tabelas devem conter uma coluna do tipo DateTime e elas não podem aceitar NULL.
• Pelo menos uma das colunas DateTime deve pertencer a um Indice Cluster, caso o indice seja composto, a coluna deve ser a chave para ordenação, ou seja, deve ser a primeira especificada.

Bom galera, fica ai a dica…

Abraço