Tuesday, 1 December 2009

Improve SQL Server delete performance with views

The SQL Server development customer advisory team met with the MySpace database to team to tackle any pain points with their use of SQL Server. Fortunately one of the key points was an issue I am currently battling with: efficient ordered deletes on large quantities of data. They have identified a potential improvement creating a view and then deleting from within that view rather than deleting directly from the table. As MySpace are dealing with huge volumes of data and it works for them it has to be worth incorporating into any deletion plan.
This approach also provides an excellent way for deleting the top x records from a table.
Essentially:
create view v1 as (select top (10000) * from t1 order by a)
delete from v1
drop view v1

Full details from LuborK here >>

It is also worth considering disabling any indexes related to the table you are deleting from as described here.

No comments: