Click here to monitor SSC
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
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 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 Veteran
SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)

Group: General Forum Members
Points: 230 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
SSC Veteran
SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)

Group: General Forum Members
Points: 293 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
SSChasing Mays
SSChasing Mays (613 reputation)SSChasing Mays (613 reputation)SSChasing Mays (613 reputation)SSChasing Mays (613 reputation)SSChasing Mays (613 reputation)SSChasing Mays (613 reputation)SSChasing Mays (613 reputation)SSChasing Mays (613 reputation)

Group: General Forum Members
Points: 613 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
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 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-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20943 Visits: 9671
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
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1353 Visits: 1168

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
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1353 Visits: 1168

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
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3480 Visits: 593

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
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 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