Início / dba / Perguntas / 8028
Accepted
adopilot
Asked: 2011-11-18 06:35:32 +0800 CST

O que é melhor para grandes alterações em uma tabela: DELETE e INSERT sempre ou UPDATE existente?

  • 772

Estou fazendo um projeto onde preciso alterar em torno de 36K registros em uma tabela diariamente. Estou me perguntando o que terá um desempenho melhor:

  1. excluir linhas e inserir novas, ou
  2. atualizar linhas já existentes

Para mim, é mais fácil excluir todas as linhas e inserir novas, mas se isso fragmentar a tabela e os índices e afetar o desempenho, prefiro fazer atualizações sempre que possível e excluir/inserir somente quando necessário.

Este será um serviço noturno e não pretendo melhorar a velocidade do processo em si. Estou mais preocupado com o desempenho das consultas nessa tabela em geral, onde já tenho 89 milhões de registros e como esse processo noturno afetará isso.

Devo excluir/inserir registros ou atualizar os existentes (quando possível) para este processo noturno?

sql-server sql-server-2005

4 respostas

  1. Best Answer

    Realmente depende de quanto dos dados está mudando. Digamos que esta tabela tenha 20 colunas. E você também tem 5 índices - cada um em um diff. coluna.

    Agora, se os valores em todas as 20 colunas estiverem mudando OU mesmo se os dados em 5 colunas estiverem mudando e essas 5 colunas estiverem todas indexadas, talvez seja melhor "excluir e inserir". Mas se apenas 2 colunas estiverem mudando e digamos que não fazem parte de nenhum índice não clusterizado, talvez seja melhor "Atualizar" os registros porque, neste caso, apenas o índice clusterizado será atualizado (e os índices não terão que ser atualizado).

    Em pesquisas adicionais, descobri que o comentário acima feito por mim é meio redundante, pois o SQL Server internamente possui 2 mecanismos separados para executar uma atualização. - Uma "atualização no local" (ou seja, alterando o valor de uma coluna para um novo na linha original) ou como uma "ATUALIZAÇÃO não local" (DELETE seguido por um INSERT).

    As atualizações no local são a regra e são executadas, se possível. Aqui as linhas ficam exatamente no mesmo local na mesma página na mesma extensão. Apenas os bytes afetados são alterados. O tlog tem apenas um registro (desde que não haja gatilhos de atualização). As atualizações acontecem se um heap estiver sendo atualizado (e houver espaço suficiente na página). As atualizações também acontecem se a chave de clustering for alterada, mas a linha não precisar ser movida.

    Por exemplo: se você tiver um índice agrupado no sobrenome e tiver os nomes: Able, Baker, Charlie Agora você deseja atualizar Baker para Becker. Nenhuma linha precisa ser movida. Então isso pode acontecer no local. Considerando que, se você precisar atualizar Able para Kumar, as linhas terão que ser deslocadas (mesmo que estejam na mesma página). Nesse caso, o SQL Server fará um DELETE seguido de um INSERT.

    Considerando o exposto, sugiro que você faça um UPDATE normal e deixe o SQL Server descobrir a melhor maneira de fazê-lo internamente.

    Para obter mais detalhes sobre "UPDATE" internos ou quaisquer internos relacionados ao SQL Server, confira o livro de Kalen Delaney, Paul Randal e outros - SQL Server 2008 Internals .

    • 14

  2. Você investigou o comando MERGE no SQL 2008? Aqui está um exemplo básico:

      merge YourBigTable ybt
  using (select distinct (RecordID) from YourOtherTable) yot
     on yot.Recordid = YBT.RecordID
  when NOT matched by target
  then  insert (RecordID)
        values (yot.DeviceID) ;

    Este é basicamente um comando "UPSERT". Atualize se existir, insira-o se não existir. MUITO rápido, comando muito legal.

    • 9

  3. Mas, eu mesmo verifiquei o Delete e Insert vs Update em uma tabela que possui 30 milhões (3 crore) de registros. Esta tabela tem uma chave composta exclusiva em cluster e 3 chaves não clusterizadas. Para Excluir e Inserir, demorou 9 minutos. Para atualização levou 55 min. Há apenas uma coluna que foi atualizada em cada linha.

    Então, peço a vocês que não adivinhem. As equações mudarão ao lidar com uma tabela grande com muitas colunas e muitos dados.

    • 6

  4. A atualização não é tão rápida. O truque é conseguir uma inserção rápida é desabilitar os índices enquanto os dados estão sendo inseridos.

    Considere usar isso:

    -- disable indexes
ALTER INDEX [index_name] ON dbo.import_table DISABLE
-- ... disable more indexes

-- don't use delete if you don't care about minimal logging. truncate is faster
TRUNCATE TABLE dbo.import_table

-- just insert the new rows
INSERT dbo.import_table
SELECT
    *
FROM
    dbo.source_table

-- rebuild indexes
ALTER INDEX [index_name] ON dbo.import_table REBUILD
-- ... rebuild more indexes

    Ainda mais rápido é também desativar a atualização automática de estatísticas nas opções de banco de dados. Se a tabela for alterada significativamente, você deve executar:

    UPDATE STATISTICS dbo.import_table

    ou

    EXEC sp_updatestats

    como um trabalho regular (diariamente, semanalmente, dependendo do tamanho do banco de dados) para manter as estatísticas atualizadas. O que deve ser observado é atualizar as estatísticas quando a tabela estiver vazia. Isso estragará as estatísticas se você não executá-lo depois que a tabela for preenchida novamente.

    • 5

relate perguntas