Blog – Fabiano Neves Amorim

Galera, existem algumas situações onde o QO(Query Optimizer) simplesmente, decide remover uma instrução do join de uma consulta o que acaba gerando um inesperado Produto Cartesiano, ou seja, a mesma coisa que um join sem relacionamento, um cross join.

 

Por exemplo, imagine o seguinte código:

 

use tempdb

GO

 

declare @tab1 Table(a Int)

 

insert into @tab1

select TOP 1000 1

  from sysobjects b, sysobjects a

 

set statistics io on

set statistics time on

 

select * from @tab1 a

 inner join @tab1 b

    on a.a = b.a

 

select * from @tab1 a

 inner join @tab1 b

    on a.a = b.a

 where a.a = 1

 

set statistics io off

set statistics time off

 

A duas consultas acima retornam todos os registros ta tabela @tab1, mas existe uma grande diferença entre a primeira e a segunda consulta.

 

A primeira diferença que podemos notar é que a segunda consulta contem uma condição no where onde a.a tem que ser igual a 1. Ai é que ta, neste caso o QO sabe que existe uma redundância de comparação, pois se “a.a” é igual 1(where), e “a.a” é igual a “b.a” (join), logo ele sabe que b.a também será igual a 1. Portanto neste caso ele remove a condição do join (a.a = b.a) e aplica o filtro de “a = 1” nas tabelas “a” e “b”.

 

Este comportamento pode ser evidenciado ao analisarmos os planos de execução gerados. O plano da segunda consulta ficou assim:

 

StmtText	Argument
select * from @tab1 a   inner join @tab1 b      on a.a = b.a   where a.a = 1	NULL
|–Nested Loops(Inner Join)	NULL
|–Table Scan(OBJECT:(@tab1 AS [b]), WHERE:(@tab1.[a] as [b].[a]=(1)))	OBJECT:(@tab1 AS [b]), WHERE:(@tab1.[a] as [b].[a]=(1))
|–Table Scan(OBJECT:(@tab1 AS [a]), WHERE:(@tab1.[a] as [a].[a]=(1)))	OBJECT:(@tab1 AS [a]), WHERE:(@tab1.[a] as [a].[a]=(1))

 

Repare que na coluna argument o QO gerou apenas um argumento para a leitura das tabelas, e no Loop ele não gerou a condição de join. Isso caracteriza um Produto Cartesiano, onde para cada linha da tabela definida como “a”, o SQL vai ligar com todas as linhas da tabela “b”.

 

Ok, mas isso é ruim?… Sim neste caso sim… Pois para esta consulta utilizar um algoritmo de Hash para fazer o join seria muito mais eficaz. Para confirmar isso, basta verificar o plano gerado pela primeira consulta. Lembrando que ambas as consultas são relativamente iguais. Repare que o SQL gerou um Hash Join para fazer a ligação entre as tabelas.

 

StmtText	Argument
select * from @tab1 a   inner join @tab1 b      on a.a = b.a	NULL
|–Hash Match(Inner Join, HASH:([b].[a])=([a].[a]), RESIDUAL:(@tab1.[a] as [b].[a]=@tab1.[a] as [a].[a]))	HASH:([b].[a])=([a].[a]), RESIDUAL:(@tab1.[a] as [b].[a]=@tab1.[a] as [a].[a])
|–Table Scan(OBJECT:(@tab1 AS [b]))	OBJECT:(@tab1 AS [b])
|–Table Scan(OBJECT:(@tab1 AS [a]))	OBJECT:(@tab1 AS [a])

 

Se analisarmos os resultados do statistics io e statistics time, veremos que a primeira consulta, utilizou menos tempo, IO e CPU para retornar os dados.

 

Primeira consulta (Hash Join):

Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table ‘#2E75B1C0’. Scan count 2, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 

SQL Server Execution Times:

   CPU time = 328 ms,  elapsed time = 19976 ms.

 

 

Segunda consulta (Loop Join):

Table ‘#2E75B1C0’. Scan count 2, logical reads 2002, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 

SQL Server Execution Times:

   CPU time = 407 ms,  elapsed time = 20377 ms.

 

Bom, mas você pode estar pensando, bem, neste caso é só utilizar um hint para forçar o uso do Hash Join certo? Errado, se você tentar fazer isso vai receber a seguinte mensagem:

 

select * from @tab1 a

 inner join @tab1 b

    on a.a = b.a

 where a.a = 1

option(hash join)

 

Msg 8622, Level 16, State 1, Line 20

Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.

 

Como o SQL remove a condição do Join, ele terá que fazer um Produto Cartesiano e o SQL só consegue fazer isso utilizando o Loop Join L. Neste caso, o Hint colide com a Lógica do QO e gera o erro.

 

Como resolver o problema?….

Basta esperar a próxima versão do SQL, e torcer para que eles copiem o Oracle, que neste caso criou um parâmetro para tratar com este problema.

http://jonathanlewis.wordpress.com/2006/12/13/cartesian-merge-join/

 

Enviei este exemplo para o Boris Baryshnikov (SQL Server Engine), e ele me respondeu dizendo que estão trabalhando em uma correção para uma futura release.

 

Já existe um chamado no Connect falando sobre isso, portanto, só nos resta aguardar, e ficarmos atentos a este problema…