Comparando Semi Joins
Publicado; 18/05/2013 Arquivado em: SQL Optimizer, SQL T-SQL Mitos 1 comentárioExistem alguns mitos envolvendo o que conceitualmente é conhecido como semi join, e, infelizmente, tais mitos acabam por prejudicar a escrita de consultas, causando problemas de performance. Mas, o que são semi joins? São joins que retornam linhas de uma tabela A baseado na existência de linhas correlacionadas em uma tabela B. Se a consulta retorna apenas atributos (campos) da tabela da esquerda, então o join é chamado de Left Semi Join; se retorna apenas atributos da tabela da direita, então é chamado de Right Semi Join. Um semi join pode ser produzido usando INNER JOINS, EXISTS, IN e também com o INTERSECT.
Antes de entrarmos em mais detalhes, listemos os mitos comuns envolvendo semi joins:
· JOIN versus EXISTS
· IN versus EXISTS
Será que realmente existe ganho de performance ao adotar, por exemplo, o EXISTS ao invés do IN? Iremos clarificar essa e outras questões no decorrer do artigo.
Preparando o ambiente
Antes de entrarmos diretamente no assunto, vamos executar o código abaixo para a criação do nosso ambiente de estudo. Estou utilizando o banco AdventureWorks2012 como referência.
— Criar o ambiente
use tempdb
go
CREATE TABLE dbo.Clientes
( ClienteID INT NOT NULL PRIMARY KEY,
Nome VARCHAR(50) NOT NULL,
Sobrenome VARCHAR(50) NOT NULL,
);
INSERT dbo.Clientes
SELECT
c.CustomerID
, p.FirstName
, p.LastName
FROM AdventureWorks2012.Sales.Customer c
JOIN AdventureWorks2012.Person.Person p
ON c.PersonID = p.BusinessEntityID
GO
CREATE TABLE dbo.Vendas
( VendasID INT NOT NULL PRIMARY KEY,
Data DATETIME NOT NULL,
ClienteID INT NOT NULL FOREIGN KEY REFERENCES dbo.Clientes (ClienteID),
TotalDaVenda DECIMAL(15,4) NOT NULL
);
INSERT dbo.Vendas
SELECT TOP 40 PERCENT
h.SalesOrderID
, h.OrderDate
, h.CustomerID
, h.TotalDue
FROM AdventureWorks2012.Sales.SalesOrderHeader h;
CREATE NONCLUSTERED INDEX AK_Vendas_ClienteID
on dbo.Vendas (ClienteID)
GO
INNER JOIN versus IN versus EXISTS
Imagine a seguinte sentença: recuperar todos os clientes que já realizaram compras na empresa?
— 1) Usando INNER JOIN com DISTINCT
SELECT DISTINCT c.ClienteID, c.Nome, c.Sobrenome
FROM dbo.Clientes c
JOIN dbo.Vendas v ON v.ClienteID = c.ClienteID
— 2) Usando IN
SELECT c.ClienteID, c.Nome, c.Sobrenome
FROM dbo.Clientes c
WHERE c.ClienteID IN
( SELECT v.ClienteID FROM dbo.Vendas v )
— 3) Usando EXISTS
SELECT c.ClienteID, c.Nome, c.Sobrenome
FROM dbo.Clientes c
WHERE EXISTS
( SELECT 1 FROM dbo.Vendas v
WHERE v.ClienteID = c.ClienteID )
Veja que:
1. Os resultados gerados são os mesmos;
2. Os planos de execução são idênticos.
Nos exemplos expostos anteriormente, para cada linha da tabela A o otimizador necessita checar se existe pelo menos uma linha correspondente na tabela B (lado oposto). Isso significa que o otimizador não precisa percorrer todas as linhas da outra tabela. Existe por aí a ideia equivocada de que quando usamos o IN o otimizador obrigatoriamente percorre todas as linhas da tabela B (o que não é verdade). O IN segue basicamente o mesmo princípio do EXISTS: fazer teste de existência. Atente que para alcançarmos o LEFT SEMI JOIN a partir do INNER JOIN, foi necessário restringir a lista de colunas da cláusula SELECT para recuperar apenas as colunas da tabela A e usarmos o DISTINCT para suprimir as linhas redundantes (este é o segredo). Apesar de não ter sido demonstrado anteriormente, também é possível alcançar um semi-join através do operador INTERSECT.
Anti-Semi Join (OUTER JOIN versus NOT IN versus NOT EXISTS)
O inverso de um semi join é um anti-semi join, que ocorre quando linhas em uma tabela A não possuem correspondentes em uma tabela B (é baseado na não existência). É possível alcançar um anti-semi join através de subconsultas utilizando NOT IN ou EXISTS, ou ainda através do operador EXCEPT. Também é possível alcançar um anti-semi join utilizando outer join, filtrando somente as outer rows.
Imagine a seguinte sentença: recuperar todos os clientes que nunca realizaram compras na empresa?
— 1) Usando NOT IN
SELECT c.ClienteID, c.Nome, c.Sobrenome
FROM dbo.Clientes c
WHERE c.ClienteID NOT IN
( SELECT v.ClienteID FROM dbo.Vendas v )
— 2) Usando NOT EXISTS
SELECT c.ClienteID, c.Nome, c.Sobrenome
FROM dbo.Clientes c
WHERE NOT EXISTS
( SELECT 1 FROM dbo.Vendas v
WHERE v.ClienteID = c.ClienteID )
Vejamos o que acontece quando utilizamos um LEFT OUTER JOIN.
— 3) Usando LEFT OUTER JOIN
SELECT c.ClienteID, c.Nome, c.Sobrenome
FROM dbo.Clientes c
LEFT JOIN dbo.Vendas v ON v.ClienteID = c.ClienteID
WHERE v.ClienteID IS NULL
O resultado da consulta é igual, mas o plano de execução gerado não é o mesmo. Veja que apesar de alcançarmos os mesmos resultados, explicitamente não aparece um Anti Semi Join quando usamos o LEFT OUTER JOIN. Se compararmos a performance das consultas veremos que o NOT EXISTS é mais eficiente. Por conta disso, deixo a seguinte dica: se você precisar recuperar linhas baseadas na não existência em outra tabela, prefira NOT EXISTS ou NOT IN; evite usar OUTER JOIN como foi demonstrado anteriormente.
Verdades e Mitos sobre JOIN’s
Publicado; 16/06/2012 Arquivado em: SQL Optimizer, SQL Server Deixe um comentárioDurante os meus trabalhos de query tuning é muito comum, por parte dos desenvolvedores, surgirem questões – e também mitos – relacionadas ao uso de JOIN. O tema pode denotar a falsa impressão de que é assunto para iniciantes, mas, eu garanto: não é! Faço uso do que Itzik Ben-Gan escreveu no seu livro Inside Microsoft SQL Server 2008: T-SQL Querying:
O bom profissional é aquele que constrói o seu conhecimento a partir de fortes fundamentos, dominando o básico.
Sem mais delongas, vamos ao que interessa. Continue lendo.
Coisas do Otimizador: quando diferenças entre Estimated Number of Rows x Actual Number Rows acontecem mesmo com estatísticas atualizadas
Publicado; 27/05/2012 Arquivado em: SQL Optimizer | Tags: Estatísticas, Otimizador de Consulta Deixe um comentárioQuando você analisa um plano de execução de alguma query e percebe que existe divergência entre o número estimado de linhas e o número atual de linhas, qual a sua conclusão inicial? Normalmente a conclusão é: a estatística deve estar desatualizada. Entretanto, essa afirmação nem sempre é verdadeira, conforme estaremos demonstrando neste artigo.
No decorrer do exemplo a seguir, pontuarei algumas questões importantes sobre Otimizador, Estatísticas e o Plan Cache. Divirtam-se!
use tempdb
go
— Cria uma tabela que guardará dados de pedidos de venda
create table dbo.stsPedidos
( IdPedido int not null primary key,
DtPedido Datetime not null,
DtEnvio Datetime not null,
Status tinyint not null
);
— Insere 5 mil linhas
insert dbo.stsPedidos
select top 5000 h.SalesOrderID, h.OrderDate, h.ShipDate, h.Status
from AdventureWorks2012.Sales.SalesOrderHeader h;
— Cria estatística para a coluna DtPedido
create statistics ST_Pedidos_Data on dbo.stsPedidos (DtPedido);
— Exibe o histograma
dbcc show_statistics(‘stsPedidos’, ST_Pedidos_data) with histogram;
go
Atente para as linhas 3 e 9:
· 2005-07-09 tem 6 valores idênticos;
· 2005-08-01 tem 80 valores idênticos;
Vejamos o que acontece quando exibimos o plano de execução estimado para as duas consultas abaixo. Lembre-se: para exibir o plano estimado basta selecionar a query e usar o atalho CTRL+L.
SELECT * FROM dbo.stsPedidos s WHERE s.DtPedido = ‘2005-07-09T00:00:00.000’;
SELECT * FROM dbo.stsPedidos s WHERE s.DtPedido = ‘2005-08-01T00:00:00.000’;
|
|
|
![clip_image002[4]](https://adeilsonrbrito.files.wordpress.com/2012/05/clip_image0024.jpg "clip_image002[4]")
Como essas queries nunca foram efetivamente executadas (acabamos de criar as tabelas), então não existe plano de execução armazenado no plan cache do SQL Server que seja compatível com as consultas. Nessa situação o SQL produz e exibe um plano estimado individual para cada query. Esses planos estimados não são guardados em cache.
Vamos consultar o Plan Cache para comprovar que os planos estimados, gerados conforme o exemplo anterior, não foram guardados em memória.
select * from
(
select t.text, p.plan_handle
from sys.dm_exec_cached_plans p
cross apply sys.dm_exec_sql_text(p.plan_handle) t
) a
where a.text like ‘SELECT * FROM dbo.stsPedidos%’;
Vamos agora executar de fato as consultas, usando o atalho F5, e incluir o plano atual das mesmas. Não vou anexar as figuras contendo o resultado das consultas, mas elas apresentaram o seguintes totais de linhas:
· ‘2005-07-09T00:00:00.000’: retornou 6 linhas
· ‘2005-08-01T00:00:00.000’: retornou 80 linhas
|
|
|
![clip_image002[6]](https://adeilsonrbrito.files.wordpress.com/2012/05/clip_image0026.jpg "clip_image002[6]")
![clip_image004[4]](https://adeilsonrbrito.files.wordpress.com/2012/05/clip_image0044.jpg "clip_image004[4]")
Note que ao colocar o mouse sobre o operador SELECT, de ambas as queries, o número estimado de linhas é 6. Opa! Mas se a segunda query (‘2005-08-01T00:00:00.000’) trouxe a quantidade certa de linhas (80) então por que, mesmo trazendo corretamente o result set, o número estimado é divergente?
Vejamos detalhes do operador Clustered Index Scan dessa segunda query.
Atente para a divergência entre Actual Number of Rows e Estimated Number of Rows. Essa divergência não é causada por estatística desatualizada, pois nós criamos a estatística para a coluna e desde então não fizemos nenhuma alteração de dados. Como não existe índice para essa coluna, a única opção que sobra ao otimizador é sempre fazer uma scan na tabela (neste caso um scan no índice clustered da tabela). Para o Query Optimizer (QO), nessa situação não fará diferença (em termos de escolha e geração de Execution Plan) se o número estimado de linhas é 6, 60, 500, etc, pois a operação a ser desempenhada sempre será um scan na coluna da tabela.
Há uma informação interessantíssima que vale à pena comentar. Para tanto, vou exibir as propriedades do operador SELECT referente ao plano de execução da segunda query (‘2005-08-01T00:00:00.000’).
Veja que o otimizador automaticamente substituiu o literal ‘2005-08-01T00:00:00.000’ da query pelo parâmetro @1 (opção conhecida como PARAMETERIZATION), conforme primeira figura. Na segunda figura podemos verificar que o plano de execução foi compilado usando como valor ‘2005-07-09T00:00:00.000’, conforme a propriedade Parameter Compiled Value.
Vejamos o que acontece quando utilizamos um índice para a coluna DtPedido.
— Elimina a estatística criada anteriormente
drop statistics dbo.stsPedidos.ST_Pedidos_Data;
— cria o índice
create nonclustered index AK_Pedidos_DtPedido on dbo.stsPedidos (DtPedido);
Atenção: não mostrarei o histograma da estatística implicitamente criada para o índice, porque ele é exatamente igual ao histograma exibido no início deste artigo.
— Executa e inclui o plano atual das duas queries abaixo
SELECT * FROM dbo.stsPedidos s
WHERE s.DtPedido = ‘2005-07-09 00:00:00.000’;
SELECT * FROM dbo.stsPedidos s
WHERE s.DtPedido = ‘2005-08-01 00:00:00.000’;
|
|
|
|
![clip_image002[8]](https://adeilsonrbrito.files.wordpress.com/2012/05/clip_image0028.jpg "clip_image002[8]")
![clip_image004[6]](https://adeilsonrbrito.files.wordpress.com/2012/05/clip_image0046.jpg "clip_image004[6]")
Note que os planos de cada query são diferentes. Para a primeira query temos um Index Seek, enquanto que na segunda continuamos com um Clustered Index Scan.
Vamos executar mais duas queries e comparar o Actual Execution Plan de ambas.
|
|
|
![clip_image002[10]](https://adeilsonrbrito.files.wordpress.com/2012/05/clip_image00210.jpg "clip_image002[10]")
![clip_image004[8]](https://adeilsonrbrito.files.wordpress.com/2012/05/clip_image0048.jpg "clip_image004[8]")
Os planos são os mesmos e, o melhor de tudo, não há divergência entre o número atual de linhas e o número estimado de linhas.
|
|
|
![clip_image002[12]](https://adeilsonrbrito.files.wordpress.com/2012/05/clip_image002121.jpg "clip_image002[12]")
![clip_image004[10]](https://adeilsonrbrito.files.wordpress.com/2012/05/clip_image004101.jpg "clip_image004[10]")
Conclusão
Conforme o que foi discorrido, é possível existir divergência entre Actual Number of Rows e Estimated Number of Rows, as quais não são causadas por estatísticas desatualizadas. Entender quando e como isso acontece é importante, sobretudo, durante trabalhos de tuning.
Adeilson Blog 