Início / dba / Perguntas / 10215
Accepted
Hafthor
Asked: 2012-01-05 16:18:05 +0800 CST

A inclusão de ORDER BY na consulta que não retorna nenhuma linha afeta drasticamente o desempenho

  • 772

Dada uma junção simples de três tabelas, o desempenho da consulta muda drasticamente quando ORDER BY é incluído, mesmo sem nenhuma linha retornada. O cenário real do problema leva 30 segundos para retornar zero linhas, mas é instantâneo quando ORDER BY não é incluído. Por quê?

SELECT * 
FROM tinytable t                          /* one narrow row */
JOIN smalltable s on t.id=s.tinyId        /* one narrow row */
JOIN bigtable b on b.smallGuidId=s.GuidId /* a million narrow rows */
WHERE t.foreignId=3                       /* doesn't match */
ORDER BY b.CreatedUtc          /* try with and without this ORDER BY */

Entendo que poderia ter um índice em bigtable.smallGuidId, mas acredito que isso pioraria as coisas neste caso.

Aqui está o script para criar/preencher as tabelas para teste. Curiosamente, parece importante que smalltable tenha um campo nvarchar(max). Também parece importar que estou entrando na bigtable com um guid (o que eu acho que faz com que ele queira usar correspondência de hash).

CREATE TABLE tinytable
  (
     id        INT PRIMARY KEY IDENTITY(1, 1),
     foreignId INT NOT NULL
  )

CREATE TABLE smalltable
  (
     id     INT PRIMARY KEY IDENTITY(1, 1),
     GuidId UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
     tinyId INT NOT NULL,
     Magic  NVARCHAR(max) NOT NULL DEFAULT ''
  )

CREATE TABLE bigtable
  (
     id          INT PRIMARY KEY IDENTITY(1, 1),
     CreatedUtc  DATETIME NOT NULL DEFAULT GETUTCDATE(),
     smallGuidId UNIQUEIDENTIFIER NOT NULL
  )

INSERT tinytable
       (foreignId)
VALUES(7)

INSERT smalltable
       (tinyId)
VALUES(1)

-- make a million rows 
DECLARE @i INT;

SET @i=20;

INSERT bigtable
       (smallGuidId)
SELECT GuidId
FROM   smalltable;

WHILE @i > 0
  BEGIN
      INSERT bigtable
             (smallGuidId)
      SELECT smallGuidId
      FROM   bigtable;

      SET @i=@i - 1;
  END

Eu testei no SQL 2005, 2008 e 2008R2 com os mesmos resultados.

sql-server performance

5 respostas

  1. Best Answer

    Concordo com a resposta de Martin Smith, mas o problema não é simplesmente estatístico, exatamente. As estatísticas para a colunastrangeId (supondo que as estatísticas automáticas estejam ativadas) mostram com precisão que não existem linhas para um valor de 3 (existe apenas uma, com um valor de 7):

    DBCC SHOW_STATISTICS (tinytable, foreignId) WITH HISTOGRAM

    saída de estatísticas

    O SQL Server sabe que as coisas podem ter mudado desde que as estatísticas foram capturadas, então pode haver uma linha para o valor 3 quando o plano for executado . Além disso, qualquer quantidade de tempo pode decorrer entre a compilação e a execução do plano (afinal, os planos são armazenados em cache para reutilização). Como diz Martin, o SQL Server contém lógica para detectar quando modificações suficientes foram feitas para justificar a recompilação de qualquer plano em cache por motivos de otimização.

    No entanto, nada disso importa em última análise. Com uma exceção de caso extremo, o otimizador nunca estimará o número de linhas produzidas por uma operação de tabela como zero. Se puder determinar estaticamente que a saída deve ser sempre zero linhas, a operação é redundante e será removida completamente.

    Em vez disso, o modelo do otimizador estima um mínimo de uma linha. Empregar esta heurística tende a produzir melhores planos em média do que seria o caso se uma estimativa mais baixa fosse possível. Um plano que produza uma estimativa de linha zero em algum estágio seria inútil a partir desse ponto no fluxo de processamento, pois não haveria base para tomar decisões baseadas em custo (linhas zero são linhas zero, não importa o que aconteça). Se a estimativa estiver errada, a forma do plano acima da estimativa de linha zero quase não tem chance de ser razoável.

    O segundo fator é outra suposição de modelagem chamada de Suposição de Contenção. Isso basicamente diz que se uma consulta une um intervalo de valores com outro intervalo de valores, é porque os intervalos se sobrepõem. Outra maneira de colocar isso é dizer que a junção está sendo especificada porque espera-se que as linhas sejam retornadas. Sem esse raciocínio, os custos geralmente seriam subestimados, resultando em planos ruins para uma ampla gama de consultas comuns.

    Essencialmente, o que você tem aqui é uma consulta que não se encaixa no modelo do otimizador. Não há nada que possamos fazer para 'melhorar' as estimativas com várias colunas ou índices filtrados; não há como obter uma estimativa menor que 1 linha aqui. Um banco de dados real pode ter chaves estrangeiras para garantir que essa situação não ocorra, mas supondo que não seja aplicável aqui, ficamos com o uso de dicas para corrigir a condição fora do modelo. Qualquer número de abordagens de dicas diferentes funcionará com essa consulta. OPTION (FORCE ORDER)é aquele que funciona bem com a consulta escrita.

    • 35

  2. O problema básico aqui é de estatística.

    Para ambas as consultas, a contagem de linhas estimada mostra que acredita que o final SELECTretornará 1.048.580 linhas (o mesmo número de linhas estimado em bigtable) em vez do 0 que realmente ocorre.

    Ambas as JOINcondições correspondem e preservariam todas as linhas. Eles acabam sendo eliminados porque a única linha tinytablenão corresponde ao t.foreignId=3predicado.

    Se você correr

    SELECT * 
