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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy