December 16, 2002 at 9:30 pm
a)
select a.col into #table_ex from a
b)
create table #table_ex (c int)
insert into #table_ex(c) select a.col from a
which runs faster?
thanks.
i work hard to finish my dream.
December 17, 2002 at 4:07 am
Both are about the same. Except two can run a suboptimal plan many times in a Stored Procedure and the first can result in locking issues with the source table. I prefer option 2 but based on your needs compare the execution plan and run each after using DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS then compare execution times. Running them one after the other without clearing the cache can result in slight differences due to able to find data in cache. ALso, you should test under a scenario that will fit in your production environment and look at locks issued.
December 17, 2002 at 10:49 am
I prefer the b choice, to eliminate the locks on system tables placed by the A choice. With the A choice, the locks on the system tables are maintained until the transaction completes after the insert. With B, these locks are released prior to population, so only a fraction of the time.
This has proven to be a major concurrency issue in a few db's I've worked with.
There's another option that may or may not be applicable for you, and that is a table variable. Better performance than a temp table under most situations, but look into them carefully before deciding to go that route. Personally, I like them, until I'm working with temp data larger than I'm comfortable placing in memory.
Edited by - scorpion_66 on 12/17/2002 10:54:27 AM
December 17, 2002 at 10:53 am
To clarify, the SELECT INTO will lead to locking of sysobjects in tempdb until you commit your transaction. If you have many users on the system, or if transactions are long, this may become a bottleneck.
December 18, 2002 at 6:41 pm
Thanks everyone for their responses.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply