CURSORS vs #TEMP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

CURSORS vs #TEMP

Author

Topic

derach2000
Starting Member

37 Posts

Posted - 2009-12-11 : 07:51:09

Hi,
I was testing my queries using two methods, one using cursor the second using temp table and while loop.
It turned out that I got 30% increase in query execution speed when using #temp table instead of cursor.
Now, I was wondering, is this the general rule, #tempt better then cursor, or does it depend on the certain conditions.
Kind regards

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-12-11 : 08:25:13

or rewrite the code to not use either a cursor or while loop and get several 100%'s improvement.

Lots and lots of examples here and other places to prove loops are bad (whether they are cursors or while loops for 99% of uses). There have been lots of discussions on this topic already.

Feel free to post the query.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-12-11 : 08:25:50

Well...you'd probably increase performance by 99% or more by removing the row-by-row processing alltogether ang go for a set-based solution. But the reason for the increase in performance is probably because of the overhead of using a cursor.

- Lumbago
http://xkcd.com/327/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-12-11 : 08:31:12

you'd probably see the difference between while loop and cursor dissapear if you declared it with the STATIC keyword. AS Lumbago says, cursor do have an overhead but that doesn't mean the WHILE loop is the best answer.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

derach2000
Starting Member

37 Posts

Posted - 2009-12-11 : 08:57:05

OK, I used the temp table becouse I don't know better so go easy on me, I'v already learned a lot today already, so bare with me.
The purpose of the query to calculate certaine statictics and update the stat table.

create table #Temp
(
row int identity(1,1) ,
[1] char(17) NULL,
[2] datetime NULL,
[3] decimal(9,2) NULL,
[4] decimal(9,2) NULL,
[5] decimal(9,2) NULL,
[6] decimal(9,2) NULL,
[7] decimal(9,2) NULL,

decimal(9,2) NULL,
[9] decimal(9,2) NULL,
[10] decimal(9,2) NULL,
[11] decimal(9,2) NULL,
[12] decimal(9,2) NULL,
[13] decimal(9,2) NULL,
[14] decimal(9,2) NULL,
constraint pktemp PRIMARY KEY (red)

)
insert into #temp
select source AS '1',DateAdd(day, DateDiff(day, 0, TABLE1.date), 0) AS '2',min(col1) AS '3',
max(col1) AS '4',max(col1)-min(col1) AS '5',min(col2) AS '6',max(col2) AS '7',max(col2)-min(col2) AS '8',
max(col3) AS '9',max(col4) AS '10',max(col5) AS '11',min(col6) AS '12',max(col6) AS '13',max(col7) AS '14'
from TABLE1
where col8<>'1' and col1>0
group by source,DateAdd(day, DateDiff(day, 0, TABLE1.date), 0)
order by source,DateAdd(day, DateDiff(day, 0, TABLE1.date), 0)

declare @currid int
select @currid = 1
while @currid is not null
begin
IF (EXISTS (SELECT source
FROM STATTABLE,#Temp
WHERE STATTABLE.source= #Temp.[1] and STATTABLE.date=#Temp.[2] and #Temp.row=@currid))
begin
UPDATE a
SET st1= t.[3],st2=t.[4],st3=t.[5],st4=t.[6],st5=t.[7],st6=t.

,st7=t.[9],st8=t.[10],st9=t.[11],st10=t.[12],st11=t.[13],st12=t.[14]
from STATTABLEa inner join #Temp t on a.source= t.[1] and a.date= t.[2] where t.row=@currid

end
else
begin
INSERT INTO STATTABLE(source, date, st1,st2,st3,st4,st5,st6,st7,st8,st9,st10,st11,st12)
select [1],[2],[3],[4],[5],[6],[7],

,[9],[10],[11],[12],[13],[14] from #Temp where red=@currid
end

select @currid = min(red)
from #Temp
where red > @currid

end
drop table #temp
end
GO

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-12-11 : 11:46:52

Since each #temp row has a unique date (based on the GROUP BY clause on insert) I think you can just do the inserts and updates for all rows at once, no need to do separate operations for each rowid:


--update for existing
update a
from   STATTABLE a
inner join #temp t
       on t.[1] = a.source
       and t.[2] = a.date

--insert where not exists
INSERT INTO STATTABLE(source, date, st1,st2,st3,st4,st5,st6,st7,st8,st9,st10,st11,st12)
select [1],[2],[3],[4],[5],[6],[7],,[9],[10],[11],[12],[13],[14] 
from #Temp 
left outer join STATTABLE a
       on t.[1] = a.source
       and t.[2] = a.date
where  a.source is null

Be One with the Optimizer
TG

derach2000
Starting Member

37 Posts

Posted - 2009-12-15 : 02:41:30

Hi,

Thank you for the code. I really dont know why I dodnt think of that code in the first place. It diddnt give me much of a increase in speed
(on 3,5 million records new query 1'24'' and old query 1'33'' -+1s)
but could prove to a better choice in the future.

Thanks everyone

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources