Compartir a través de

SQL Server 2005: Migrate cursor developed stored procedures

  • Artículo

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

Comments