Updating record through cursor
A recent article on 4Guys From had an ASP script to generate the Transact-SQL code for a cursor.
I've always thought that cursors where slower than set-based SQL but I never knew how much slower.
Each SELECT statement in the WHILE loop had to scan the subset of data since I wasn't selecting indexed primary key values.
However, even coding this to use indexed sequential primary key values I don't think you could overcome the performance deficit.
Read on for the results and a couple of suprises thrown in. Fortunately one of the applications I work with has a 13 million row lookup table that I frequently use for this type of activity. The structure looks like this: There is a unique index on RATE_PFX, RATE_ID, ZIP_AREA and EFF_DT. My tests consisted of looping through a subset of approximately 1,000 rows in the middle of the table.
I do realize this could have been accomplished in a single SELECT statement.
My test in this case was to SELECT all the distinct rates in the table and count and sum them.
My WHILE loop looked like this: I compared this to a cursor that looked almost identical.
This is a scenario where I thought a cursor might be faster. This reduced the processing time for the cursor down to 4,799ms or almost 5 seconds.
That's still almost 20 times shower than that the four updates.
The only way a non-cursor solution could produce these results is through multiple SELECT statements.