Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Curb those Cursors !

With query performance becoming a constant battle, its often a forgotten fact that cursors should be avoided (in the majority of cases).

The SQL optimiser struggles when dealing with cursor ridden statements, when a FETCH command is executed, its moreorless the same as an extra SELECT command being executed.

Sometimes cursors are necessary, especially when executing stored procedures against separate records in a particular table. However, when it comes to using queries to return result sets, most of the time cursors can be avoided entirely. Use a more efficient set-based approach to solve your problem.

Comments

Posted by SQLMob on 18 July 2010

Could you give me an example of where cursors are the only way to go (or the better way forward)?

did not quite understand "especially when executing stored procedures against separate records in a particular table"

thx

Posted by Justin Hostettler-Davies on 19 July 2010

SQLMob - perhaps its just me not being a proficient enough SQL programmer, sometimes it does make sense to use cursors to loop through records and store values in variables or temporary tables and just report the results at the end. But I do realise that some nifty T-SQL can be used - especially with the new features that came with SQL 2005.

So i guess my statement of 'Sometimes cursors are necessary' - should be amended to - 'Sometimes cursors are necessary if you're not a proficient T-SQL programmer' ?!

One specific example I was thinking of is :

A script to determine how much stock is left after each transaction. Selling football shirts , there are 2000 shirts available for sale at the end of the first day. At the end of the second day, 1500 of those shirts have been sold, leaving only 500 shirts. At the end of the third day, 900 shirts have been sold, which means that more shirts needs to be purchased. Because the next result is totally dependent on the preceeding results, it makes sense to try to use a cursor to loop through each records and store values in variables ?

But again thats probably because I dont know enough enhanced T-SQL to cater for this. - I will get swotting up on things like Common-Table expressions though !

Thanks

JHD

Posted by uterque on 19 July 2010

Justin Hostettler-Davies - I believe you could solve that with carry-over-update. See this brilliant article for more information and examples: www.sqlservercentral.com/.../69389

Posted by Justin Hostettler-Davies on 20 July 2010

uterque - excellent article - thanks alot - i'll explore deeper!

Posted by cfradenburg on 21 July 2010

As to, "when executing stored procedures against separate records in a particular table," we have cases in the application I support where we do things like that although it's not against data in an existing table.

We can create medical results in our application, I'll use a Complete Blood Count as an example.  That has multiple items underneath it such as White Cell Count and Red Cell Count.  Everything gets put into a Work In Progress table until the user commits it.  When they commit it a number of things need to be done like moving to the permanent tables, auditing, possibly tasks created, and several checks done for each specific item.  We cursor through each row in the WIP table and called the stored proc on it due to the number of things that need to be done.  There may be other ways to do this, especially with SQL 2008 table valued parameters, but this as worked well for us so far.

Leave a Comment

Please register or log in to leave a comment.