Benjamin Guinebertière

This blog is about Microsoft Azure. Older stuff include architecture, SOA, BizTalk, ...

SQL Server 2005: Migrate cursor developed stored procedures

SQL Server 2005: Migrate cursor developed stored procedures

  • Comments 2

In a recent mission, customer had many stored procedures using cursors. Those stored procedures contained business code and it was very important to avoid regressions. So I wondered how Visual Basic .NET stored procedures that could keep business logic would behave compared to pure SQL Statements and cursors.

Here are the results of my tests:

Stored procedure style		Execution time

Transact-SQL cursors		1 minute

Visual Basic .NET,
same logic as cursors		7 s

Transact-SQL, SQL style		< 1 s 

I was not surprised to see that SQL style was the fastest, but I also liked the result on VB .NET :-)

 

Here is the simple code I used

Create a table with 1000 rows. By self joining it, I will get a billion rows.

use misc
go
drop table tableA
go
create table tableA
(
 ID int primary key
)
go
declare @i int;
SET @i=1;
WHILE @i <= 1000
BEGIN
 insert into tableA values(@i);
 SET @i=@i+1;
END
go
select count(*) from tableA
go

VB .NET stored procedure code:

Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

Partial Public Class StoredProcedures
    <Microsoft.SqlServer.Server.SqlProcedure()> _
    Public Shared Sub VBCursor()
        Dim total As SqlTypes.SqlDecimal
        total = 0
        Using cn As New SqlConnection("context connection=true")
            Dim cmd As New SqlCommand( _
                "select cast(a.ID * b.ID as decimal) from tableA a, tableA b", _
                cn)
            cn.Open()
            Dim reader As SqlDataReader
            reader = cmd.ExecuteReader()
            While reader.Read()
                total = total + reader.GetDecimal(0)
            End While
            reader.Close()
            cn.Close()
        End Using
        Dim resultRecord As New _
           
SqlDataRecord(New SqlMetaData("result", SqlDbType.Decimal))
        resultRecord.SetDecimal(0, total.Value)
        SqlContext.Pipe.Send(resultRecord)
    End Sub
End Class

Transact-SQL stored procedures code

drop procedure TSQLSet
go
create procedure TSQLSet
as
 select sum(cast(a.ID * b.ID as decimal))
 from tableA a, tableA b
go
drop procedure TSQLCursor;
go

create procedure TSQLCursor
as
 declare @current decimal;
 declare @total decimal;
 set @total=0;
 declare c cursor for 
 select cast(a.ID * b.ID as decimal)
 from tableA a, tableA b;
 open c;
 fetch next from c into @current;
 while @@fetch_status=0
 begin
  set @total = @total + @current;
  fetch next from c into @current;
 end
 close c;
 deallocate c;
 select @total;
go

Test execution code

exec TSQLSet
go
exec TSQLCursor
go
exec VBCursor
go
Leave a Comment
  • Please add 2 and 6 and type the answer here:
  • Post