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.
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 INTDECLARE cur CURSORFOR SELECT a FROM table_variable2OPEN curFETCH cur INTO @aWHILE (@@fetch_status = 0)BEGIN INSERT INTO table_variable1 (a) VALUES (@a) FETCH NEXT FROM cur INTO @aENDCLOSE curDEALLOCATE curI 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 > 00 row(s) selected |
|
|
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 |
|
|
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 :-) |
|
|
|
|
|