Table-Valued Functions and Cursors Performance

Another performance issue identified was the use of Cursors when applying Table-Valued Functions to large datasets, whereby the TVF was applied to each individual record using a cursor, e.g. to split delimited strings into lists:


DECLARE @string_table TABLE (
   string VARCHAR(MAX)
);

INSERT INTO @string_table
VALUES
   ('the,first,string,of,several')
   ,('this,is,another,string')
   ,('the,last,string')
   ,(...);

-- Cursor method:

DECLARE @string VARCHAR(MAX);

DECLARE mycursor CURSOR FOR
   SELECT string FROM @string_table;

OPEN mycursor

FETCH NEXT FROM mycursor
   INTO @string

WHILE @@FETCH_STATUS = 0
BEGIN
   SELECT @string, value FROM dbo.comma_delimited_string_to_list(@string)

   FETCH NEXT FROM mycursor
      INTO @string
END

CLOSE mycursor
DEALLOCATE mycursor

The alternative & more performant option is to use CROSS APPLY to apply a TVF to the entire dataset, e.g.:


-- Cross Apply Method:

SELECT b.string, c.value FROM @string_table b
CROSS APPLY dbo.comma_delimited_string_to_list(string) c
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Kevin Kline

Career and Technical Advice for the IT Professional

TroubleshootingSQL

Explaining the bits and bytes of SQL Server and Azure

SQL Authority with Pinal Dave

SQL Server Performance Tuning Expert

Powershellshocked

A blog about PowerShell and general Windows sysadmin stuff

Simon Learning SQL Server

I'm trying to become "better" at SQL Server and data - here's how I'm doing it!

%d bloggers like this: