WHILE LOOP Takes a long time

Question

Post reply

WHILE LOOP Takes a long time

SQLliam

SSC Enthusiast

Points: 173
More actions
July 16, 2020 at 8:53 pm

#3770626

This is a strange one and hope someone can help me understand the problem.
The code in question has been working without issue for about 2 years last week it started taking an extremely long time to finish. It went from about 3 seconds to upward of 6 minutes overnight.
I am going to use generic code here so it is easier to follow.
The code WAS:
While (Select count(thisColumn) where theOtherColumn = 'Whatever') > 1
begin
print 'Hello'
break;
end
So the first thing you are going to say is that it doesn't change anything so it will never finish. The point is there are 0 zero records that meet the criteria, so the loop should run once and exit. Which it does ... after 6 minutes or so.
if you run JUST the select count it returns right away that the count is zero. So if the count IS ZERO why does it take 6 minutes to determine that all of a sudden?
NOW... the next bit is what I sent to the dev team as a solution.
Declare @counts as int = 1
WHILE @counts <>0
begin
select @ counts = Select count(thisColumn) where theOtherColumn = 'Whatever'
Print 'Hello'
break;
end

That actually fixed the long wait. I couldn't explain it to the devs... they were in disbelief until they tried it.
SO... who has the big brains that can explain this to me. I know it MUST have something to do with parameterization.

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

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

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 1

My first step - grab an execution plan. Have a look under the hood at what is happening.

But I don't think those 2 queries are doing the same thing. Having (essentialy):

WHILE 0 > 1
BEGIN
PRINT 'Hello'
END

on my system does NOT print hello. it tells me the commands completed successfully without going through the loop even once.

My completely random guess as to why it started taking a while is your count is bad estimates, blocking, bad/missing indexes, table size growth, high network I/O, high disk I/O, etc. Estimates being my first guess on this as the second query runs faster.

But execution plans will tell you more of the story. If it is estimating 1 row and getting back 1 billion, that's gonna be slow. If the first query uses a table scan and the second is using an index seek, that'll be a performance difference.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

Since you aren't actually using the count for anything other than measuring if the count is greater than "0", just do a check for existence instead of a count... that'll short circuit the table scan and make even the 3 second run run faster.

--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)

SQLliam SSC Enthusiast Points: 173 More actions · Answer 3

I may have over simplified the code.

in the actual code there is a following step that IF the count is higher than 0 it performs a task that will lessen the number of records in the count.

the reason I mentioned that at the time of troubleshooting it was in fact zero. Why does it take minutes longer to evaluate a record count of zero when it is directly checking the number of records as opposed to setting a variable to the zero count and using the variable in the whole loop. Zero is zero.

im not looking for solutions.... I found that by setting my variable and looping on its value

What I’m asking for is insight as to why it has such a significant runtime.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 4

Look at the execution plan. It's possible that your data crossed a threshold that changed the row counts. Maybe it used to do an index seek and now it's doing a scan because the estimated row counts changed. Maybe the statistics are out of date and you're no longer getting an accurate row count, so, scan instead of seek. Maybe it's a parameter sniffing issue and a bad plan got compiled and is being reused so a scan instead of a seek. There are a bunch of maybes. No, for sure, answer without more data. More than likely that data is in the execution plan.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 5

Totally agree with Grant. Insitu code will run differently than dedicated code a whole lot of the time.

--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)