SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Replacing Cursors and While Loops


Replacing Cursors and While Loops

Author
Message
Clinton Herring
Clinton Herring
Mr or Mrs. 500
Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)

Group: General Forum Members
Points: 530 Visits: 12
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/cherring/replacingcursorsandwhileloops.asp



Jeswanth Jaishanker
Jeswanth Jaishanker
SSC Eights!
SSC Eights! (822 reputation)SSC Eights! (822 reputation)SSC Eights! (822 reputation)SSC Eights! (822 reputation)SSC Eights! (822 reputation)SSC Eights! (822 reputation)SSC Eights! (822 reputation)SSC Eights! (822 reputation)

Group: General Forum Members
Points: 822 Visits: 172

Try this code. you might save remaining 12 Minutes also..

UPDATE
B
SET
b.id = A.id
FROM
dbo.many_tbl A inner join dbo.OutpUt_tbl B
ON A.id = B.ID

Jeswanth



--------------------------------
Stephen Bailey
Stephen Bailey
Old Hand
Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)

Group: General Forum Members
Points: 345 Visits: 34

I can't see the purpose of the ORDER BY clause. If you were to remove this from your select statement, you may find that you improve the performance. It may also be the case that the outer loop has an ORDER BY clause that can also be removed.

Profile the procedure to determine what indexes are being used on the tables. Ensure that the indexes in use are the most efficient. You may be able to improve the performance yet again by creating covering indexes.



Stephen Bailey
rschaeferhig
rschaeferhig
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3285 Visits: 119

If you don't believe this example take a look at the example I posted which Noeld and Remi helped me convert:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=197948

This went from 2 hours to 6:46 to 0:26 in the final iteration!!





I reject your reality and substitute one of my own. - Adam Savage-Mythbuster
Anubis-237029
Anubis-237029
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 2

Recently I have had to create an admin task that needs to be run on our 24gb database each day. Unfortunately due to our company security I cannot show the DDL but will do my best to explain...

In this task (50000 lines of TSQL) I have had to use several cursors to complete the task. This task is broken down into several smaller or sub tasks to perform the entire operation.

Though I admit I have not had the chance to fully analyze the task as yet to identify scans and the like (which I will get to shortly) however I wish to try and rid myself of the cursors first.

In one 'sub' task I managed to do this by converting a cursor into a simple insert statement:

Insert Into dbo.tbl
Select col1, col2, col3
From dbo.workingtbl

This managed to cut time down from 11 minutes to 3. Working with approx 500,000 rows in the sql above.

The remaining cursors are becoming a little hard to work our. In the cursor I open approximately 20 variables which are used throughout the loop..

Declare #x cursor for
select col1, col2... col20
from dbo.table
order by col1

fetch next from #x into @var1, @var2... @var20

while..... (you all know the rest)


In the while loop above I perform close to 100 calculations, 5 to 10 updates and inserts etc....

With this many calculations and updates/inserts could anyone suggest a better method of this loop without using a cursor? Or perhaps this is one of those isolated cases where a cursor is best?

Either way, any suggestions would be great!


Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)

Group: General Forum Members
Points: 117176 Visits: 9672
It would be best if you started a new thread for this question. You will get more attention to it and you'll be able to provide more details.

Could you provide the table definition (even if you change the names). Some sample data and the expected outputs. I'm thinking at the moment that you could do a first select statement that would do the 100 calculations and insert that data in a temp table. Then you could do your updates/inserts using that temp table. It should run 10/50 times faster that way.
Mike C
Mike C
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10606 Visits: 1173

Great article. A lot of people make the mistake of trying to use SQL as a procedural language instead of a declarative language. It's hard for some to think in terms of SQL set-based statements as opposed to iterative looping and cursors.

I'd estimate that cursors could be eliminated and replaced with set-based SQL statements 99.9999% of the time that they are used.


Mike C
Mike C
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10606 Visits: 1173

In addition to what was already stated about creating a temp table to hold intermediate results, if your calculations are conditional using IF..ELSE constructs, you might look at converting them to CASE expressions and/or modifying your WHERE clauses.

Without seeing your DDL it's impossible to get much more detailed than that.


Yelena Varshal
Yelena Varshal
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14694 Visits: 607

Hi,

Good Article!

In my opinion cursor usage should depend on "How Often". Cursors provide an easy programming solution when the extensive row processing should be done. If something should be done only a couple of times like in one time data migration tasks, I ususally opt for cursors. If something has to run every day and takes a lot of time than the set approach should be researched and maybe some database architecture changes including de-normalizing the database should be done.

Yelena




Regards,
Yelena Varshal

Clinton Herring
Clinton Herring
Mr or Mrs. 500
Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)

Group: General Forum Members
Points: 530 Visits: 12
Stephen,

In my actual code the data needed to be order in the pivoted result set. If one doesn't care about the order then your correct, the order by is not needed.

Clint



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search