Results 1 to 14 of 14
  1. #1
    Join Date
    Sep 2006
    Location
    Czech Republic
    Posts
    82

    Unanswered: Temporary tables or cursors

    Hello,

    I would like to know what do you prefer if you need records from table one by one... Do you prefer temporary tables or cursors? (What construction do you use to get values in case you prefer temporary tables...?) What is by your opinion more effecient and faster ... Many thanks for your hints and suggestions...

    Many thanks for response
    David

  2. #2
    Join Date
    Sep 2006
    Location
    Czech Republic
    Posts
    82
    And another question yet... If somethig crash (insert, update or something) during stored procedure should I deallocate and close cursor or system do it automatic?

    Many Thanks

    David

  3. #3
    Join Date
    Feb 2007
    Posts
    4
    Quote Originally Posted by Musil David
    Hello,

    I would like to know what do you prefer if you need records from table one by one... Do you prefer temporary tables or cursors? (What construction do you use to get values in case you prefer temporary tables...?) What is by your opinion more effecient and faster ... Many thanks for your hints and suggestions...

    Many thanks for response
    David
    hi, i prefer temp tables . that would be faster for accessing, rather than the curosr. of course only a little bit diff with the cursors. But create Index @ runtime for the table, then it would be more feasible

    thanx

  4. #4
    Join Date
    Sep 2006
    Location
    Czech Republic
    Posts
    82
    Thanks for response.... I would like your argument why is going through temporary table more efficient than fetching cursor...

  5. #5
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Set based processing is faster, avoid cursors.
    Why do you think you need a cursor, can you give an example of what you are trying to do?

  6. #6
    Join Date
    Sep 2006
    Location
    Czech Republic
    Posts
    82
    e.g. this values from one table(s) are used for calling of stored proc... I have no idea, how can I do it faster and without cursors...

    declare k_t_table cursor for
    select a, b from t_table(s)

    open k_t_table

    fetch k_t_table into @a, @b


    while @@sqlstatus = 0
    begin

    select @c = c from table c_table

    exec up_x @a, @c

    fetch k_t_table into @a, @b

    end

    close k_t_table
    Last edited by Musil David; 03-19-07 at 09:52.

  7. #7
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Calling a proc to update x_table while selecting one row at a time from another table via a cursor seem very inefficient. (What does up_x do?)

    I will rather change the update to a join
    Something like

    Update x_table
    set x=c.c*t.a
    from x_table x
    ,t_table t
    ,c_table c
    where t.a=c.a
    and c.b=a.b

  8. #8
    Join Date
    Sep 2006
    Location
    Czech Republic
    Posts
    82
    It is not so important what´s in the while cycle for our general discussion I think...

    So, I tried some example to compare tmp tables with cursors... I have big_table with values, I wanna go through this table and get some values according condition (I think most common example)... I created solution with cursor and with temp table... Solution with cursor for 1000 selected rows took 100 ms with tmp table 800 ms... (so eight times longer... inserting and selecting from tmp table takes most of performance)


    Code:
    create proc up_test1
    as
    declare @ln_row int, @ln_rows int, @ln_x decimal(10)
    
    
    select convert(varchar,getdate(),109)
    
    
    create table #t_tmp ( id numeric(9) identity primary key, x decimal(10) )
    
    insert #t_tmp (x) select x from big_table where (some condition same as in up_test2)
    
    select @ln_row = min(id) from #t_tmp
    select @ln_rows = max(id) from #t_tmp
    
    while @ln_row <= @ln_rows
    begin
       select x from #t_tmp where id = @ln_row 
    
       select @ln_row =  @ln_row + 1
    end
    
    select convert(varchar,getdate(),109)


    Code:
    create proc up_test2
    as
    declare @ln_x decimal(10)
    
    
    select convert(varchar,getdate(),109)
    
    
    declare k_big_table cursor for
    select x from big_table where (some condition same as in up_test1)
    
    open k_big_table
    
    fetch k_big_table into @ln_x
    
    while @@sqlstatus = 0
    begin
       select x = @ln_x 
    
     
       fetch k_big_table into @ln_x
    end
    
    close k_big_table
    
    select convert(varchar,getdate(),109)

  9. #9
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Your problem is that you continue to do row by row processing
    When you use a temp table it should be to avoid row by row processing

    e.g. Lets create some dummy data
    Code:
    select name,sales=count(*),rating=0 into t1 
    from sybsystemprocs..syscolumns group by name
    insert into t1 select name+'A',sales,rating from t1
    insert into t1 select name+'B',sales,rating from t1
    insert into t1 select name+'C',sales,rating from t1
    insert into t1 select name+'D',sales,rating from t1
    create unique index ix1 on t1 (name)
    --select count(*) from t1
    exec sp_spaceused t1
    name                 rowtotal    reserved        data            index_size      unused          
    ----                 --------    --------        ----            ----------      ------          
    t1                   13983       768 KB          444 KB          288 KB          36 KB
    Now lets assume we want to update the rating depending on sales using a cursor:
    Code:
    select dt=getdate() into #time 
    go
    declare c1 cursor for
    select sales from t1
    for update of rating
    go
    open c1
    set nocount on
    declare @sales int
    fetch c1 into @sales
    while @@sqlstatus=0 
    begin
      if @sales <10 
        update t1 set rating=1 where current of c1
      else if @sales <51 
        update t1 set rating=2 where current of c1
      else
        update t1 set rating=3 where current of c1
      fetch c1 into @sales
    end
    close c1
    deallocate cursor c1
    go
    select runtime=datediff(ms,dt,getdate()) from #time
    --select rating,count(*) from t1 group by rating
    drop table #time
    set nocount off
    go
    runtime     
    ----------- 
           1380
    Now lets avoid the row by row cursor processeing by using a temp table #t2
    Code:
    select dt=getdate() into #time 
    go
    select 
    low=1,high=9,rating=1 into #t2 union all select
    10,50,2 union all select
    51,999999999,3
    
    update t1 
    set rating=b.rating
    from t1 a, #t2 b
    where a.sales between b.low and b.high
    
    drop table #t2
    go
    
    select runtime=datediff(ms,dt,getdate()) from #time
    --select rating,count(*) from t1 group by rating
    drop table #time
    go
    runtime     
    ----------- 
            216
    Clearly a huge improvement. Now the bets option, lest avoid a cursor and a temp table
    Code:
    select dt=getdate() into #time 
    go
    update t1 
    set rating=case
    when sales<10 then 1
    when sales between 10 and 50 then 2
    else 3
    end
    
    select runtime=datediff(ms,dt,getdate()) from #time
    --select rating,count(*) from t1 group by rating
    drop table #time
    go
    runtime     
    ----------- 
            126

  10. #10
    Join Date
    Sep 2006
    Location
    Czech Republic
    Posts
    82
    Nice inspirational examples... I learned something from them...

    But if we e.g. have to run stored procedure for each value, there is cursor the best solution ( or do you know other possibly how to do?)

    see my previou example :

    Code:
    declare k_t_table cursor for
    select @a, @b from t_table(s)
    open k_t_table
    fetch k_t_table into @a, @b
    while @@sqlstatus = 0
    begin
      exec up_x @a, @c
      fetch k_t_table into @a, @b
    end
    close k_t_table

  11. #11
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Are you sure you can't avoid calling up_x
    What does it do? You haven't answered post #7

  12. #12
    Join Date
    Sep 2006
    Location
    Czech Republic
    Posts
    82
    Why do you think it is important? Moustly this up_xs sends e-mails to clients or write to table that is basis for e.g. printing letters, viewing of data (depts, no payment etc.) ...

  13. #13
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Quote Originally Posted by Musil David
    up_xs sends e-mails
    I don't see another option
    Quote Originally Posted by Musil David
    or write to table that is basis for ...
    In this case rather change the row by row fetch, insert to a insert into select statement.

  14. #14
    Join Date
    May 2013
    Posts
    1
    Quote Originally Posted by Musil David View Post
    Thanks for response.... I would like your argument why is going through temporary table more efficient than fetching cursor...
    i dont think statement is correct.... implicitly cursor will do same work as you will do with creating temp table and then updating some value on a column to identify which row you are in... etc etc......
    I feel that any code written in native and close to hardware will be much faster then the temp table.
    BUT there are always ways to run thing faster.

Posting Permissions

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