I recently ran into a stored procedure with cursor logic, I wanted to see what would be the performance gain if cursor logic is rewritten with set theory operations.
Here is a simplified description of the stored procedure. There is a table with five columns, one column is an identity and a primary, (let's call it the ID), three columns (
z) are of type integers and the fifth column (
a) is a computed column. The computation is quite complex so it cannot be declared as a computed column expression. For each row, the values of the three columns (
z) are passed in as parameters to a custom function (where the logic is encapsulated) which spits out a calculated value. Finally, for every row, the column "
a" is updated with the calculated value from the custom function. There are around 7000 rows in this table.
Using the Code
Let's create a table called
test1 with five columns. For this example, let's stick with a simple logic, the fifth column is a sum of cols
Step 1: Create the test table:
create table test1
id int not null identity(1,1),
a int null
Step 2: Insert dummy data:
insert into test1 values (5,5,5, NULL)
Step 3: Using cursors to update the column "
declare @x1 int
declare @x2 int
declare @x3 int
declare @x4 int
declare @x5 int
declare c1 cursor local for
select id, x, y, z, a from test1
fetch next from c1 into
if (@@FETCH_STATUS = -1)
set @x5 = @x4 + @x2 + @x3
set test1.a = @x5
where id = @x1
Step 4: Reset column "
update test1 set a = null
Step 5: Create a table valued function (
tvf) shown below:
create function dbo.fnsomelogic (@x int ,@y int, @z int)
returns @val table
declare @q int
set @q = @x + @y +@z
insert into @val (q) values (@q)
Step 6: Use cross apply and the
tvf to update column
set test1.a = c.q
from test1 b cross apply dbo.fnsomelogic(b.x,b.y,b.z) c
Points of Interest
If you observe the messages, the cursor which is a row based operator, displays (1 row(s) affected) for every row it updated, unlike the cross apply which displays (9132 row(s) affected). Although the problem is screaming out use cursors, however with little observation a cross apply along with a table valued function can boost the performance significantly. Relational/Set theory concepts are deeply embedded within SQL Server.