Click here to Skip to main content
Click here to Skip to main content

How to Use Cross Apply Instead of Cursors in SQL Server

, 17 Sep 2013 CPOL
Rate this:
Please Sign up or sign in to vote.
How to use cross apply along with a table valued function as an alternative to cursors

Introduction

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 (x, y, 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 (x, y, 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 x,y,z.

Step 1: Create the test table:

create table test1 
(
    id int not null identity(1,1), 
    x int,
    y int,
    z int,
    a int null
)

Step 2: Insert dummy data:

insert into test1 values (5,5,5, NULL)
go 10
-- (I inserted 9132 rows, took five minutes for the code to execute) 

Step 3: Using cursors to update the column "a":

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
open c1
    while (0=0)
        begin
        fetch next from c1 into 
        @x1, @x2,@x3,@x4,@x5
        
        if (@@FETCH_STATUS = -1)
            break
        
        -- your logic
        set @x5 = @x4 + @x2 + @x3
            
        update test1
        set test1.a = @x5
        from test1
        where id = @x1
        end
close c1
deallocate c1

-- Execution time 01:09

Step 4: Reset column "a":

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
(
    q int
)
as 
begin 
    declare @q int 
    set @q = @x + @y +@z    
    insert into @val (q) values (@q)
    return
end
-- tvf can be invoked as shown below
-- select * from dbo.fnsomelogic(1,2,3)

Step 6: Use cross apply and the tvf to update column a:

update test1
set test1.a = c.q
from test1 b cross apply dbo.fnsomelogic(b.x,b.y,b.z) c
--Execution Time: (9132 row(s) affected) in less than a second.   

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Venkata Turlapati
Database Developer
United States United States
No Biography provided

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.141223.1 | Last Updated 17 Sep 2013
Article Copyright 2013 by Venkata Turlapati
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid