Thursday, May 21, 2009

Halving your delete times with large datasets

Here is info from MySpace and the SQL Performance Team on how to delete large ordered blocks of data.

create view v1 as (select top (10000) * from t1 order by a)

and we can delete the “top” rows using simply

delete from v1

The query plan for this delete is much simpler.

Pic2

and the I/O and cpu statistics demonstrate the improvement:

Microsoft SQL Server Development Customer Advisory Team

No comments: