while in #temp slower than cursor

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

SQL Server 2000 Forums

SQL Server Development (2000)

while in #temp slower than cursor

Author

Topic

rollershade
Starting Member

1 Post

Posted - 2008-06-11 : 21:38:27

Hi guys

ive got a cursor which takes over an hour to complete and have read on this site that a temp table and the while loop could be quicker so ive created a test before i change live code over. the two code snippets are below, results are from 5 runs on each code and averaged, first result from each test was taken out.

Cursor time : 695.6
While time : 1000.5

So not convinced by temporary table deal, have i written something slightly wrong in the while? as ive not used it before.

thanks
col

CODE :
select getdate()
dECLARE mm_Cursor CURSOR FOR select TOP 10000 date from playerpoints
OPEN mm_Cursor
DECLARE @date NVARCHAR(50)
-- Initial read from the cursor
FETCH NEXT FROM mm_Cursor INTO @date
-- Loop thru the cursor
WHILE @@FETCH_STATUS = 0
BEGIN

dECLARE @date1 DATETIME
set @date1 = @date

-- Read from the cursor again
FETCH NEXT FROM mm_Cursor INTO @date
END
CLOSE mm_Cursor
DEALLOCATE mm_Cursor
select getdate()

select getdate()
CREATE TABLE #TempLoop (
RowID int IDENTITY(1, 1),
[date] NVARCHAR(50)
)
DECLARE @NumberRecords int, @RowCount int
DECLARE @date AS NVARCHAR(50)

-- Insert the resultset we want to loop through
-- into the temporary table
INSERT INTO #TempLoop (date) SELECT top 10000 date from playerpoints

-- Get the number of records in the temporary table
SET @NumberRecords = @@ROWCOUNT
SET @RowCount = 1

-- loop through all records in the temporary table
-- using the WHILE loop construct
WHILE @RowCount <= @NumberRecords
BEGIN
dECLARE @date1 DATETIME
set @date1 = (SELECT date FROM #TempLoop WHERE RowID = @RowCount)
SET @RowCount = @RowCount + 1
END

-- drop the temporary table
DROP TABLE #TempLoop
select getdate()

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-11 : 23:17:32

Why do you need to loop at all? Looping is what is making this so slow, not necessarily the actual cursor or while/#temp.

Please describe in words what needs to happen here.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-06-12 : 07:13:13

I just wrote a blog post on this very topic:

http://weblogs.sqlteam.com/jeffs/archive/2008/06/05/sql-server-cursor-removal.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources