SQL Server otimizando consultas de atualização para grandes Volumes de dados
Atualizar tabelas muito grandes pode ser uma tarefa demorada e às vezes pode levar horas para terminar. Além disso, também pode causar problemas de bloqueio.Aqui estão algumas dicas para o SQL Server otimizar as atualizações em grandes volumes de dados.
- removendo o índice na coluna a ser atualizada.
- executando a atualização em lotes menores.
- desativando os gatilhos de exclusão.
- substituindo a instrução Update por uma operação de inserção em massa.
com isso dito, vamos aplicar os pontos acima para otimizar uma consulta de atualização.
o código abaixo cria uma tabela fictícia com 200.000 linhas e índices necessários.
CREATE TABLE tblverylargetable ( sno INT IDENTITY, col1 CHAR(800), col2 CHAR(800), col3 CHAR(800) ) GO DECLARE @i INT=0 WHILE( @i < 200000 ) BEGIN INSERT INTO tblverylargetable VALUES ('Dummy', Replicate('Dummy', 160), Replicate('Dummy', 160)) SET @i=@i + 1 ENDGOCREATE INDEX ix_col1 ON tblverylargetable(col1) GO CREATE INDEX ix_col2_col3 ON tblverylargetable(col2) INCLUDE(col3)
considere a seguinte consulta de atualização que deve ser otimizada. É uma consulta muito direta para atualizar uma única coluna.
UPDATE tblverylargetable SET col1 = 'D'WHERE col1 = 'Dummy'
a consulta leva 2: 19 minutos para ser executada.
vejamos o plano de execução da consulta mostrada abaixo. Além da atualização do índice agrupado, o índice ix_col1 também é atualizado. A operação index update e Sort juntas leva 64% do custo de execução.
1. Removendo o índice na coluna a ser atualizada
a mesma consulta leva de 14 a 18 segundos quando não há nenhum índice no col1. Assim, uma consulta de atualização é executada mais rapidamente se a coluna a ser atualizada não for uma coluna de chave de índice. O índice sempre pode ser criado assim que a atualização for concluída.
2. Executando a atualização em lotes menores
a consulta pode ser otimizada ainda mais executando-a em lotes menores. Isso geralmente é mais rápido. O código abaixo atualiza os registros em lotes de 20000.
DECLARE @i INT=1 WHILE( @i <= 10 ) BEGIN UPDATE TOP(20000) tblverylargetable SET col1 = 'D' WHERE col1 = 'Dummy' SET @i=@i + 1 END
a consulta acima leva de 6 a 8 segundos para ser executada. Ao atualizar em lotes, mesmo que a atualização falhe ou precise ser interrompida, apenas as linhas do lote atual são revertidas.
3. Desativar gatilhos de exclusão
gatilhos com cursores pode diminuir extremamente o desempenho de uma consulta de exclusão. Desativar após excluir gatilhos aumentará consideravelmente o desempenho da consulta.
4. Substituir a instrução Update por uma operação de inserção em massa
uma instrução update é uma operação totalmente registrada e, portanto, certamente levará uma quantidade considerável de tempo se milhões de linhas forem atualizadas.A maneira mais rápida de acelerar a consulta de atualização é substituí-la por uma operação de inserção em massa. É uma operação minimamente registrada no modelo de recuperação simples e registrada em massa. Isso pode ser feito facilmente fazendo uma inserção em massa em uma nova tabela e, em seguida, renomeie a tabela para original. Os índices e restrições necessários podem ser criados em uma nova tabela conforme necessário.
o código abaixo mostra como a atualização pode ser convertida em uma operação de inserção em massa. Leva 4 segundos para ser executado.
SELECT sno, CASE col1 WHEN 'Dummy' THEN 'D' ELSE col1 END AS col1, col2, col3 INTO tblverylargetabletemp FROM tblverylargetable
a inserção em massa pode ser otimizada para obter um aumento de desempenho adicional.
espero que isso ajude!!!
Curta-nos no FaceBook / Siga-nos no Twitter / Junte-se ao grupo SQL Server que mais cresce no FaceBook