Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Data Warehouse Update to Live Table Expand / Collapse
Author
Message
Posted Wednesday, October 09, 2013 8:43 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 6:26 AM
Points: 419, Visits: 722
Here is the actual execution plan:



For comparison, the WOStatus DELETE query, which takes significantly less time, looks like this:



The major difference appears to be the sorts on the index delete. Presumably the >= operator is causing this.
Post #1503146
Posted Wednesday, October 09, 2013 10:15 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, April 14, 2014 11:49 AM
Points: 739, Visits: 2,470
Can you try
DELETE FROM fact.WorkOrder WHERE CreateDate >= @startdate OPTION (RECOMPILE)




The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1503196
Posted Wednesday, October 09, 2013 10:29 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 6:26 AM
Points: 419, Visits: 722
That query took 12 seconds. That's a bit longer than I'd like it to take, but within the acceptable range. The execution plan looks like this:



The inner-most clustered index scan has been changed to an index seek.

Since this runs on a pretty tight loop, if there is a way to further reduce the delete time then I'd like to explore it.
Post #1503202
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse