Help with optimizing SQL While loop

Question

I need help please optimizing the SQL While Loop below. This is calling on those who have experienced in this to please help. Currently, my T-SQL code runs for over 25 minutes and I would like to cut down that time as much as possible. I was able to identify this loop as a major problem area and would appreciate any help to get this done.

 DECLARE @rownumber int

 DECLARE @power_show BIT

 DECLARE  @AD_show BIT 

  set @rownumber = 0 

--FOR EACH ROW CONTAINED IN MY TEMPTABLE

 WHILE @rownumber < @rowcounter

 BEGIN
      set @rownumber = @rownumber + 1

       -- THE VARIABLES 
      DECLARE @record_no as BIGINT
      DECLARE @phone_name VARCHAR(30)
      DECLARE @messagepriority as INTEGER
      DECLARE @phone_number VARCHAR(30)
      DECLARE @phone_id BIGINT
      DECLARE @questionMessage BIGINT

      SELECT   
@phone_name = n.phone_name, @phone_number =n.phone_number, @messagepriority =n.messagepriority, @phone_id=n.phone_id , 
      @AD_show=n.AD_show, @power_show=n.power_show
      FROM 
      #temporary_phonetable n WITH(NOLOCK) 
      WHERE n.rownumber = @rownumber

      --EXECUTE STOREDPROC ADDMESSAGETOQUEUE WHICH RETURNS THE ROWID OF THE NEWLY CREATED ROW, IF ANY
      SELECT @record_no = sp_queryExecute AddMessageToQueue(@phone_number, @responsemessages, @dateresponsessent, @savednames, @userid,                                                                    un.messagepriority, @responsetype,  un.AD_show, un.power_show, @service_provider, @PhoneType)  


      If(@questionid > 0)
      BEGIN 
            -- EXECUTE STOREDPROC ADDQUESTIONMESSAGE WHICH RETURNS THE ROWID OF NEWLY CREATED ROW, IF ANY 
            SET @questionMessage = sp_queryExecute  AddQuestionMessage(@questionid,@phone_id,  @record_no, DATEADD(d, 30, GETDATE()) )
      END 

            -- ADD THE NEW ROWID TO THE TEMP TABLE
            UPDATE #temporary_phonetable SET record_no = @record_no, questionMessage=@questionMessage 
            WHERE phone_number = @phone_number  AND rownumber = @rownumber
      END

How many items are you processing? Are you sure there are not any blocks during your processing? Is it possible to bring the sp code into the query above so we can see if there is a way to do this operation in a set based fashion? — RC_Cleland
– RC_Cleland, Commented Nov 23, 2010 at 2:41

Ramy · Accepted Answer · 2010-11-23 02:03:18Z

2

I don't think the optimization needs to take mplace in your loop. I recommend checking what's happening in the stored procedures. First answer was correct in saying you should move your declares outside of the loop. But I don't believe that'll save significant amounts of time.

Addendum: it's worthwhile to make try and do this withOUT using a cursor and having to loop through.

edited Nov 23, 2010 at 2:03

answered Nov 23, 2010 at 1:19

Ramy

21.3k49 gold badges106 silver badges160 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Joe Over a year ago

agreed- check the SP - i was going to type that and you beat me to it.

Kobojunkie Over a year ago

Before this loop, I use CTES to filter the data up to the point where I then have to perform inserts on two tables for each row, if condition met. If you look, I call two external storedprocs in the lop there. How would I go about achieving this without a loop?

Ramy Over a year ago

@Kobojunkie, i think you might want to start another question about the code from before the while loop. I can't tell from what you're saying whether or not you can avoid using a cursor. BUT as a general SQL rule, you should avoid cursors. That doesn't mean your problem can be solved without a cursor. I just can't, in good conscience, suggest a solution without pointing out that it's always worth trying to avoid cursors.

Joe · Accepted Answer · 2010-11-23 01:12:39Z

0

not knowing t-sql i see 2 things immediately.

Why use a While for a counted loop? Use a for loop (in plsql we have cursor loops specifically for iterating a cursor)
Why are you declaring/initializing your vars inside the loop? Maybe there is a Declare them outside and re0initialize them (if need be) inside the loop.

answered Nov 23, 2010 at 1:12

Joe

3,3471 gold badge17 silver badges11 bronze badges

4 Comments

Andrew Whitaker Over a year ago

There is no for loop construct in SQL Server. Besides, the root of the problem is that a loop is here at all.

Mitch Wheat Over a year ago

"There is no for loop construct in SQL Server" - erm, there's a WHILE loop, but you are correct in saying avoid loops. A set based approach is better

Andrew Whitaker Over a year ago

@Mitch Obviously there's a WHILE loop, it's in the code sample. I was simply addressing the first point of @Joe's answer.

Kobojunkie Over a year ago

I declare/initialize my variables in the loop to ensure I get clean data each round of the loop. I usually try to avoid WHILE LOOPS but in this case, this is what I could come up with but if you have a better idea, I am open to it.

Collectives™ on Stack Overflow

Help with optimizing SQL While loop

2 Answers 2

3 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related