Slow package running time

  • p.mayne

    SSC Rookie

    Points: 27

    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

  • Devendra (Dev) Shirbad

    SSC-Forever

    Points: 42493

    Please post your query here for analysis.

  • Latheesh NK

    SSCertifiable

    Points: 6559

    Post your execution plan as well for the query which takes an hour.

  • p.mayne

    SSC Rookie

    Points: 27

    this is the query and the execution plan

  • Rajesh.KB

    SSC-Addicted

    Points: 403

    96 % of total cost is taking for sorting.. Do u really want Distinct in the query??

  • p.mayne

    SSC Rookie

    Points: 27

    yes we need to do that.

  • p.mayne

    SSC Rookie

    Points: 27

    unless you have a better way to do it? i.e removing duplicate.

  • Latheesh NK

    SSCertifiable

    Points: 6559

    Split the query into multiple queries to remove the LEFT join. That would do the trick here.

  • p.mayne

    SSC Rookie

    Points: 27

    thanks for that. we will try your solution.

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply