September 30, 2011 at 4:19 am
Hi
I hope someone can give me a direction for investigating a slow package running time. We have created an etl package which select data from one database dump it to another. Usually the package will run in 7 minutes. For the past 4 weeks it tends to run around hour.
I have check all the part of this package and discovered that one of the extract query was very slow to come back.
I ran that query on the source database directly form the server instance, so I could remove any network issue. the query still took 1 hour.
I have found with my colleague that updating the statistics on the four tables used by the query, help sometimes to go back to a mins run but it is not always the case.
we are also redoing indexes every night on these tables.
the query is a select with 2 main union and distinct.
any help will appreciated
P
September 30, 2011 at 4:36 am
Please post your query here for analysis.
September 30, 2011 at 4:37 am
Post your execution plan as well for the query which takes an hour.
September 30, 2011 at 4:45 am
this is the query and the execution plan
September 30, 2011 at 5:02 am
96 % of total cost is taking for sorting.. Do u really want Distinct in the query??
September 30, 2011 at 5:07 am
yes we need to do that.
September 30, 2011 at 5:08 am
unless you have a better way to do it? i.e removing duplicate.
September 30, 2011 at 6:52 am
Split the query into multiple queries to remove the LEFT join. That would do the trick here.
September 30, 2011 at 8:24 am
thanks for that. we will try your solution.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply