Início / dba / Perguntas / 242187
Accepted
carlo.borreo
Asked: 2019-07-06 06:52:57 +0800 CST

Declarações preparadas com muitos parâmetros são lentas

  • 772

Eu tenho um aplicativo escrito em PHP com Laravel que prepara e executa regularmente instruções como esta:

   -- All parameters are varchar(10)
   SELECT c1, c2, c3, c4
      from MyBigTable
     where is_active = 1
       and c1 in (@P1, @P2, @P3, @P4 ... @P250)
       AND c2 is not NULL

Os usuários têm uma grade de big data e podem selecionar muitas linhas (há até um botão para selecionar 'ALL'). Se eles selecionarem 250 linhas, essa instrução é o que acontece. Mas leva mais de um minuto para ser executado, o que é inaceitável.

Tabela MyBigTable tem cerca de 10 milhões de linhas. O plano de execução estimado mostra que 100% do tempo é gasto em uma "busca de índice, não clusterizada". A partir disso, deduzo que a situação não pode ser melhorada usando índices, e que o único problema está no uso de declarações preparadas. (Se você acha que estou errado, é só me avisar). Além disso, entendo que essas declarações preparadas são preparadas, usadas uma vez e descartadas, então não acho que sejam realmente benéficas.

Que recomendação devo dar aos desenvolvedores?

Devo apenas dizer a eles para parar de usar instruções preparadas e codificar os 250 valores na consulta?

Ou devo dar a eles alguma solução alternativa, como o uso de tabelas temporárias (crie uma tabela temporária, insira 250 valores e faça uma consulta em MyBigTable JOINed com temp)?

Ou alguma outra ideia?

EDIT: plano de execução https://www.brentozar.com/pastetheplan/?id=rJ-b2XalH

sql-server sql-server-2016

3 respostas

  1. 1) As cláusulas IN são, durante a fase de otimização, expandidas para múltiplos OR. Exemplo: C1 = @P1 Ou C1 = @P2.

    2) Se a contagem de parâmetros for maior que 63 o processo de otimização irá construir uma tabela interna para ele. Dito isso, não tenho tanta certeza sobre as estatísticas sendo geradas na tabela temporária gerada dinamicamente.

    3) Colocar seus parâmetros em uma tabela #temp pode resultar em um plano de execução ligeiramente diferente, no entanto, isso também pode apresentar problemas de simultaneidade dependendo do design do seu aplicativo.

    4) Antes de introduzir uma tabela temporária, eu tentaria reescrever a consulta em pedaços lógicos menores. Por exemplo, isole o processo de filtro em relação a uma tabela externa menor otimizada e aplique isso com a tabela interna.

    5) Certifique-se de que suas estatísticas estejam atualizadas e que você esteja recebendo um plano de execução paralelo. Seria bom se você pudesse compartilhar seu plano para que possamos dar uma olhada no que está acontecendo.

    Blockquote O plano de execução estimado mostra que 100% do tempo é gasto em uma "busca de índice, não clusterizada". A partir disso deduzo que a situação não pode ser melhorada usando índices,

    6) "busca de índice" pode ser "ruim" se representar muitas leituras lógicas. Você deve verificar as estatísticas para ter certeza de que o ajuste de índice não produzirá melhores resultados.

    • 4
  2. Best Answer

    Olhando para o plano de consulta (estimado), a única coisa que se destaca como algo que pode causar um problema se as estimativas estiverem incorretas é o predicado residual em c7.

    NOZES

    Você precisaria capturar um plano real para avaliar completamente se a mudança vale a pena, mas pode valer a pena mudar essa coluna de uma coluna incluída para uma coluna-chave.

    • 4

  3. Seria útil ver o plano de consulta real para uma das instâncias maiores e talvez os detalhes extras emitidos quando você o executa com SET STATISTICS IO ON. Suspeito que possa estar varrendo toda a tabela nesse ponto, ou todo esse índice grande de índice, pois vi um comportamento semelhante no passado com grandes INcláusulas estáticas. É aqui que a "varredura de salto de índice", conforme implementada pela Oracle, pode ser útil, mas o SQL Server não oferece suporte a isso. Como você está construindo a instrução SQL preparada no código de qualquer maneira, você pode tentar muitos UNIONs para emular o comportamento:

       SELECT [c2], [c1], [c5], [c4], [c3], [c6]
     FROM [MyBigTable]
    WHERE [c7] = 1
      AND [c3] IN = 'a'
      AND [c4] IS NOT NULL
UNION ALL
   SELECT [c2], [c1], [c5], [c4], [c3], [c6]
     FROM [MyBigTable]
    WHERE [c7] = 1
      AND [c3] IN = 'b'
      AND [c4] IS NOT NULL
UNION ALL
      ...

    (usando ALLcom UNIONpara evitar um tipo distinto desnecessário que pode ser caro)

    Se isso é melhor ou não depende muito do número de linhas que cada um SELECTretorna. No número de linhas...

    e eles podem selecionar muitas linhas (há até um botão para selecionar 'ALL'). Se eles selecionarem 250 linhas, essa instrução é o que acontece. Mas leva mais de um minuto para ser executado, o que é inaceitável.

    Quantas linhas isso implica que estão sendo consideradas? Se selecionar "todos" significa "olhar para todos aqueles 10s de milhões de linhas" (ou "olhar para a maioria deles"), então pode ser simplesmente que isso é rápido, pois seu subsistema de E/S pode fazer o trabalho, mesmo com uma verificação de salto em um índice ideal. E você pode ter um problema de design de aplicativo em vez de um de banco de dados: a opção "all" é realmente de algum uso real para o usuário neste momento?

    • 1

relate perguntas