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 9, 2013 8:43 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: Today @ 10:28 AM
Points: 421, Visits: 738
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 9, 2013 10:15 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 6:30 AM
Points: 973, Visits: 3,032
Can you try
DELETE FROM fact.WorkOrder WHERE CreateDate >= @startdate OPTION (RECOMPILE)




The SQL Guy @ blogspot

@SeanPearceSQL

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

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

Group: General Forum Members
Last Login: Today @ 10:28 AM
Points: 421, Visits: 738
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