SQL Server Performance Forum – Threads Archive
delete performance
Hi everyone, I need to delete records from a 20 millon rows Main_table (and growing). The table has an identity column and an index on it. It has 5 other indexes (one clustered). I have a second table with only one column, cluster indexed to hold the ids of the records of the Main_table to be deleted. A sp runs about 500 times nightly and inserts into this to_delete_rows table all the ids of the rows in the Main_table to be removed. Each time the sp runs, it inserts between 1,000 and 25,000 rows, adding up to about 1 millon rows/night. This sp runs with different parameters, 10 instances of them at the same time in different connections. What I’m doing to delete the records in main_table is: Delete Main_tablefrom Main_table M inner join to_delete_rows D on M.record_id = D.record_id I could do it at the end of the whole process or at the end of each sp run (may cause locking delays as the sp are running simultaneously) Any idea of how I can improve performance? I can make any modifications to the tables/sp as necessary, including a deletion flag column in the main table or whatever makes it run faster. Thanks Javier
I am curious as to why you are not just deleting the data directly from the main_table, instead of writing the records to be deleted to another table, and then using a join to determine what needs to be deleted? It seems to me that directly deleting the data would overall be faster. —————————–
Brad M. McGehee, MVP
Webmaster
SQL-Server-Performance.Com
there are good reasons to delete in batches,
if that is what you are doing,
i would check the plan to make sure you have good indexes,
supposedly the transition from row to table lock is either 2,500 rows or 5,000 rows depending on which version you have
The process to select those records involves a heavy query with quite a few joins, logic and filters. Before deleting them I have to do 2 inserts based on those records. And I have to do it about 500 times a night! So what I do is run that heavy query, save the ids of the records in a table, do the 2 inserts with a "light" join with no filters to this table that contains the list, and then I have to delete those records. Everything runs ok except the deletion part, which takes quite a long time. The version I’m running is SQL Server 2000 – 8.00.760 Thanks again, guys
Try reworking the query to Delete M
from to_delete_rows D
inner loop join Main_table M
on M.record_id = D.record_id Cheers
Twan
Check if there are appropriate indexes on columns record_id in both tables. Creating the tables on saperate file groups located on saperate physical disks may also be considered. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
]]>