Cursor Performance

By Bill Graziano on 17 September 2001 | Tags: Queries

A recent article on 4GuysFromRolla.com 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. Read on for the results and a couple of suprises thrown in.

Methodology

My first step was to generate some test data. Fortunately one of the applications I work with has a 13 million row lookup table that I frequently use for this type of activity. I pulled a million rows out of this table. The structure looks like this:

CREATE TABLE TST_DATA (
	RATE_PFX char (2) NOT NULL ,
	RATE_ID char (7) NOT NULL ,
	ZIP_AREA char (3) NOT NULL ,
	EFF_DT datetime NOT NULL ,
	TERM_DT datetime NOT NULL ,
	RATE money NOT NULL 
)
GO

There is a unique index on RATE_PFX, RATE_ID, ZIP_AREA and EFF_DT. The index is non-clustered. My tests consisted of looping through a subset of approximately 1,000 rows in the middle of the table. Basically I was asking the table for all rates for a given RATE_PFX and RATE_ID. Each test was run six times with the first result being ignored. I used GETDATE() before and after each statement to determine the execution time. I didn't use any performance metric other than execution time to evaluate the results. My test server was an empty dual-Pentium III 450Mhz Xeon with 2GB of RAM and a 50GB RAID array. It is running SQL Server 7.0 SP3 on Windows NT4. The RAID array has an onboard memory cache.

A Single Select

My first test compared a single SELECT statement to a cursor to determine the count of records and the sum of the rates. My statement selected 873 of the records in the table. The single SELECT is shown below.

SELECT 	@SUM_AMT = SUM(RATE),
	@RECS = COUNT(*)
FROM	TST_DATA
WHERE	RATE_ID = @RATE_ID
AND	RATE_PFX = 'H2'

The cursor has a bit more code. It's shown here.

DECLARE C1 CURSOR
READ_ONLY
FOR 
SELECT	RATE
FROM	TST_DATA
WHERE	RATE_ID = @RATE_ID
AND	RATE_PFX = 'H2'

OPEN 	C1

FETCH NEXT FROM C1 INTO @AMT
WHILE (@@fetch_status <> -1)
BEGIN
	IF (@@fetch_status <> -2)
	BEGIN
		SET @SUM_AMT = @SUM_AMT + @AMT
		SET @RECS = @RECS + 1
	END
	FETCH NEXT FROM C1 INTO @AMT
END

CLOSE C1
DEALLOCATE C1

You can download the complete script here. As you can see both the SELECT statement and the cursor sum amounts and count the records. They both returned the same record count and the same total. The SELECT statement executed in an average of 14 millisecond or 14/1000th of a second. The cursor averaged 132 milliseconds (ms). It appears from this test that the cursor was over 9 times slower than a straight SELECT. That's a little slower than I expected but not shocking.

Multiple SELECTs

My next test is a little more complicated. I assumed that a cursor could return the row once and then test the values returned to generate multiple counts. The only way a non-cursor solution could produce these results is through multiple SELECT statements. In many cases you can use a CASE statement for this but I wanted to compare multiple SELECTS to a single cursor.

I added four SELECT statements to my intial count and sum statement. Each of these did a count of the records that matched a certain criteria. None of these four additional queries should use an index beyond what the initial query did. One of them is show below as an example.

SELECT	@C1 = COUNT(*)
FROM	TST_DATA
WHERE	RATE_ID = @RATE_ID
AND	RATE_PFX = 'H2'
AND	RIGHT(ZIP_AREA,1) = '0'

The cursor was also updated to keep track of four individual record counts based on the characteristics of the records returned. The record counts matched. The results here are pretty much what I expected. The five SELECT statements (the first plus my four additional SELECTs) averaged 66 ms per run. That's 4.6 times slower than the single SELECT ran. No suprises there.

The cursor to return all five counts averaged 165 ms or about 25% slower than the cursor to determine one total. Overall for five SELECT statements (five separate record counts) the cursor was 2.5 times slower than the five SELECT statements. Based on my rough math they would be equal at about 12-15 SELECT statements.

Multiple UPDATEs

Next I wanted to run a similar test using UPDATE statements. I converted my four SELECT statements to UPDATE statements. Each UPDATE statement added .01 to the rate of the records that matched. I changed the cursor to run a series of IF statements and then update the record and add the .01 to the rate. The results were pretty much what I expected. The four updates averaged 240ms per run and the cursor averaged 7,189ms per run or over 7 seconds per run. In this test the cursor was 30 times slower!

Of course, that's not the best way to use a cursor. I changed the cursor to read in the rate and other fields and run four IF statements against the values returned and add .01 to the rate if necessary. It then wrote back the record to the table. That meant that the cursor should only update once per row read rather than up to four times. This is a scenario where I thought a cursor might be faster. Boy was I wrong. 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.

Cursor vs. WHILE Loop

I also wanted to compare running a cursor to running a WHILE loop that continually SELECTs the next value to be processed. I write this code quite a bit to avoid writing cursors and I was curious if it was really helping out. 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:

SELECT 	@AMT = MIN(RATE)
FROM	TST_DATA
WHERE	RATE_ID = @RATE_ID
AND	RATE_PFX = 'H2'
AND	RATE > @AMT

WHILE @AMT IS NOT NULL
BEGIN
	PRINT 	CONVERT(VARCHAR, @AMT)

	SELECT 	@AMT = MIN(RATE)
	FROM	TST_DATA
	WHERE	RATE_ID = @RATE_ID
	AND	RATE_PFX = 'H2'
	AND	RATE > @AMT

	SET @SUM_AMT = @SUM_AMT + @AMT
	SET @RECS = @RECS + 1

END

I compared this to a cursor that looked almost identical. The cursor averaged 28ms per run and the WHILE loop averaged 1,493ms per run or over 50 times slower! If you look at the code you'll notice that the script is really written in favor of the cursor. 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. I also think a smaller data set would have closed the performance gap.

Conclusion

In conclusion I'd encourage you really think through situations where you want to use a cursor. In almost all cases they will incur a performance penalty. But there are those rare occassions where they can make things really easy.

Final Note: In my last example I used a WHILE loop to sum up records. I do realize this could have been accomplished in a single SELECT statement. I think almost all my examples could have been simplified. It was difficult to write code simple enough to explain but complicated enough to illustrate the points. The code you see is my compromise.