Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Eliminating Cursors Expand / Collapse
Author
Message
Posted Tuesday, January 17, 2006 4:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 14, 2011 5:55 AM
Points: 16, 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.
Post #251196
Posted Tuesday, January 17, 2006 4:52 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 7, 2014 10:41 AM
Points: 20, 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.
Post #251198
Posted Tuesday, January 17, 2006 5:29 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 29, 2014 8:12 AM
Points: 1,307, Visits: 283

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.




Post #251206
Posted Tuesday, January 17, 2006 6:23 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 25, 2014 8:49 AM
Points: 147, Visits: 196

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.

Post #251222
Posted Tuesday, January 17, 2006 6:25 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 13, 2009 2:07 AM
Points: 26, 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



Post #251223
Posted Tuesday, January 17, 2006 6:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 13, 2009 2:07 AM
Points: 26, 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.....



Post #251231
Posted Tuesday, January 17, 2006 6:48 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 25, 2014 8:49 AM
Points: 147, Visits: 196

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?

 

Post #251238
Posted Tuesday, January 17, 2006 6:51 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, December 19, 2008 7:20 PM
Points: 18, 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

Post #251239
Posted Tuesday, January 17, 2006 6:55 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, August 31, 2013 11:41 PM
Points: 121, 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


Post #251242
Posted Tuesday, January 17, 2006 7:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 9, 2014 11:28 AM
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.

Post #251248
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse