Início / dba / Perguntas / 78112
Accepted
Gili
Asked: 2014-10-01 21:03:14 +0800 CST

Sobrecarga de decompor consultas complexas em várias consultas simples?

  • 772

Que tipo de sobrecarga devo esperar ao substituir uma única consulta complexa por várias consultas simples?

Meu objetivo é melhorar a legibilidade e a portabilidade de todo o código SQL, portanto, favorecerei construções simples e substituirei extensões específicas de banco de dados por ANSI SQL sempre que possível.

Por exemplo:

  • Imagine que o cliente está invocando SQL dinâmico (em oposição a procedimentos armazenados)
  • Cenário 1: Cliente invoca:INSERT INTO employee SELECT name FROM user
  • Cenário 2: Cliente invoca:
Statement getNames = connection.createStatement();
try (ResultSet rs = getNames.executeQuery("SELECT name FROM user"))
{
  while (rs.next())
  {
    String name = result.getString(1);
    PreparedStatement prepared = connection.prepareStatement("INSERT INTO employee SET name = ?");
    prepared.setString(1, name);
    prepared.executeUpdate();
  }
}

O Cenário 1 não é uma consulta complexa, mas, para fins de argumentação, vamos fingir que é. O Cenário 2 obtém o mesmo resultado usando várias consultas (mais simples). Que tipo de sobrecarga posso esperar do Cenário 2 em relação ao Cenário 1? É algo com que devo me preocupar ou é insignificante?

ATUALIZAÇÃO : https://stackoverflow.com/a/14408631/14731 faz um bom ponto. A decomposição manual de consultas codifica permanentemente um plano de execução em vez de permitir que o otimizador do banco de dados escolha. Dito isto, ainda não está claro se a sobrecarga é significativa.

performance application-design

2 respostas

  1. Best Answer

    Como regra geral, você deve fornecer ao banco de dados o máximo de informações possível sobre a tarefa que está implementando. Como isso se aplica aos seus cenários?

    Cenário 1 ( INSERT .. SELECT)

    O banco de dados sabe que você está prestes a mover em massa todo um conjunto de dados de uma tabela ou de uma tabela derivada para outra. Pode otimizar a execução dada:

    • A cardinalidade da operação em massa
    • As várias restrições / gatilhos que serão invocados
    • A quantidade de armazenamento que precisa ser reservada na nova tabela
    • etc etc.

    Cenário 2 ( SELECT, e o N x INSERT)

    O banco de dados não tem nenhuma pista sobre as várias instruções preparadas que você enviará após o arquivo SELECT. Mesmo que fosse inteligente o suficiente para coletar estatísticas e heurísticas de longo prazo sobre o que virá depois do seu SELECT, seria imprudente presumir qualquer coisa sobre o carregamento subsequente. Então, efetivamente, esse cenário é na maioria das vezes muito pior do que o outro.

    Há algumas observações a serem feitas, no entanto:

    • Mesmo se você tiver um bom motivo de design de aplicativo para particionar INSERTs assim, provavelmente deverá enviá-los em lote para o banco de dados
    • Um bom motivo para particionar INSERTs é o fato de que você pode ter um controle mais refinado sobre os comprimentos das transações. Geralmente não é bom inserir milhões de registros em uma transação de execução longa com o log ativado. Portanto, desligue o log ou confirme após N inserções

    Conclusão

    As observações acima são sobre seus dois cenários específicos. Os cenários do mundo real não são tão simples, mas você também afirmou isso na pergunta. O que estou tentando enfatizar é que, em muitos casos, você deve permitir que o banco de dados execute operações de dados em massa, porque é nisso que ele é muito bom.

    • 3

  2. Para seu exemplo específico, o cenário 1 mantém todos os dados no servidor. O Cenário 2 exigirá que os dados sejam empacotados, enviados pela rede para o cliente, onde devem ser armazenados em buffer (e, talvez, derramados no disco) e, em seguida, removidos do buffer, reempacotados e enviados de volta ao servidor, onde serão finalmente ser processado. Este tempo de rede aumenta. Faça isso com frequência suficiente com conjuntos de linhas grandes o suficiente e você verá o aumento da latência. Faça uma linha de cada vez (como mostra seu exemplo) e você estará arruinando sua decisão pelo resto de sua vida natural. Enviar um lote geralmente é mais rápido do que enviar demonstrativos individualmente.

    O Cenário 1 é uma única instrução com uma transação implícita para manter os dados consistentes. O Cenário 2 exigirá uma transação explícita para atingir a mesma consistência. Esses bloqueios terão que ser mantidos durante a ida e volta ao cliente, o que causará bloqueio.

    Se você dividir uma consulta complexa em declarações mais simples, todas enviadas em lote, haverá ganhos e perdas. Por exemplo, dividir

    select
    <lots of rows>
from <really complex predicate>

    em

    select
    <some columns>
into #T1
from dbo.Table1 as a
where <whatever>;

select
    <other stuff>
from #T1
inner join dbo.Table2 as b
where <etc.>

    Terá o custo óbvio da gravação e leitura de #T1. Pode haver um benefício, no entanto, se você puder indexar #T1 de uma maneira que ajude a segunda consulta, talvez se uma manipulação complexa for realizada nos valores da Tabela1.

    Os procedimentos armazenados podem sofrer de detecção de parâmetro. Às vezes, isso pode ser corrigido recompilando o procedimento a cada execução. As recompilações podem ser caras. Ao dividir uma consulta complexa em várias outras mais simples, você pode colocar a dica de recompilação apenas na(s) instrução(ões) que se beneficia(m) dela.

    Um otimizador não buscará indefinidamente o melhor plano de execução. Eventualmente, ele atingirá o tempo limite e será executado com o melhor disponível naquele momento. O trabalho que o otimizador deve fazer é exponencialmente maior com a complexidade da consulta. Ter consultas mais simples pode permitir que o otimizador encontre o melhor plano para cada consulta individual, proporcionando uma execução geral de menor custo.

    • 1

relate perguntas