Inicial > SQL Server > Produto Cartesiano e Query Optimizer

Produto Cartesiano e Query Optimizer

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…

Categorias:SQL Server
  1. Robson Miranda De Souza Toyofuku
    20 de fevereiro de 2020 às 10:13

    Essa matéria é de 2009. Já existe uma solução para este problema?

    • 20 de fevereiro de 2020 às 10:21

      Se acredita que não :-(… E duvido que teremos uma solução pra isso… Abs.

  1. No trackbacks yet.

Deixe uma resposta para mcflyamorim Cancelar resposta

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

Logotipo do WordPress.com

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

Foto do Google

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

Imagem do Twitter

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

Foto do Facebook

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

Conectando a %s

%d blogueiros gostam disto: