次の方法で共有

Increase your SQL Server performance by replacing cursors with set operations

  • [アーティクル]

You have probably heard many times, from different sources, that as a best practice; avoid using TSQL cursors.

During a recent visit to a partner we ran into a common cursor case, which I wanted to use as an example to demonstrate why you should avoid TSQL cursors in most cases, and how to convert cursor logic to simple set join operations. Now there are certain scenarios where using a cursor makes sense. For example, a cursor is ideal for row by row processing that can’t be accomplished by set based operations. A cursor is flexible in that it provides a window, or subset, of data and that allows manipulation of the data in various ways. Study carefully what you want to achieve on case by case basis before using a cursor. Keep in mind SQL Server, as a modern RDBMS system, performs much better with set operations.

Here is simplified version of a real cursor that was used to update a big table with over 200 million rows.

DECLARE @EntityId Varchar(16)

DECLARE @PerfId Varchar(16)

DECLARE @BaseId Varchar(16)

DECLARE @UpdateStatus Int

DECLARE outerCursor CURSOR FOR

SELECT EntityId, BaseId

FROM outerTable

--Returns 204,000 rows

OPEN outerCursor

FETCH NEXT FROM outerCursor INTO @EntityId, @BaseId

WHILE @@FETCH_STATUS = 0

BEGIN

DECLARE innerCursor CURSOR FOR

SELECT PRFMR_ID

FROM innerTable

WHERE ENTY_ID = @BaseId

OPEN innerCursor

FETCH NEXT FROM innerCursor INTO @PerfId

SET @UpdateStatus = @@FETCH_STATUS

WHILE @UpdateStatus = 0

BEGIN

UPDATE 200MilRowTable

SET ENTY_ID = @EntityId

WHERE PRFMR_ID = @PerfId

FETCH NEXT FROM innerCursor INTO @PerfId

SET @UpdateStatus = @@FETCH_STATUS

END

CLOSE innerCursor

DEALLOCATE innerCursor --clean up inner cursor

FETCH NEXT FROM outerCursor INTO @EntityId, @BaseId

END

CLOSE outerCursor

DEALLOCATE outerCursor –cleanup outer cursor

You might notice that this is a nested cursor with 204,000 loops in total for outerCursor. The innerTable has 10 million rows but innerCursor varies in number of loops depending on @BaseId of outerCursor. When I arrived at the customer this cursor had been running for over a day. The developer was “hoping” that it would finish soon given another day or two. The problem was nobody knew for sure how much time this thing would need to complete. Well, we can find out how much progress it has made so far to make an educated guess:

SELECT execution_count, st.text

FROM sys.dm_exec_query_stats as qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st

WHERE st.text like '%FETCH NEXT FROM outerCursor INTO%'

This would tell us how many times the outer cursor has looped already. It returned 107. That’s only around 0.5% (107/204,000) after 30 hours of running. If the trend were to continue, the cursor would need another 8+ months!!!

A rewrite of the batch to take advantage of set operations is as simple as this:

SELECT i.PRFMR_ID, o.EntityId INTO #tempTable

FROM innerTable i join outerTable o on i.ENTY_ID = o.BaseId

Go

UPDATE 200MilRowTable

SET m.ENTY_ID = t.EntityId

FROM 200MilRowTable m join #tempTable t on m.PRFMR_ID = t.PRFMR_ID

Go

--note this is only one of a few ways to rewrite.

In this particular case, “SELECT INTO” is minimally logged under simple recovery mode. The two statement approach makes it easier to understand the conversion logic.

This batch took approximately 17 hours to complete. Between the statement, I also put the database into simple recovery mode and added appropriate indexes to the temp table. I also dropped indexes from 200MilRowTable that touched “ENTY_ID” to speed this up. Adding indexes back took another 7 hours. The total time was approximately 24 hours, which is just a small fraction of the original cursor batch. I need to point out that the non-cursor batch uses more resources since the UPDATE now spawns multiple threads to process parallely. Remember our goal here is to make this finish faster not worrying about how much resources it consumes.

Note: t

his might not be a perfect example because the nested cursor is magnifying the slow performance.

However, the bottom line is; aviod cursors if possible and use joins / set operations whenever you can.

Comments