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


Eliminating Cursors


Eliminating Cursors

Author
Message
mmarovic
mmarovic
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 76
I tested the loop solution and it hardly offered any performance improvement over cursor. I would like to have test specification that showed 75% improvement.
John McLaughlin-213204
John McLaughlin-213204
SSC Journeyman
SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)

Group: General Forum Members
Points: 94 Visits: 8
I've used both methods of processing, and from my experience I would say the biggest gain was from inserting the data into a temporary table first. A cursor created from the same set of static data would probably yield the same performance.
André Cardoso
André Cardoso
UDP Broadcaster
UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)

Group: General Forum Members
Points: 1439 Visits: 374

I think the performance gains are obtained when the stored procedure updates/interferes with the result set.

In that case, the performance gains can be significative.





Mike DiRenzo
Mike DiRenzo
SSChasing Mays
SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)

Group: General Forum Members
Points: 611 Visits: 210

I thought SQL Server Central was a place where developers posted original authorship. Clearly if this were original authorship, the author would have done his due diligence and figured out that his procedure code was not working efficiently due in part to TempDB filling up. Moreover, the author would highlighted the margin of diminishing returns with respect to using TempDB versus memory as a means of gaining response times to the caller functions in his alleged plagiarized article. In fact, the author of this article could have made mention as to the source of his findings and perhaps pointed his readers to said source. For example, what title/publisher or what Url, or who the original author was that this author copied, pasted, and called-his-own this age-old technique.


bobjbain
bobjbain
SSC Veteran
SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)

Group: General Forum Members
Points: 254 Visits: 38
Ah yes, because the cursor is non-static it would have to check for updates to its data before the next fetch.

If the sp is updating the data the cursor uses then this would be costly.

I would therefore go beck to using a static cursor (ir it fetches all the data first then iterates through the recordset).

I have been doing a test just iterating through 800K rows of a table, fetching data only...

A static cursor took 8.5 secs to iterate through the whole lot
A noral cursor took 17.5 secs
and the table variable based approach took 30 secs!!!!

This was all running on my machine not the server, I will maybe test same scenario on the server and report back...

Cheers

Bob



bobjbain
bobjbain
SSC Veteran
SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)

Group: General Forum Members
Points: 254 Visits: 38
Valid points all but it doesn't get to the crux of my question in that where do people get the idea that cursors are bad.

I know I am from an Oracle background but in my SQL experience (some 6 years) I have never had a problem with cursors! Memory or otherwise, if somebody could point me to an article that points out the flaws problems with cursors I would be most grateful..

Coming out with statements such as "I don't like cursors so I don't use them" just isn't helpful.....



Mike DiRenzo
Mike DiRenzo
SSChasing Mays
SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)

Group: General Forum Members
Points: 611 Visits: 210

I concur with your statement of "I don't like cursors so I don't use them". It is an empty and unsupported posit. It is my understanding that if your procedure code is not running fast (which by the way is a relative term) due to alleged cursor code, then perhaps the database server’s RDBMS is not getting the horsepower it needs like memory, larger hard-disk, or a stronger processor.

There are cost-benefit trade-offs all over the place. If a developer is cursor-centric in his procedure creations but said procedures do not perform “fast-enough”, what is “fast-enough” ? Sure, a code review by another set of eyes might help point out some glaring in-efficiencies, but if “not fast-enough” means having to rewrite something at a cost that is less than cost of attaining more hardware, then so be it, attempt to reach that margin of diminishing return in terms of speed, rewrite your code.

I liken the analogy to the number of people attempting to enter a very small automobile. If you can only fit five persons but need to fit ten, are you going to cram five more persons or are you going to get a larger automobile?


John Gonyo
John Gonyo
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 4

I was doubtful of the solution posed in this artcle also, so I did my own tests. I got similar results as found by bobjbian. The static cursor beat all comers for larger result sets. As the result set got smaller, the times between the 3 styles got closer. I suppose for very small result sets (<100 rows?) that the temp table might have less overhead than a cursor, but it's only marginal.

No doubt the SP called by the article performed an update to the underlying table causing the cursor to to be updated. So while the table variable may have helped the performance, so too would a STATIC cursor. And the STATIC cursor would have been faster than the table variable.

- J


sheepoo
sheepoo
Old Hand
Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)

Group: General Forum Members
Points: 327 Visits: 34
I see that there is at least one person who has accused me of plagiarism. I can assure you that this is not the case and that this is a piece of code which has been recently implemented by myself in one of our enterprise solutions.

I do, however, agree to the point that I should have been more explicit in describing a few things, especially a little more insight as to why using a WHILE construct gives better results (which it certainly does in my case!) than a CURSOR routine. But then not everyone is good at writing articles and I am just learning the art here .

As for my comment that using WHILE is better than using CURSORS: I understand(as I have noticed as well), and do correct me if I am wrong, that the inherent design of CURSOR implementation within SQLServer causes inconsistent locking and extra IO. This can be avoided using the WHILE technique since all you are doing is re-running the SQL statement one after the other. In my implementation this is all the more important since there is heavy usage of OUTPUT stored procedures which , unfortunately, cannot be used in simple SELECT statments.

Lastly, to answer the question "where do people get the idea that cursors are bad"
I say: experience
Rob Robinson
Rob Robinson
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 20

Actually, this is kind of funny as I've been using this approach for a couple of years and not thought to much of it so as to comment to the world ... Since my background is in software development - namely c, c++, Ruby, Lisp and other syntactically succinct languages, I felt driven to look for a way to get around the clunky syntax of the cursor declaration, as well as the issue that the reference variable appears differently in code than a standard variable in that the @ is not present and the parser uses this "syntactical difference" as a class differentiator. While there is a little more code, it is also more "readable" and therefore leans toward meeting goals of self-documentation.

The "next one" in line with this (and I have not looked to see if there are any postings to this effect already), is using this technique for dynamic iteration of tables using sys information – which has a few different caveats. If there is any desire for this, let me know and I can post example code and ancillary discussion. One benefit to this is constructing queries on the fly where you do not want to have to hard code in order to get the efficiency of using the column names (as opposed to using the * operator, which causes performance degradation in testing). This enables you to create “template” procedures more readily. There are other advantages as well.


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