Home Forums SQL Server 2012 SQL Server 2012 - T-SQL Conversion script runs for long time unless carved up, not sure why. RE: Conversion script runs for long time unless carved up, not sure why.

  • February 25, 2017 at 3:10 pm

    #1930356

    NicHopper - Friday, February 24, 2017 2:21 AM

    Hi,

    I'll keep this brief. We have a data conversion script written by one of the data team. It basically gets data puts it into temp tables, updates bit and then so on.

    The script is essentially 10+ section run one after the other. In some early tests the script ran in about 5 minutes.
    My question is why if I run the entire script in 1 go does it take 30 minutes plus to run (I stop it after 30 minutes) yet if I run it section by section then it completes in around 5 minutes? Why is the such a difference in running it in sections and 1 go?

    Does anyone have an explanation for this behaviour?

    Thanks,

    Nic

    Assuming that some of the queries in the script rely on the outcome of some of the earlier queries, the answer is likely because you're changing a good bit of data on the fly and the execution plan that was formed for all the updates is no longer based on the conditions at the time you hit the go button.  It may serve you well to add a statement level recompile to many of the queries in the script.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)