FROM tinytable t  
WHERE t.foreignId=3  AND id=1

    e observe o número estimado de linhas em 1vez de 0e esse erro se propaga por todo o plano. tinytableatualmente contém 1 linha. As estatísticas não seriam recompiladas para esta tabela até que 500 modificações de linha ocorressem para que uma linha correspondente pudesse ser adicionada e não acionaria uma recompilação.

    A razão pela qual a ordem de junção muda quando você adiciona a ORDER BYcláusula e há uma varchar(max)coluna smalltableé porque ela estima que varchar(max)as colunas aumentarão o tamanho da linha em 4.000 bytes em média. Multiplique isso por 1048580 linhas e isso significa que a operação de classificação precisaria de 4 GB estimados, portanto, decide sensatamente fazer a SORToperação antes do arquivo JOIN.

    Você pode forçar a ORDER BYconsulta a adotar a ORDER BYestratégia de não junção com o uso das dicas abaixo.

    SELECT *
FROM   tinytable t /* one narrow row */
       INNER MERGE JOIN smalltable s /* one narrow row */
                        INNER LOOP JOIN bigtable b
                          ON b.smallGuidId = s.GuidId /* a million narrow rows */
         ON t.id = s.tinyId
WHERE  t.foreignId = 3 /* doesn't match */
ORDER  BY b.CreatedUtc
OPTION (MAXDOP 1)

    O plano mostra um operador de classificação com um custo estimado de subárvore de 12,000contagens de linha estimadas quase e incorretas e tamanho de dados estimado.

    Plano

    Aliás, não achei que substituir as UNIQUEIDENTIFIERcolunas por números inteiros alterou as coisas no meu teste.

    • 23

  3. Ative o botão Mostrar Plano de Execução e você poderá ver o que está acontecendo. Aqui está o plano para a consulta "lenta": insira a descrição da imagem aqui

    E aqui está a consulta "rápida": insira a descrição da imagem aqui

    Olhe para isso - executados juntos, a primeira consulta é ~ 33x mais "cara" (proporção de 97: 3). O SQL está otimizando a primeira consulta para ordenar o BigTable por data e hora e, em seguida, executando um pequeno loop de "busca" em SmallTable e TinyTable, executando-os 1 milhão de vezes cada (você pode passar o mouse sobre o ícone "Clustered Index Seek" para obter mais estatísticas). Portanto, a classificação (27%) e 2 x 1 milhão de "buscas" em tabelas pequenas (23% e 46%) são a maior parte da consulta cara. Em comparação, a não- ORDER BYconsulta executa um total geral de 3 varreduras.

    Basicamente, você encontrou uma lacuna na lógica do otimizador SQL para seu cenário específico. Mas, conforme declarado por TysHTTP, se você adicionar um índice (o que retarda um pouco sua inserção/atualização), sua digitalização torna-se muito rápida.

    • 3

  4. O que está acontecendo é que o SQL está decidindo executar o pedido antes da restrição.

    Tente isto:

    SELECT *
(
SELECT * 
FROM tinytable t
    INNER JOIN smalltable s on t.id=s.tinyId
    INNER JOIN bigtable b on b.smallGuidId=s.GuidId
WHERE t.foreignId=3
) X
ORDER BY b.CreatedUtc

    Isso fornece a você um desempenho aprimorado (neste caso, em que a contagem de resultados retornados é muito pequena), sem realmente ter o desempenho atingido ao adicionar outro índice. Embora seja estranho quando o otimizador SQL decide executar a ordem antes da junção, é provável que, se você realmente tivesse dados de retorno, classificá-los após as junções levaria mais tempo do que classificar sem.

    Por fim, tente executar o seguinte script e veja se as estatísticas e índices atualizados corrigem o problema que você está tendo:

    EXEC [sp_MSforeachtable] @command1="RAISERROR('UPDATE STATISTICS(''?'') ...',10,1) WITH NOWAIT UPDATE STATISTICS ? "

EXEC [sp_MSforeachtable] @command1="RAISERROR('DBCC DBREINDEX(''?'') ...',10,1) WITH NOWAIT DBCC DBREINDEX('?')"

EXEC [sp_MSforeachtable] @command1="RAISERROR('UPDATE STATISTICS(''?'') ...',10,1) WITH NOWAIT UPDATE STATISTICS ? "
    • 3

  5. Você deve adicionar um índice para seu pedido por campo(s) e verá que a velocidade aumentará. Consulte https://stackoverflow.com/questions/1716798/sql-server-2008-ordering-by-datetime-is-too-slow

    Experimente, não acho que seu palpite, que só vai tornar as coisas mais lentas, esteja certo.

    • 1

relate perguntas