Mitos do SQL Server – A ordem das tabelas influencia no desempenho de uma instrução SELECT ?

Olá Pessoal,

Para inaugurar a categoria "Mitos do SQL Server" em relação ao SQL Server, escolhi uma thread que vi nos fóruns de SQL Server do MSDN e do Technet intitulada "A ordem das joins pode afetar a performance de uma consulta no sql ?". Independente da resposta, estou certo que qualquer um que trabalha (ou já trabalhou) com SQL já teve essa dúvida (mesmo que nos primeiros contatos). E será que faz diferença ? Além de pensar na elaboração da consulta ainda é preciso pensar na ordem das tabelas para deixá-la mais performática ? Ao invés de simplesmente dar uma resposta e talvez perpetuar um mito, nada melhor que tentar encontrar a resposta de forma verídica. A seguir três versões da mesma consulta no Adventure Works.

— Ordem 1
— Employee, Contact, SalesOrderHeader
SELECT
    C.Title, C.FirstName, C.MiddleName, C.LastName, C.EmailAddress, C.Phone,
    E.NationalIDNumber, E.LoginID, E.HireDate,
    COUNT(S.SalesOrderID) As TotalVendas
FROM
    HumanResources.Employee As E
    INNER JOIN Person.Contact As C ON E.ContactID = C.ContactID
    INNER JOIN Sales.SalesOrderHeader As S ON E.EmployeeID = S.SalesPersonID
GROUP BY
    C.Title, C.FirstName, C.MiddleName, C.LastName, C.EmailAddress, C.Phone,
    E.NationalIDNumber, E.LoginID, E.HireDate

— Ordem 2
— Contact, Employee, SalesOrderHeader

SELECT
    C.Title, C.FirstName, C.MiddleName, C.LastName, C.EmailAddress, C.Phone,
    E.NationalIDNumber, E.LoginID, E.HireDate,
    COUNT(S.SalesOrderID) As TotalVendas
FROM
    Person.Contact As C
    INNER JOIN HumanResources.Employee As E ON C.ContactID = E.ContactID
    INNER JOIN Sales.SalesOrderHeader As S ON E.EmployeeID = S.SalesPersonID
GROUP BY
    C.Title, C.FirstName, C.MiddleName, C.LastName, C.EmailAddress, C.Phone,
    E.NationalIDNumber, E.LoginID, E.HireDate

— Ordem 3
— SalesOrderHeader, Contact, Employee

SELECT
    C.Title, C.FirstName, C.MiddleName, C.LastName, C.EmailAddress, C.Phone,
    E.NationalIDNumber, E.LoginID, E.HireDate,
    COUNT(S.SalesOrderID) As TotalVendas
FROM
    Sales.SalesOrderHeader As S
    INNER JOIN HumanResources.Employee As E ON E.EmployeeID = S.SalesPersonID
    INNER JOIN Person.Contact As C ON E.ContactID = C.ContactID
GROUP BY
    C.Title, C.FirstName, C.MiddleName, C.LastName, C.EmailAddress, C.Phone,
    E.NationalIDNumber, E.LoginID, E.HireDate

Como a consulta retorna exatamente os mesmos resultados, executei as três juntas duas vezes para que os planos de execução e páginas fiquem em memória e assim o resultado seja mais imparcial. Se apenas a primeira execução fosse considerada, talvez a segunda consulta se beneficiasse das páginas em memória da primeira consulta e talvez a terceira consulta tivesse a mesma vantagem em relação a segunda consulta.

A execução dessas três consultas em um mesmo BATCH com a análise do plano de execução, estatísticas de tempo e IO é mostrada a seguir:

Ordem Tempo IO necessário
Employee, Contact, SalesOrderHeader 223 ms Table ‘SalesOrderHeader’. Scan count 290, logical reads 641
Table ‘Contact’. Scan count 0, logical reads 607
Table ‘Employee’. Scan count 1, logical reads 9
Contact, Employee, SalesOrderHeader 266 ms Table ‘SalesOrderHeader’. Scan count 290, logical reads 641
Table ‘Contact’. Scan count 0, logical reads 607
Table ‘Employee’. Scan count 1, logical reads 9
SalesOrderHeader, Contact, Employee 211 ms Table ‘SalesOrderHeader’. Scan count 290, logical reads 641
Table ‘Contact’. Scan count 0, logical reads 607
Table ‘Employee’. Scan count 1, logical reads 9

O plano de execução das três consultas foi exatamento mesmo conforme a figura abaixo:

As estatísticas de tempo, o gasto de IO o percentual de 33% para cada consulta e o mesmo plano de execução evidenciam que as três consultas são exatamente idênticas tanto no resultado quando no desempenho. Pode-se argumentar que o tempo de execução tem ligeiras diferenças em ms e que isso talvez sugira que a terceira consulta seja a mais eficiente. Isso no entanto não é verdade. Esse tempo é obtido conforme a disponibilidade de recursos da máquina na ocasião da execução e a provável diferença pode ter se dado porque naquele momento uma fração mínima de quantum estava mais ou menos disponível para uma determinada consulta. Não há como o tempo delas ser exatamente o mesmo, mas devido a baixíssima variação e aos demais indicadores concluí-se que as consultas são exatamente as mesmas mudando apenas sua sintaxe.

Esse resultado por si só já parece suficiente para refutar a hipótese de que a ordem das tabelas influencia no desempenho de uma instrução SELECT visto que as três consultas têm exatamente o mesmo desempenho. Sim, é o que parece, mas na verdade o que se concluiu até agora é que as três consultas têm exatamente o mesmo desempenho não porque a ordem não tenha diferença, mas sim porque não importa a ordem, o SQL Server compilou todas para fazer o JOIN na ordem que ele julgou melhor, ou seja, primeiro é feito o JOIN entre HumanResources.Employee com a tabela Person.Contacts e o resultado dessa junção é submetido a um novo JOIN contra Sales.SalesOrderHeader conforme a figura abaixo:

Para saber mesmo se a ordem das tabelas faz ou não diferença no desempenho de uma instrução SELECT, é necessário "sobrescrever" as idéias do otimizador através de HINTs. No caso da ordem das tabelas, isso pode ser feito com o HINT FORCE ORDER conforme o script a seguir:

— Ordem 1
— Employee, Contact, SalesOrderHeader
SELECT
    C.Title, C.FirstName, C.MiddleName, C.LastName, C.EmailAddress, C.Phone,
    E.NationalIDNumber, E.LoginID, E.HireDate,
    COUNT(S.SalesOrderID) As TotalVendas
FROM
    HumanResources.Employee As E
    INNER JOIN Person.Contact As C ON E.ContactID = C.ContactID
    INNER JOIN Sales.SalesOrderHeader As S ON E.EmployeeID = S.SalesPersonID
GROUP BY
    C.Title, C.FirstName, C.MiddleName, C.LastName, C.EmailAddress, C.Phone,
    E.NationalIDNumber, E.LoginID, E.HireDate
OPTION (FORCE ORDER)

— Ordem 2
— Contact, Employee, SalesOrderHeader

SELECT
    C.Title, C.FirstName, C.MiddleName, C.LastName, C.EmailAddress, C.Phone,
    E.NationalIDNumber, E.LoginID, E.HireDate,
    COUNT(S.SalesOrderID) As TotalVendas
FROM
    Person.Contact As C
    INNER JOIN HumanResources.Employee As E ON C.ContactID = E.ContactID
    INNER JOIN Sales.SalesOrderHeader As S ON E.EmployeeID = S.SalesPersonID
GROUP BY
    C.Title, C.FirstName, C.MiddleName, C.LastName, C.EmailAddress, C.Phone,
    E.NationalIDNumber, E.LoginID, E.HireDate
OPTION (FORCE ORDER)

— Ordem 3
— SalesOrderHeader, Contact, Employee

SELECT
    C.Title, C.FirstName, C.MiddleName, C.LastName, C.EmailAddress, C.Phone,
    E.NationalIDNumber, E.LoginID, E.HireDate,
    COUNT(S.SalesOrderID) As TotalVendas
FROM
    Sales.SalesOrderHeader As S
    INNER JOIN HumanResources.Employee As E ON E.EmployeeID = S.SalesPersonID
    INNER JOIN Person.Contact As C ON E.ContactID = C.ContactID
GROUP BY
    C.Title, C.FirstName, C.MiddleName, C.LastName, C.EmailAddress, C.Phone,
    E.NationalIDNumber, E.LoginID, E.HireDate
OPTION (FORCE ORDER)

A execução das consultas alteradas teve as seguintes estatísticas de tempo e IO:

Ordem Tempo IO necessário
Employee, Contact, SalesOrderHeader 201 ms Table ‘SalesOrderHeader’. Scan count 290, logical reads 641
Table ‘Contact’. Scan count 0, logical reads 607
Table ‘Employee’. Scan count 1, logical reads 9
Contact, Employee, SalesOrderHeader 264 ms Table ‘SalesOrderHeader’. Scan count 290, logical reads 641
Table ‘Employee’. Scan count 1, logical reads 9
Table ‘Contact’. Scan count 1, logical reads 41
SalesOrderHeader, Contact, Employee 213 ms Table ‘Worktable’. Scan count 0, logical reads 0
Table ‘Contact’. Scan count 1, logical reads 34
Table ‘Employee’. Scan count 1, logical reads 9
Table ‘SalesOrderHeader’. Scan count 1, logical reads 57

Antes de partir para os planos de execução, um certo detalhe merece atenção. A última consulta produziu uma tabela extra chamada Worktable. Além do acesso a Contact, Employee, e SalesOrderHeader também houve acesso a uma tabela denominada Worktable. Essa tabela representa uma armazenamento temporário (entenda-se uma tabela temporária criada pelo SQL Server) para que o resultado da consulta pudesse ser trabalhado de forma intermediária o que já é um indicativo de piora em relação às demais soluções. Os planos de execução são mostrados abaixo:

Plano de Execução – Consulta 1 (Employee, Contact, SalesOrderHeader)

Plano de Execução – Consulta 2 (Contact, Employee, SalesOrderHeader)

Plano de Execução – Consulta 3 (SalesOrderHeader, Contact, Employee)

Como pode-se notar de um total de 100%, a primeira consulta gasta 11%, a segunda 37% e a terceira 52%. Isso leva a crer que a ordem das tabelas realmente faz toda a diferença no desempenho de uma consulta. As diferenças são consideráveis levando-se em conta que a primeira consulta representa apenas 11% do gasto total e que a terceira consulta representa um esforço maior que as duas anteriores juntas.

A primeira consulta é mais eficiente porque lista as tabelas menores primeiro. A tabela Employees é especificada primeiro e por ser relativamente pequena pode ser colocada em memória e lida com muito mais facilidade. O JOIN entre Employees e Contact irá produzir um resultado com base nos critérios do JOIN e considerando que o relacionamento de Employees e Contact é de 1:1, é de se esperar que o resultado desse JOIN seja exatamente a quantidade de empregados existentes (290). Isso é mais eficiente que fazer o JOIN entre Contact e Employees (se Contact vier primeiro), pois, existem 19972 registros em Contact e colocá-la em memória para lê-la é bem mais dispendioso até porque desses 19972, apenas 290 seriam válidos, o que levaria a um esforço maior do que a primeira opção. Especificar a SalesOrderHeader em primeiro lugar seria a pior escolha, pois, ela conta com 31465 registros e representa a maior tabela.

É difícil elaborar uma regra geral, mas o que normalmente seria o mais recomendável para todos os casos é especificar as tabelas menores primeiro e as maiores por último.

Algumas considerações

Esse teste permite concluir que o mito é sim verdadeiro e que inegavelmente a ordem em que as tabelas são especificadas influencia sim no desempenho de uma consulta. Entretanto, isso não significa que seja necessário preocupar-se com isso. Se os otimizadores fossem os mesmos de vinte anos atrás realmente seria preciso uma atenção especial na ordem em que as tabelas são especificadas para elaborar consultas de alto desempenho. O pior é que à medida que novos registros são inseridos, alterados e excluídos, uma consulta que anteriormente tivesse um alto desempenho poderia representar uma escolha ruim, pois, as premissas de qual é a ordem correta estariam alteradas. Sem dúvida, seria um CAOS ter de se preocupar com a formulação da consulta e de revisões periódicas para verificar se a ordem não ficou "errada" por conta de alguma carga e (ou) expurgo de dados.

Por essas e outras que os otimizadores tiveram que adaptar-se e se tornarem mais inteligentes. Que a ordem faz diferença não há dúvida, mas também não há dúvida de que o otimizador irá avaliar exatamente qual é a melhor ordem. Não é preciso mais preocupar-se com a ordem "correta", pois, o otimizador já irá se encarregar disso automaticamente dispensando esse cuidado. Aqueles que têm a preocupação com a ordem e fazem disso um aspecto imprescindível da otimização das consultas são aqueles que ainda pensam como os otimizadores de vinte anos atrás e talvez nunca tenham testado as diferentes soluções para ver se nos tempos atuais isso ainda faz diferença (felizmente não).

Há quem julgue que talvez o uso do HINT com a ordem "correta" dispense o custo extra do otimizador avaliar a melhor ordem. Sinceramente não acho um argumento válido, visto que sobrescrever o otimizador, irá gerar um trabalho extra de avaliar constamente se o uso do HINT FORCE ORDER ainda será mais eficiente. Vale a pena lembrar que o ganho também é imperceptível. Se o exemplo for testado com o HINT FORCE ORDER na melhor ordem e sem o HINT, o desempenho é o mesmo.

Então não se preocupe com a ordem em que as tabelas são especificadas na cláusula SELECT. É melhor investir tempo em tentar otimizar outros aspectos como índices, bloqueios, predicados bem elaborados, etc.

[ ]s,

Gustavo

3 Respostas para “Mitos do SQL Server – A ordem das tabelas influencia no desempenho de uma instrução SELECT ?

  1. Olá Gustovo Maiar,Essa sua estréia sobre Mitos do SQL foi fabulosa. Fantástico mesmo.

  2. Olá Heberton,Pois é. Parece que esses mitos vão dar o que falar…Abs,

  3. Vladimir Michel

    O grande "problema" nesse tipo de otimização é em ambientes onde os desenvolvedores também escrevem códigos SQL, onde você precisa convencer os mesmos (nem sempre apenas argumentos técnicos bastam) a fazerem tal otimização!Mas cabe a nós pelo menos tentar!

Deixe uma 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 )

Imagem do Twitter

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

Foto do Facebook

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

Foto do Google+

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

Conectando a %s