asked
CTE to delete large amounts of data
Hi, I have an archiving exercise to undertake and one of my concerns is the resources it will take to do the deletes from the live table. I have been investigating using cte and dumping a list of id's here and then deleting based on this list. Not having used cte's before, will this be a more efficient way of working with this type of data? This delete will run every day and is in the region of 1 million rows per day. Does this mean that there is only one logged event (delete after cte definition) as opposed to 1 million (delete using where clause)? Kind Regards M
A cte is just a definition over physical tables, and isn't invoked until referenced - so any resource use in deleting data straight from the base tables will be the same whether or not the cte is used. Also using the cte won't turn your query into a bulk-logged query - the individual row deletes will still be logged. You may have to experiment and see how long it takes to delete 1 million rows, then break up the query to chunk it depending on your results, using say `DELETE TOP n`, to give other transactions a chance to access the table between `DELETE`s, and keep the transaction log size down (if in FULL recovery). The idea you have is good, but I'd use a temporary table to build a list of IDs for deletion, then repeat the 'chunk' until the list is processed. If possible it may be worth scheduling the delete for a time when the database is least used.
