March 29, 2002 at 8:25 am
I have been reading some posts that cursors should be avoided whenever possible.
I can understand that any single query that replaces a cursor loop most probably will perform better. But I have read (on swynk I believe), that it is even better to use a 'while' loop with a simple query selecting the next record, rather than using a (server side) cursor to loop through the selection.
I was wondering what is your opinion on this.
By the way, before you start asking me for a more concrete example, I do not have any at hand. Just more of a 'theoretical' issue for me.
March 29, 2002 at 8:36 am
I'd like to see a real test done on this issue. Im not a big fan of cursors, but they do have their place - usually more in an admin/maint role for scrolling through dbs, tables, etc, but sometimes they are the right tool. The while loop technique doesn't thrill me - it's barely set based and I would think would offer performance similar to a dynamic cursor since it queries the db once per row.
Andy
March 29, 2002 at 8:45 am
First cursors are important but yes should be avoided with extreme prejudice. But, you may still run into issues which cannot be done in other ways.
Next while loops are usefull they are pretty much equal to cursors and are not always better, depends on situation. The only issue is you have to have something that can be incremented to provide the loop an end point and you must be carefull not to cause yourself and accidental infinite loop. Best choices for whiles are identity fields and unique fields (questions on how to do this let me know because most folks just use numeric fields (int, numeric) but you can use word fields by doing a count of greater than last value with a top 1 to get the first next value to loop). The basic thing that seperates them from a cursor is the fact they use less memory resources since they don't create a recordset in memory the way cursors do. But they are worse in that for you to get the next value to use you will have to go back to the database, thus increasing drive access and adding to possible contention levels on the drives and if is a large set of data may actually perform slower than the cursor since it is not cached into memory for access.
Finally, if you can avoid a cursor and a while loop you will have fewer possible issues with resources, infinite looping and hard drive access being of concern. This should be the goal of your queries, unless you have no other choice.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 29, 2002 at 8:53 am
It really depends on what you need to do. From a performance point of view set-based operations will are generally quicker than cursor stepping.
HOWEVER if your application requires conditional processing based on the values in individual rows then you are better off using a cursor to step through the rows.
For example I have a client who carries out Manufacturing Requirement Planning which requires the aggregation of Sales-demand by week and the netting of of that to available stock by week. If there is a shortfall in a particular week then there is a choice of actions available which depend on the actual sales product. So the main procedure does the matching by month using a cursor for the sales-item/month which in turn provides the parameters to open a cursor for the stock-items/month. The netting off is tested and the procedure branches based on the type of sales item.
Try doing THAT within SQL! Yes I know someone will find a way, but that brings me to my next point which covers the ease of debugging and maintaining the code. In this case the Cursor approach offers a more supportable solution. Incidentally this client runs SQL SERVER 7 on some pretty decrepit NT boxes and still achieves a throught put of planning for 6000 sales items against 50000 stock alternatives over a 6 month planning period in under an hour.
Phew a lengthy answer but I think it boils down to Set-Based if you can, Cursor based if the application would benefit from it
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
March 29, 2002 at 9:28 am
Great information above and some good points. Something else to consider, there are some business processes that are better not performed in SQL. The server is a limited resource and if something is not set based, then there are times when you are better off performing the calculations using some type of client.
In the past I had a financial app that calculated performance over time. T-SQL isn't a great language for math calculations and this required lots of "matching" of rows in the same table, often in a way that doesn't lend itself to a set based operation. We developed a client app that could retrieve xx rows, process them and return the result to SQL. This was a precalculation, similar to what analysis Services does, but it worked in this situation.
I'd say that you have to examine your situation and decide if a larger result set to the client and processing it there is better than contention on your server for a cursor (using memory resources) or a while loop that continually queries the tables. I have a similar problem here and in one of the next TameStrings artices, I will look at something similar.
Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply