select a.col into #table_ex from a 速度快(写得快，方便，维护基础字段时不闭考虑临时表！)
Author Topic **** Starting Member 1 Posts Posted - 12/16/2002 : 9:30:00 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 awhich runs faster? thanks.
Antares686 SQL Server Guru USA 3813 Posts Posted - 12/17/2002 : 04:07: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. Scorpion_66 Valued Contributor USA 227 Posts Posted - 12/17/2002 : 10:49:16 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
mromm Starting Member USA 10 Posts Posted - 12/17/2002 : 10:52:55 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.
除非你在TEMPDB下也建自定义数据类型，但关机后重开又不行了
速度快(写得快，方便，维护基础字段时不闭考虑临时表！)
****
Starting Member 1 Posts Posted - 12/16/2002 : 9:30:00 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 awhich runs faster?
thanks.
Antares686
SQL Server Guru
USA
3813 Posts Posted - 12/17/2002 : 04:07: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.
Scorpion_66
Valued Contributor
USA
227 Posts Posted - 12/17/2002 : 10:49:16 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
mromm
Starting Member
USA
10 Posts Posted - 12/17/2002 : 10:52:55 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.