SQL Server,带有截断的临时表与带有删除的表变量 [英] SQL Server, temporary tables with truncate vs table variable with delete
问题描述
我有一个存储过程,我在其中创建了一个通常包含 1 到 10 行的临时表.该表在存储过程中被多次截断和填充.它被截断,因为这比删除快.当我因使用删除而受到惩罚时,我是否通过用表变量替换这个临时表来获得任何性能提升(截断不适用于表变量)
I have a stored procedure inside which I create a temporary table that typically contains between 1 and 10 rows. This table is truncated and filled many times during the stored procedure. It is truncated as this is faster than delete. Do I get any performance increase by replacing this temporary table with a table variable when I suffer a penalty for using delete (truncate does not work on table variables)
虽然表变量主要在内存中并且通常比临时表快,但我是否因为必须删除而不是截断而失去任何好处?
Whilst table variables are mainly in memory and are generally faster than temp tables do I loose any benefit by having to delete rather than truncate?
推荐答案
运行以下脚本,看起来表变量是更好的选择
Running the followign to scripts, it would seem that the Table Variable is the better option
CREATE TABLE #Temp(
ID INT
)
DECLARE @Int INT,
@InnerInt INT
SELECT @Int = 1,
@InnerInt = 1
WHILE @Int < 50000
BEGIN
WHILE @InnerInt < 10
BEGIN
INSERT INTO #Temp SELECT @InnerInt
SET @InnerInt = @InnerInt + 1
END
SELECT @Int = @Int + 1,
@InnerInt = 1
TRUNCATE TABLE #Temp
END
DROP TABLE #TEMP
GO
DECLARE @Temp TABLE(
ID INT
)
DECLARE @Int INT,
@InnerInt INT
SELECT @Int = 1,
@InnerInt = 1
WHILE @Int < 50000
BEGIN
WHILE @InnerInt < 10
BEGIN
INSERT INTO @Temp SELECT @InnerInt
SET @InnerInt = @InnerInt + 1
END
SELECT @Int = @Int + 1,
@InnerInt = 1
DELETE FROM @Temp
END
来自 Sql Profiler
From Sql Profiler
CPU Reads Writes Duration
36375 2799937 0 39319
vs
CPU Reads Writes Duration
14750 1700031 2 17376
这篇关于SQL Server,带有截断的临时表与带有删除的表变量的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!