Improve Performance by Replacing Cursors

This is the end...

This is the last article for my upcoming design patterns session. Today we’re going to look into speeding up your queries by replacing cursors, a row-by-row operation with a table value function. Before you start composing that hate-mail on how I’m wrong for not understanding cursors, let me say this: there is a time and a place for everything, even cursors (think running totals). The problem is I still see them doing row-by-row operations in SQL. And SQL is not optimized for row-by-row. It is optimized for set-based operations.

If you can rethink your T-SQL logic and consider the whole set of data you’ll be working on, rather than what you have to do to each row, you’ll find you’re rewarded by the SQL Engine. Your results come back in record time!

Let’s look at an example I created to illustrate a row-by-row operation. This cursor takes in a column containing a user’s full name, and breaks it into first and last name using a space as the break point.

If you want to follow along, and you have my OriginalTroubledTable, use the following code to set up the NameSplitTable.

IF OBJECT_ID('adventureworks.dbo.NameSplitTable') IS NOT NULL
	DROP TABLE NameSplitTable
GO	
CREATE TABLE [dbo].[NameSplitTable](
	  [NameID] INT IDENTITY(1,1)
	, [FullName] VARCHAR(100) NULL
	, [FirstName] [varchar](50) NULL
	, [LastName] [varchar](50) NULL
) ON [PRIMARY]

GO
--I want to have more than 10k rows so repeat inserts until we get more!
WHILE (SELECT COUNT(*) from  [NameSplitTable]) < 10000
BEGIN 
	INSERT INTO [NameSplitTable]
	([FullName])
	SELECT  firstname + ' ' + LastName
	FROM OriginalTroubledTable
END

If we were doing a single row, the following code wouldn’t be that bad.

DECLARE @fullname varchar(100)
DECLARE @NameID INT
DECLARE @splitPos INT

SELECT TOP 1  
	  @NameID = NameID
	, @fullName = FullName
FROM [NameSplitTable]

SET @splitPos = CHARINDEX(' ', @fullname)

IF(@splitPos > 0)
BEGIN
	UPDATE [NameSplitTable] SET
		FirstName = SUBSTRING(@fullName, 1, @splitPos)
		, LastName = SUBSTRING(@fullName, @splitPos, LEN(@fullName))
	WHERE
		NameID = @NameID
END

Look at the execution plan, the cost of the UPDATE isn’t horrible. In my test environment the execution took less than one second.

The real cost of this query comes in when you run this code once for every row of the table. In my test case, I have 11,760 rows. That means that this table scan is going to scan through the table 11,760 times. That will add up quickly! Let’s see the Cursor version of the code.

DECLARE @fullname varchar(100)
DECLARE @NameID INT
DECLARE @splitPos INT

DECLARE FullNames CURSOR FOR
	SELECT NameID, FullName
	FROM [NameSplitTable];

OPEN FullNames;
FETCH NEXT FROM FullNames INTO @NameID, @fullname;

WHILE @@FETCH_STATUS = 0
BEGIN
	SET @splitPos = CHARINDEX(' ', @fullname)
	IF(@splitPos > 0)
	BEGIN
		UPDATE [NameSplitTable] SET
			FirstName = SUBSTRING(@fullName, 1, @splitPos)
			, LastName = SUBSTRING(@fullName, @splitPos, LEN(@fullName))
		WHERE
			NameID = @NameID
END

FETCH NEXT FROM FullNames INTO @NameID, @fullname;
END

CLOSE FullNames
DEALLOCATE FullNames

You can run this on your machine if you have a lot of patience, but I’m going to run it for 60 seconds, and see how many rows I can update in 60 seconds. I was able to update 5,734 rows. And that’s the big problem with cursors, each operation could be very quick, but the cumulative effect of running the code so many times adds up!

In this case it’s the cumulative effect of doing that many table scans and table updates to do the work. How much faster would it be if I could update all the rows at once?

Let’s reset our test table and find out.

update [NameSplitTable] set FirstName = NULL, LastName = NULL

First, in order to be able to do this string manipulation across multiple rows we’re going to need a table value function to do the same substring we did in the cursor.

CREATE FUNCTION tvf_splitNames ( @fullName VARCHAR(100) )
RETURNS @output TABLE (
	[FirstName] [varchar](50) NULL
	, [LastName] [varchar](50) NULL
)
AS
BEGIN
	DECLARE @splitPos INT
	SET @splitPos = CHARINDEX(' ', @fullname)
	IF(@splitPos > 0)
		INSERT INTO @output
		SELECT SUBSTRING(@fullName, 1, @splitPos) FirstName
			, SUBSTRING(@fullName, @splitPos, LEN(@fullName)) AS LastName
	ELSE
		INSERT INTO @output
		SELECT @fullname as FirstName, NULL as LastName

	RETURN 
END

Now that we have a table value function we can use the CROSS APPLY technique to apply this function to all the rows in our NameSplitTable. We simply map in The full name, and pull out the resulting first and last names.

SELECT
	nst.FullName
	, tvf.FirstName
	, tvf.LastName
FROM [NameSplitTable] nst
CROSS APPLY tvf_splitNames (nst.FullName) tvf

Yes, this is only a select, but I wanted to show you how 84% cost to the table scan was changed once we move from row-by-row to Set based processing.

We reduced the cost of the table scan, by adding a table valued function and a nested loop. Yes, this is apples and oranges to the original function, but run the code. We were able to select back 11,760 rows, getting their first and last names in 9 seconds.

The cursor took 2:35 to actually update all the rows.

UPDATE nst SET
	nst.FirstName = tvf.firstName
	, nst.LastName = tvf.LastName
FROM [NameSplitTable] nst
CROSS APPLY tvf_splitNames (nst.FullName) tvf

The cross apply updated it in 1 second.

Now that is apples to apples.

One of the harder things to do in SQL is knowing how well your code is going to scale. If you write code thinking your workload will be a handful of records once a day, and that code is actually run several hundred thousand times per minute, you may not be happy with how quickly the work is performed.

The trick is always learning what SQL does well, and what it doesn’t. Once you understand that, you’ll begin to know what you have to change to speed things up!

That’s it for my design patterns session. As always, if you have any questions, please let me know!

1 Comment on "Improve Performance by Replacing Cursors"


Leave a Reply

Your email address will not be published. Required fields are marked *