Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2003
    Location
    athens
    Posts
    1

    Angry Unanswered: usage of cursors in stored procedures - examples

    does anyone have examples of the usage of cursors in stored procedures in SQL Server 2000

  2. #2
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    Cursors in SP are used in the same manner as out of SP. These examples use only simple cursors,
    for info about cursors FOR UPDATE,KEYSET,SCROLL see BOL topic "DECLARE CURSOR","Transact-SQL Cursors" and related topics.

    There are very rare situations in SQL, where cursors are the only one choice or they are faster then set operation.
    One good example is concatenation of strings, which cannot be done by aggregate function, because of order sensitivity.

    /*
    Times are tested after restarting SQLSERVICE (almost deterministic and fast - for one dba).
    The first time is for 32000 records, the second for 65000 records.
    */

    Code:
    
    --INIT
    drop table StringTable
    GO
    Create table StringTable("id" int identity(1,1) primary key clustered,GroupId int not null ,String varchar(8000) not null)
    insert StringTable(GroupId,String)
     select checksum(Newid())%10000+1000,left(Newid(),checksum(Newid())%10+9)
     from       ( select  0 as X union all select 1 union all select 2 union all select 3 ) X0  --4
     cross join ( select  0 as X union all select 1 union all select 2 union all select 3 ) X1 --16
     cross join ( select  0 as X union all select 1 union all select 2 union all select 3 ) X2 --64
     cross join ( select  0 as X union all select 1 union all select 2 union all select 3 ) X3 --256
     cross join ( select  0 as X union all select 1 union all select 2 union all select 3 ) X4 --1024
     cross join ( select  0 as X union all select 1 union all select 2 union all select 3 ) X5 --4096
     cross join ( select  0 as X union all select 1 union all select 2 union all select 3 ) X6 --16384
     cross join ( select  0 as X union all select 1                                       ) X7 --32768
    -- cross join ( select  0 as X union all select 1                                       ) X8 --65000
    --select * from StringTable
    GO
    
    --SET SQL, LOOP
    --25s/42s
    set nocount on
    select st.GroupId,st.String,st."id"
     into #ResultTable
     from StringTable st
     join
     (
      select GroupId,"id"=max("id") 
      from StringTable 
      group by GroupId
     ) x on st."id"=x."id"
    while @@rowcount>0 begin
     update rt set 
      rt.String=rt.String+st.String
     ,rt."id"=st."id"
      from #ResultTable rt 
      join StringTable  st on rt.GroupId=st.GroupId
      join
      (
       select st.GroupId,"id"=max(st."id") 
       from StringTable  st
       join #ResultTable rt on st.GroupId=rt.GroupId and st."id"<rt."id"
       group by st.GroupId
      ) x on st."id"=x."id"
    end
    drop table #ResultTable
    set nocount off
    GO
    
    --CURSOR SQL
    --4s/5s
    set nocount on
    select top 0 GroupId,String 
     into #ResultTable
     from StringTable
    declare @c cursor
    set @c=cursor static for 
     select GroupId,String
     from StringTable
     order by GroupId,"id" DESC
    declare @GroupId int
    declare @OldGroupId int
    declare @String varchar(8000)
    declare @ConString varchar(8000)
    open @c
    fetch from @c into @GroupId,@String
    set @OldGroupId=@GroupId
    set @ConString='' 
    if not @@fetch_status=0 RETURN
    while @@fetch_status=0 begin
     if @OldGroupId=@GroupId set @ConString=@ConString+@String
     else begin
      insert #ResultTable(GroupId,String) values (@GroupId,@ConString)
      set @OldGroupId=@GroupId
      set @ConString=@String
     end 
     fetch from @c into @GroupId,@String
    end
    insert #ResultTable(GroupId,String) values (@GroupId,@ConString)
    drop table #ResultTable
    set nocount off
    GO
    


    Cursor solution has equal functionality, more code and runs 6.2x faster.
    That is because of using advanced algorithm in cursor. It is ordering table only once, then it uses row-by-row logic.

    Code:
    
    --ACCELERATED SET SQL, LOOP
    --1s/1s
    set nocount on
    select st.GroupId,st.String,st."id"
     into #ResultTable
     from StringTable st
     join
     (
      select GroupId,"id"=max("id") 
      from StringTable 
      group by GroupId
     ) x on st."id"=x."id"
    create clustered index IC_ResultTable on #ResultTable(GroupId)
    while @@rowcount>0 begin
     update rt set 
      rt.String=rt.String+st.String
     ,rt."id"=st."id"
      from #ResultTable rt 
      join StringTable  st on rt.GroupId=st.GroupId
      join
      (
       select st.GroupId,"id"=max(st."id") 
       from StringTable  st
       join #ResultTable rt on st.GroupId=rt.GroupId and st."id"<rt."id"
       group by st.GroupId
      ) x on st."id"=x."id"
    end
    drop table #ResultTable
    set nocount off
    GO
    


    By adding index IC_ResultTable SQL set query boosts up and is 4x faster than cursor!
    You cannot accelerate cursor browsing by an index.


    You can say, that creating cursor solution was loose of time. But this cursor is usable.
    1. We can look for first high probable occurence of string in concatenated string.
    Set loop would concatenate almost all the table. Cursor loop just a few first records.
    2. We decide to upgrade our fixed reports to programable ones. Programable reports are complicated by themselves.
    Everything must be simple, including concatenation of strings. How could I use function for every row
    quering table 15 times? Cursor would do it just once.

    Code:
    
    create function dbo.ufn_ConcatenateCursor(@GroupId int) returns varchar(8000) as begin
     declare @ConString varchar(8000)
     declare @String varchar(8000)
     declare @c cursor
     set @c=cursor fast_forward read_only for 
            select String 
            from StringTable
            where GroupId=@GroupId
            order by "id" DESC
     open @c
     fetch from @c into @String
     if not @@fetch_status=0 RETURN @ConString
     set @ConString=@String
     while @@fetch_status=0 begin
      set @ConString=@ConString+@String
      fetch from @c into @String
     end
     RETURN @ConString
    end
    GO
    
    create function dbo.ufn_ConcatenateSet(@GroupId int) returns varchar(8000) as begin
     declare @ConString varchar(8000)
     declare @String varchar(8000)
     declare @Id int
     declare @OldId int
     select @Id=max(st."id"),@String=max(st.String)
      from StringTable st
      join (select "id"=max("id") 
            from StringTable 
            where GroupId=@GroupId
           ) x on st."id"=x."id"
     if @Id is null RETURN @ConString
     set @ConString=''
     set @OldId=@Id
     while (@Id=@OldId) begin
      set @ConString=@ConString+@String
      set @OldId=@Id
      select @Id=max(x."id"),@String=max(st.String)
       from StringTable st
       join (select "id"=max("id")
             from StringTable 
             where GroupId=@GroupId and "id"<@OldId
            ) x on st."id"=x."id"
     end
     RETURN @ConString
    end
    GO
    
    --4s/380s
    select dbo.ufn_ConcatenateCursor(GroupId)
    from (select distinct GroupId from StringTable) X
    
    --3s/203s
    select dbo.ufn_ConcatenateSet(GroupId)
    from (select distinct GroupId from StringTable) X
    



    And the winner is - SET QUERY (really unexpected) Can anyone write this cursor better ?
    So cursors are worse scalable, I used too large resultset ...



    GOD GIVE ME STRENGTH ! THERE MUST BE SOMETHING FOR CURSORS !



    So I remember already. Loop for dynamic execution of single queries.


    Final example - Dropping user tables

    Code:
    
    declare @name sysname
    declare @schema sysname
    declare @c cursor 
    set nocount on
    set @c=cursor static for
     select table_schema,table_name 
     from information_schema.tables 
     where table_schema<>'dbo'
    open @c
    fetch from @c into @schema,@name
    while @@fetch_status=0 begin
     select @schema=QUOTENAME(@schema),@name=QUOTENAME(@name)
     exec('drop table '+@schema+'.'+@name)
     fetch from @c into @schema,@name
    end
    close @c
    deallocate @c
    set nocount off
    

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •