SQL CURSOR Performance – SQL For Programmers
Last week I showed how to use the SQL CURSOR to loop through records in a database. In that article I mentioned that you want to avoid using the CURSOR if you can because it has performance problems.
The article was posted on DZone where a self-proclaimed SQL guru picked it up and left a comment basically stating an alternate way of looping through records that would do the same thing but didn’t require the CURSOR. What he suggested was the common practice of doing something like this:
SELECT TOP 1 @field1=Field1, @field2=field2, @key=keyfield(s) FROM TABLE ORDER BY keyfield(s) WHILE @key /*appropriate logic to verify there is a record */ BEGIN /* processing here */ SET @lastKey=@key SELECT TOP 1 @field1=Field1, @field2=field2, @Key=keyfield(s) FROM TABLE WHERE @lastKey < keyfield(s) END
Note: this is a rough outline of the code. The key point is that you are doing a select for the next record up in order from the one you just retrieved and you are doing it with nothing but select statements.
Also, keep in mind that keyfield(s) could be one, or multiple fields and that the field(s) should be unique. Otherwise this won’t work.
If you need to sort in an order other than the primary key, just make keyfield(s) a composite key with the sort order fields first and the primary key last. Ie assuming all of the following fields are strings, keyfield(s) = field1 + field2 + primaryKeyField.
Instead of doing TOP 1, you could also do a select for the MINimum value of the remaining keys.
And it works for most cases.
However, in researching this post, I also ran across this article:
http://www.sqlteam.com/article/cursor-performance
The gist of the article is that while everyone knows you aren’t supposed to use CURSORS because they are so slow, you need to TEST your solution to make sure that in this particular case it really is faster.
But like I stated when I started this series, my job is to get you beyond SELECT, INSERT, UPDATE, and DELETE statements so that you can do more on the server side, not to turn you all into DBAs who know where all of these performance gotchas are.
Other post in SQL For Programmers
- SQL For Developers - 9 Reasons to bother - August 13th, 2008
- MSSQL CREATE and DROP Database - SQL for Programmers - August 20th, 2008
- MSSQL CREATE TABLE - SQL For Programmers - August 22nd, 2008
- SQL For Programmers - ALTERing the TABLE - September 1st, 2008
- SQL For Programmers - Finding a String - September 3rd, 2008
- SQL For Programmers - Finding IN a List - September 8th, 2008
- SQL For Programmers - Stored Procedures (Better than LINQ) - September 10th, 2008
- SQL For Programmers - Stored Procedure Basics - September 16th, 2008
- Basic SQL Commands - SQL For Programmers - September 18th, 2008
- SQL IF/WHILE Blocks - SQL For Programmers - September 30th, 2008
- SQL WHILE - SQL For Programmers - October 2nd, 2008
- Temporary Tables - SQL For Programmers - October 7th, 2008
- SQL CURSOR - SQL For Programmers - October 13th, 2008
- SQL CURSOR Performance - SQL For Programmers - October 22nd, 2008
- Random in SQL - SQL For Programmers - November 4th, 2008
- SQL - Filtering WHERE condition on two rows - November 26th, 2008
- SQL - Transactions - April 15th, 2009
- SQL For Programmers – New Question - July 6th, 2009
- SQL SELECT CASE Instead of IIF - October 20th, 2009
Other Related Items:
AC Adapter Laptop Charger Power Cord Plug for Toshiba Satellite A100 A105 (for select models only / doesn't fit A105-S4XXX Series) A135 A200 A205 A210 A215 A 100 A 105 A 135 A 200 A 205 A 210 A 215 M35 2430 2435 L100 L 100; P/N PA3468U-1ACAPWR+ ac adapters are made with the highest quality materials. Laptop safeguard features against incorrect voltage, short circuit, internal overheating... Read More >
Fenix LD 20 6 Level High Performance Cree LED Flashlight, Black, 6- Inch, Maximum 180 LumensATTRIBUTES No Bulb: LED Burn Time: 2/5/13/71 Hours Carry System: Lanyard Holster Finish: Olive Green Material: T6 Aircraft Grade Aluminum ... Read More >
How Would You Move Mount Fuji?: Microsoft's Cult of the Puzzle -- How the World's Smartest Companies Select the Most Creative ThinkersMicrosoft's notoriously grueling interview process has been emulated by companies everywhere that seek to separate the most creative thinkers from the... Read More >
If you're new here, you may want to subscribe to my RSS feed. Thanks for visiting!










[...] SQL CURSOR Performance – SQL for Programmers (Dave M. Bush) [...]
[...] Dave Bush on SQL CURSOR Performance – SQL For Programmers [...]