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.
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."
Cursor speed
Posted - 12/06/2002 : 10:50:08
