Databases
StackOverflow2013
Postsdbo
POHelp with optimizing SQL While loop
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHelp with optimizing SQL While loop
    text
    copied!<p>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.</p> <pre><code> DECLARE @rownumber int DECLARE @power_show BIT DECLARE @AD_show BIT set @rownumber = 0 --FOR EACH ROW CONTAINED IN MY TEMPTABLE WHILE @rownumber &lt; @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 &gt; 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 </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload