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


Eliminating Cursors


Eliminating Cursors

Author
Message
sheepoo
sheepoo
SSC-Addicted
SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)

Group: General Forum Members
Points: 495 Visits: 34
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/kAli/eliminatingcursors.asp
bobjbain
bobjbain
Old Hand
Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)

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



Vladan
Vladan
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10060 Visits: 763

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...





Fabiano Monteiro
Fabiano Monteiro
SSC-Enthusiastic
SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)

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


bobjbain
bobjbain
Old Hand
Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)

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



Japie Botma
Japie Botma
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1255 Visits: 379
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
Consulting DBA / Developer
South Africa
André Cardoso
André Cardoso
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1531 Visits: 374

(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





bobjbain
bobjbain
Old Hand
Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)

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



Kuido Külm
Kuido Külm
Old Hand
Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)

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



Mark Hickin
Mark Hickin
SSC-Addicted
SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)

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


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