SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Cursor speed
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Rangi
Starting Member

United Kingdom
18 Posts

Posted - 12/06/2002 :  10:50:08  Show Profile  Visit Rangi's Homepage  Reply with Quote
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

USA
2 Posts

Posted - 12/06/2002 :  12:10:23  Show Profile  Reply with Quote
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

United Kingdom
18 Posts

Posted - 12/06/2002 :  12:13:29  Show Profile  Visit Rangi's Homepage  Reply with Quote
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

USA
141 Posts

Posted - 12/06/2002 :  12:18:04  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.44 seconds. Powered By: Snitz Forums 2000