Conversion script runs for long time unless carved up, not sure why.

Question

Conversion script runs for long time unless carved up, not sure why.

NicHopper

SSCrazy Eights

Points: 9090
More actions
February 24, 2017 at 2:21 am

#335490

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
Check out my blog http://www.sqlservercentral.com/articles/Best+Practices/61537/

Viewing 5 posts - 1 through 4 (of 4 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Not really enough information here to answer your question, but you might want to compare how long each individual section takes when run separately against when run in one go. You'll need to put some rudimentary logging into your script to achieve this, if it's not already there.

John

NicHopper SSCrazy Eights Points: 9090 More actions · Answer 2

Thanks for the reply. Your right we need a bit more logging in there. As the conversion is important to the business I'll run it sections and then come back to it on another server and see what the timings are.

Thanks.

Nic

Check out my blog http://www.sqlservercentral.com/articles/Best+Practices/61537/

Lowell SSC Guru Points: 323504 More actions · Answer 3

I assume you are running the script in SSMS, right?
I've seen the same behavior in a script that was 46K lines of inserts for a zipcode database.(INSERT...VALUES)
run it in SSMS, and it takes a LONG time, but if i ran it via sqlcmd, it ran in a normal amount of time;
i just attributed it to the SSMS overhead, and potentially running out of memory in SSMS, or at least coming close to it as far as a client applicaiton goes.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 4

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)