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 Monday, January 2, 2006 12:44 PM
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
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/kAli/eliminatingcursors.asp
Post #247716
Posted Tuesday, January 17, 2006 2:56 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
Just a few code corrections (being pedantic), the insert won't work unless you provide the list of columns that you want to insert into....

INSERT INTO @item_table
(item_category_id, order_id)
SELECT -- Same SELECT statement as that for the CURSOR
it.item_category_id
,ord.order_id
FROM dbo.item_categories it

Also, another performance increase would be to set the @loop_counter variable to @@rowcount after you have done the insert into the table variable (depending on how many records are in the table this could increase performance several fold)

--now populate this table with the required item category values
DECLARE @loop_counter INT

INSERT INTO @item_table
(item_category_id, order_id)
SELECT -- Same SELECT statement as that for the CURSOR
it.item_category_id
,ord.order_id
FROM dbo.item_categories it

INNER JOIN dbo.orders ord
ON ord.item_category_id = it.item_category_id

WHERE ord.order_date >= '1-sep-05'
and it.isSuspended != 1

SET @loop_counter = @@ROWCOUNT

DECLARE @item_category_counter INT

Further to this can anyone give me stats on when using this method becomes more efficient than using cursors. i.e. how many records selected in the cursor, machine memory config etc. etc. etc.

I have been using cursors for years (coming from an Oracle background) and have not had problems with them!!

Thanks



Post #251176
Posted Tuesday, January 17, 2006 3:47 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, May 5, 2011 1:38 AM
Points: 1,636, Visits: 604

Although it is best practice to name columns, and especially in such article it would be advisable to do it correctly, it is not true that the insert will not work. The insert will work as it is written by the author.

The setting of @loop_variable is done only once in the entire process, so I doubt the proposed change would increase performance several fold. But, same as Bob, I would be very interested in some closer info about why replacing cursor with a WHILE loop increases performance by 75%. The loop in example generally does the same as the cursor, iterates row after row and for each row starts some procedure... so where is the huge gain? Better use of indexes? Reused query plan?

IMHO the main problem with cursors is, that some people tend to use them there, where it is possible to use a true set-based approach, i.e. update 1000 rows at once instead of updating one after another. I'm not that much sure about the shown example... it is one of the things where I think cursors aren't really too bad. Since I never had to use cursors, I prefer to avoid them altogether - but if you are familiar with them, this could be one of the situations where cursor is OK. I'm really curious what some of the more experienced posters will say...




Post #251183
Posted Tuesday, January 17, 2006 3:50 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 4, 2010 3:13 PM
Points: 13, Visits: 22

Tks for that best article but, i have a question...

Well, i have a lot of procedures that use cursors running in background and the server has low performance all day long.

If we have more than 1.000.000 rows returned in my query, that stil is the best option?

Best regards,

Fabiano

 

Post #251184
Posted Tuesday, January 17, 2006 4:01 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
Sorry, are you saying that cursors are the best option or loading into a table variable and then iterating through it is the best option.....


Post #251189
Posted Tuesday, January 17, 2006 4:03 AM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, September 11, 2014 7:32 AM
Points: 130, Visits: 186
I have used this method for years (because I hate cursors), but does it slightly different. I use a #Temp table and then select the TOP 1 at the beginning of the loop with a delete at the end of the loop. I would recommend this method to anybody in a position that HAS to use a cursor.

5ilverFox
Namakwa Sands
South Africa
Post #251190
Posted Tuesday, January 17, 2006 4:06 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, September 15, 2014 8:37 AM
Points: 1,306, Visits: 273

(The comments are to the author of the article)

Have you tried to convert the cursor to a static cursor?

Because, in my opinion, that's what you are doing with a temporary table (defining the result set "A priori"). I have seen great improvements with this simple modification DECLARE curs_name CURSOR to DECLARE curs_name CURSOR STATIC (assuming you can ignore the modifications to the original result set).

I always try to achieve a set based solution, but if you are only trying to speedup that operation, with the least amount of change, sometimes it pays to play with the cursor options. Of course, with a temporary table, you can more easily migrate to a set based solution.

More problematic (at least, finding an elegant situation) is to use set operations when the tables use an identity column (especially using the identity of one table as the foreign key of another table).

Best regards,
André Cardoso




Post #251191
Posted Tuesday, January 17, 2006 4:09 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
I can't understand, where has this hate of cursors come from. Is it a general DBA thing or something else.

I would have thought that using a cursor/recordset would be much simpler to control than selecting into temp tables/table variables etc.

Is it a performance hangover from SQL 7, I just do not see any performance improvement in 2000.....

Bob



Post #251192
Posted Tuesday, January 17, 2006 4:33 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, October 15, 2012 1:12 AM
Points: 185, Visits: 43

You can also add PRIMARY KEY to the table definititon

DECLARE @item_table TABLE (primary_key INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
                                    item_category_id INT,
                                    order_id INT )

The followings SELECT are better

SELECT @item_category_id = item_category_id ,@order_id = order_id
FROM @item_table
WHERE primary_key = @item_category_counter


 

Post #251194
Posted Tuesday, January 17, 2006 4:36 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, November 24, 2011 2:31 AM
Points: 169, Visits: 156

I think it's worth making the point that this approach does nothing to combat what is fundamentally bad about cursors. All we have done here is created a cursor, without using the declare cursor syntax.

The article states:

<However, this kills the whole theory of set-based operations, which are inherently faster than their row based counterparts. The following is one way of converting your CURSOR statement using the WHILE keyword in such a scenario>

Yes, but this still isn't set based is it? It's still doing everything row by row, which is exactly what we want to avoid.

Much better to take a step back and see if you process many, or all of the purchase orders in a small number of set based sql statements, and try not to take such a procedural approach.

I'd also say that the 75% performance improvement is highly dubious. How can this be? Presumably, the work is being done within the usp_generate_purchase_order procedure. Are we really saying that the looping code was the bottleneck? If this process took 1 min originally, are we saying that by substituting the cursor for the while loop reduced it to 15 secs? I really don't think so.

I'm not saying that this construct should never be used. There are many cases when you might want to slow things down (archiving/purging/data moving for example) to aid concurrency and reduce the size of transactions, but this is not a good pattern to use in high performance database programming.

 

Post #251195
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse