Home » SQL For Programmers » SQL CURSOR Performance – SQL For Programmers

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.

Like this Article? Subscribe to get every article sent to your email.

Related Post

  • SQL CURSOR – SQL For ProgrammersSQL CURSOR – SQL For Programmers One of the things it took me a while to figure out was how to get a stored procedure to loop through data in a table and pull out specific data I needed.There are actually two parts […]
  • Basic SQL Commands – SQL For ProgrammersBasic SQL Commands – SQL For Programmers Before we get into the specifics of the commands that can be used within a stored procedure, I think it would be helpful to review some of the more basic commands that we can use that […]
  • SQL WHILE – SQL For ProgrammersSQL WHILE – SQL For Programmers The IF statement we looked at on Tuesday was pretty tame compared to the WHILE construct.Actually, the main thing you need to keep in mind is that WHILE is all you have.  There is no […]
  • SQL For Programmers – Finding IN a ListSQL For Programmers – Finding IN a List In the last SQL post, we looked at looking for content that was LIKE other content.  While this has its uses, it is limited in its ability to find more than one pattern.  So what if we […]
  • Temporary Tables – SQL For ProgrammersTemporary Tables – SQL For Programmers You may think that you don't need a temporary table.  But if you've ever retrieved data from your database or retrieved data from a table and put it in a list of some sort simply to […]

About Dave Bush

Dave Bush is a Full Stack ASP.NET developer. His commitment to quality through test driven development, vast knowledge of C#, HTML, CSS and JavaScript as well as his ability to mentor younger programmers and his passion for Agile/Scrum as defined by the Agile Manifesto and the Scrum Alliance will certainly be an asset to your organization.

2 Pingbacks/Trackbacks