区别一:
区别二:
任何含有临时表的存储过程是不能被预编译的.这在一个很长的存储过程中,优势会更加凸显.
区别三:
table变量作为变量只能在同范围内存在,不可能跨范围.还有就是table变量在内置的存储过程中或者exec(string)语句中是不可见的还有就是不能被用于INSERT/EXEC statements.
下面的例子我来演示如何对比临时表和table变量在存储过程中预编译的区别.
创建一个测试表:
然后创建存储过程:T1
为精确测量存储过程执行时间,我使用了以下代码:
现在我们给这个存储过程来第一次提速:个它加个主键
然后再来一次提速:给它加个聚集索引
很惊讶速度已经提高了很多!
那好了,咱们再来测试一下table变量的速度吧
然后再来创建一个有主键的:
然后咱们来看看我测试的结果吧!
发现对比在某些情况下sql2000的速度要比2005的要快上很多!
结论:
没有通用的规则指导你什么时候用临时表什么时候用table变量.
将复杂逻辑的存储过程移植到sql2005的时候,要格外小心!他可能比2000的效率要慢上好几十倍的!
在你的实际测试中,请测试两个极端:销量数据和超大量数据.
<
PRE lang
=
sql
>
CREATE
table
#T (s
varchar
(
128
))
DECLARE @T table (s varchar ( 128 ))
INSERT into #T select ' old value # '
INSERT into @T select ' old value @ '
BEGIN transaction
UPDATE #T set s = ' new value # '
UPDATE @T set s = ' new value @ '
ROLLBACK transaction
SELECT * from #T
SELECT * from @T
s
-- -------------
old value #
s
-- -------------
new value @ </ PRE >
这行代码一目了然,临时表能起到事务回滚的作用,但是table变量不可以.作者解释是table变量不在事务作用范围之内.所以当table变量即使遇到回滚命令,但是也不会真的执行回滚.
DECLARE @T table (s varchar ( 128 ))
INSERT into #T select ' old value # '
INSERT into @T select ' old value @ '
BEGIN transaction
UPDATE #T set s = ' new value # '
UPDATE @T set s = ' new value @ '
ROLLBACK transaction
SELECT * from #T
SELECT * from @T
s
-- -------------
old value #
s
-- -------------
new value @ </ PRE >
区别二:
任何含有临时表的存储过程是不能被预编译的.这在一个很长的存储过程中,优势会更加凸显.
区别三:
table变量作为变量只能在同范围内存在,不可能跨范围.还有就是table变量在内置的存储过程中或者exec(string)语句中是不可见的还有就是不能被用于INSERT/EXEC statements.
下面的例子我来演示如何对比临时表和table变量在存储过程中预编译的区别.
创建一个测试表:
CREATE
table
NUM (n
int
primary
key
, s
varchar
(
128
))
GO
SET nocount on
DECLARE @n int
SET @n = 1000000
WHILE @n > 0 begin
INSERT into NUM
SELECT @n , ' Value: ' + convert ( varchar , @n )
SET @n = @n - 1
END
GO
GO
SET nocount on
DECLARE @n int
SET @n = 1000000
WHILE @n > 0 begin
INSERT into NUM
SELECT @n , ' Value: ' + convert ( varchar , @n )
SET @n = @n - 1
END
GO
然后创建存储过程:T1
CREATE
procedure
T1
@total int
AS
CREATE table #T (n int , s varchar ( 128 ))
INSERT into #T select n,s from NUM
WHERE n % 100 > 0 and n <= @total
DECLARE @res varchar ( 128 )
SELECT @res = max (s) from NUM
WHERE n <= @total and
NOT exists ( select * from #T
WHERE #T.n = NUM.n)
GO
这个存储过程的参数@Total给定的越大,那么执行的时间越长.
@total int
AS
CREATE table #T (n int , s varchar ( 128 ))
INSERT into #T select n,s from NUM
WHERE n % 100 > 0 and n <= @total
DECLARE @res varchar ( 128 )
SELECT @res = max (s) from NUM
WHERE n <= @total and
NOT exists ( select * from #T
WHERE #T.n = NUM.n)
GO
为精确测量存储过程执行时间,我使用了以下代码:
DECLARE
@t1
datetime
,
@n
int
SET @t1 = getdate ()
SET @n = 100 – ( ** )
WHILE @n > 0 begin
EXEC T1 1000 – ( * )
SET @n = @n - 1 end
SELECT datediff (ms, @t1 , getdate ())
GO
(*) 是存储过程参数.
SET @t1 = getdate ()
SET @n = 100 – ( ** )
WHILE @n > 0 begin
EXEC T1 1000 – ( * )
SET @n = @n - 1 end
SELECT datediff (ms, @t1 , getdate ())
GO
现在我们给这个存储过程来第一次提速:个它加个主键
CREATE
procedure
T2
@total int
AS
CREATE table #T (n int primary key , s varchar ( 128 ))
INSERT into #T select n,s from NUM
WHERE n % 100 > 0 and n <= @total
DECLARE @res varchar ( 128 )
SELECT @res = max (s) from NUM
WHERE n <= @total and
NOT exists ( select * from #T
WHERE #T.n = NUM.n)
GO
在下面的对比中你可以发现已经大大的提速了.
@total int
AS
CREATE table #T (n int primary key , s varchar ( 128 ))
INSERT into #T select n,s from NUM
WHERE n % 100 > 0 and n <= @total
DECLARE @res varchar ( 128 )
SELECT @res = max (s) from NUM
WHERE n <= @total and
NOT exists ( select * from #T
WHERE #T.n = NUM.n)
GO
然后再来一次提速:给它加个聚集索引
CREATE
procedure
T3
@total int
AS
CREATE table #T (n int , s varchar ( 128 ))
INSERT into #T select n,s from NUM
WHERE n % 100 > 0 and n <= @total
CREATE clustered index Tind on #T (n)
DECLARE @res varchar ( 128 )
SELECT @res = max (s) from NUM
WHERE n <= @total and
NOT exists ( select * from #T
WHERE #T.n = NUM.n)
GO
@total int
AS
CREATE table #T (n int , s varchar ( 128 ))
INSERT into #T select n,s from NUM
WHERE n % 100 > 0 and n <= @total
CREATE clustered index Tind on #T (n)
DECLARE @res varchar ( 128 )
SELECT @res = max (s) from NUM
WHERE n <= @total and
NOT exists ( select * from #T
WHERE #T.n = NUM.n)
GO
很惊讶速度已经提高了很多!
那好了,咱们再来测试一下table变量的速度吧
CREARE
procedure
V1
@total int
AS
DECLARE @V table (n int , s varchar ( 128 ))
INSERT into @V select n,s from NUM
WHERE n % 100 > 0 and n <= @total
DECLARE @res varchar ( 128 )
SELECT @res = max (s) from NUM
WHERE n <= @total and
NOT exists ( select * from @V V
WHERE V.n = NUM.n)
GO
@total int
AS
DECLARE @V table (n int , s varchar ( 128 ))
INSERT into @V select n,s from NUM
WHERE n % 100 > 0 and n <= @total
DECLARE @res varchar ( 128 )
SELECT @res = max (s) from NUM
WHERE n <= @total and
NOT exists ( select * from @V V
WHERE V.n = NUM.n)
GO
然后再来创建一个有主键的:
CREATE
procedure
V2
@total int
AS
DECLARE @V table (n int primary key , s varchar ( 128 ))
INSERT into @V select n,s from NUM
WHERE n % 100 > 0 and n <= @total
DECLARE @res varchar ( 128 )
SELECT @res = max (s) from NUM
WHERE n <= @total and
NOT exists ( select * from @V V
WHEREre V.n = NUM.n)
GO <
@total int
AS
DECLARE @V table (n int primary key , s varchar ( 128 ))
INSERT into @V select n,s from NUM
WHERE n % 100 > 0 and n <= @total
DECLARE @res varchar ( 128 )
SELECT @res = max (s) from NUM
WHERE n <= @total and
NOT exists ( select * from @V V
WHEREre V.n = NUM.n)
GO <
然后咱们来看看我测试的结果吧!
Records | T1 | T2 | T3 | V1 | V2 |
10 | 0.7 | 1 | 13.5 | 0.6 | 0.8 |
100 | 1.2 | 1.7 | 14.2 | 1.2 | 1.3 |
1000 | 7.1 | 5.5 | 27 | 7 | 5.3 |
10000 | 72 | 57 | 82 | 71 | 48 |
100000 | 883 | 480 | 580 | 840 | 510 |
1000000 | 45056 | 6090 | 15220 | 20240 | 12010 |
But the real shock is when you try the same on SQL Server 2005:
N | T1 | T2 | T3 | V1 | V2 |
10 | 0.5 | 0.5 | 5.3 | 0.2 | 0.2 |
100 | 2 | 1.2 | 6.4 | 61.8 | 2.5 |
1000 | 9.3 | 8.5 | 13.5 | 168 | 140 |
10000 | 67.4 | 79.2 | 71.3 | 17133 | 13910 |
100000 | 700 | 794 | 659 | Too long! | Too long! |
1000000 | 10556 | 8673 | 6440 | Too long! | Too long! |
发现对比在某些情况下sql2000的速度要比2005的要快上很多!
结论:
没有通用的规则指导你什么时候用临时表什么时候用table变量.
将复杂逻辑的存储过程移植到sql2005的时候,要格外小心!他可能比2000的效率要慢上好几十倍的!
在你的实际测试中,请测试两个极端:销量数据和超大量数据.