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)
 Cursor speed

Author  Topic 

Rangi
Starting Member

18 Posts

Posted - 2002-12-06 : 10:50:08
SQL Server 2000, which of these is faster and how significant is the difference in speed?

Method one:

INSERT INTO table_variable1 (a) (SELECT a FROM table_variable2)

OR Method two:

DECLARE @a INT
DECLARE cur CURSOR
FOR SELECT a FROM table_variable2
OPEN cur
FETCH cur INTO @a
WHILE (@@fetch_status = 0)
BEGIN
   INSERT INTO table_variable1 (a) VALUES (@a)
   FETCH NEXT FROM cur INTO @a
END
CLOSE cur
DEALLOCATE cur

I would test it myself but I don't have enough data in my table yet to see any significant difference. The table will eventually have a lot of data though, so I would like to use the best way.
It would be convenient if I could use the cursor method cos I would like to be able to access individual cell values, but if it is much slower then I'll do it the other way.

Any help would be much appreciated,

Rangi

angelWPB
Starting Member

2 Posts

Posted - 2002-12-06 : 12:10:23
Only use a cursor as a last resort. Row-based operations have so much overhead, not to mention the coding required. Imagine a barrel of golf balls that you want to move to another barrel. Would you rather pour the first barrel into the second (method A) or move each ball individually (method B)?

A wise guru once told me "Set-based is the true path."

--Angel

- - - - - - - - - - - - - - - - - - - - - - - - -
SELECT * FROM user WHERE clue > 0

0 row(s) selected
Go to Top of Page

Rangi
Starting Member

18 Posts

Posted - 2002-12-06 : 12:13:29
Thanks, that's what I thought... I think I will then follow the advice of your wise guru.

Rangi

Go to Top of Page

lfmn
Posting Yak Master

141 Posts

Posted - 2002-12-06 : 12:18:04
Why would you want to use a cursor?

The first method is much faster.

SQL is useful if you don't know cursors :-)
Go to Top of Page
   

- Advertisement -