SQL Server DELETE é mais lento com índices
Eu tenho um banco de dados SQL Server 2005 e tentei colocar índices nos campos apropriados para acelerar oDELETE
de registros de uma tabela com milhões de linhas (big_table
possui apenas 3 colunas), mas agora oDELETE
tempo de execução é uniformemais longo! (1 hora versus 13 min, por exemplo)
Eu tenho um relacionamento entre as tabelas e a coluna que eu filtroDELETE
por está na outra tabela. Por exemplo
DELETE FROM big_table
WHERE big_table.id_product IN (
SELECT small_table.id_product FROM small_table
WHERE small_table.id_category = 1)
Btw, eu também tentei:
DELETE FROM big_table
WHERE EXISTS
(SELECT 1 FROM small_table
WHERE small_table.id_product = big_table.id_product
AND small_table.id_category = 1)
e, embora pareça correr um pouco mais rápido que o primeiro, ainda é muito mais lento com os índices do que sem.
Criei índices nesses campos:
big_table.id_product
small_table.id_product
small_table.id_category
Meu arquivo .ldf cresce muito durante oDELETE
.
Por que meuDELETE
consultas mais lentas quando tenho índices em minhas tabelas? Eu pensei que eles deveriam correr mais rápido.
ATUALIZAR
Ok, consenso parece ser índices desacelerarão uma enormeDELETE
porque o índice precisa ser atualizado. Embora eu ainda não entenda por que não podeDELETE
todas as linhas de uma só vez e atualize o índice uma vez no final.
Fiquei com a impressão de que algumas das minhas leituras acelerariamDELETE
fazendo pesquisas por campos noWHERE
cláusula mais rápida.
"Os índices funcionam tão bem ao procurar um registro nos comandos DELETE e UPDATE quanto nas instruções SELECT."
Porém, mais adiante neste artigo, diz que muitos índices podem prejudicar o desempenho.
Respostas parabobs questões:
55 milhões de linhas na tabela42 milhões de linhas sendo excluídasSemelhanteSELECT
A instrução não seria executada (exceção do tipo 'System.OutOfMemoryException' foi lançada)Eu tentei as 2 consultas a seguir:
SELECT * FROM big_table
WHERE big_table.id_product IN (
SELECT small_table.id_product FROM small_table
WHERE small_table.id_category = 1)
SELECT * FROM big_table
INNER JOIN small_table
ON small_table.id_product = big_table.id_product
WHERE small_table.id_category = 1
Ambosfalhou depois de correr para25 min com esta mensagem de erro do SQL Server 2005:
An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown.
O servidor de banco de dados é uma máquina Xeon dual core mais antiga com 7,5 GB de RAM. É o meu banco de dados de teste de brinquedos :), portanto, não está executando mais nada.
Preciso fazer algo especial com meus índices depois deCREATE
eles para fazê-los funcionar corretamente?