关于Sql Server中临时表与表变量的问题。。。

表结构是这样的（为了方便描述，非必要字段没有写出）：
A表，即主表：

数据量在17W。

B表，即子表：

数据量在50W.

我有下面两组sql。
第一组：使用临时表：

CREATE TABLE #tmp
(
	fid INT
)

INSERT INTO #tmp
        ( fid )
SELECT list.fId FROM dbo.list INNER JOIN dbo.main ON main.fId = list.fParentId
WHERE main.fType = 99 AND list.fName LIKE '%bad%'

SELECT * FROM #tmp

DROP TABLE #tmp

第二组：使用表变量

DECLARE @tmp TABLE
(
	fid INT
)

INSERT INTO@tmp
        ( fid )
SELECT list.fId FROM dbo.list INNER JOIN dbo.main ON main.fId = list.fParentId
WHERE main.fType = 99 AND list.fName LIKE '%bad%'

SELECT * FROM @tmp

筛选后的数据量不到100行。
现在我碰到的问题是：使用临时表只需要大概200ms，但是使用表变量接近2S。
不知道是为什么，请大家指教。


下面的是一些统计数据。
IO情况（临时表）：

表 'main'。扫描计数 25，逻辑读取 9104 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'list'。扫描计数 25，逻辑读取 13846 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 '#tmp________________________________________________________________________________________________________________0000001119C7'。扫描计数 0，逻辑读取 73 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

（表变量）

表 '#04862155'。扫描计数 0，逻辑读取 73 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'list'。扫描计数 1，逻辑读取 12575 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'main'。扫描计数 1，逻辑读取 8285 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

41 个解决方案

#1

表变量缺乏统计信息，外国专家建议少于100条数据时可以用表变量，超过后使用临时表，个人没有真正测过这个阈值，但是在百万级临时数据环境下，的确临时表更为优秀

#2

两者核心区别在于：
1、临时表有统计信息，优化器可以得知其中数据分布，做出尽可能优秀的优化策略，但是表变量没有，所以几乎只有表扫描。
2、临时表可以有索引，但是表变量只能在“定义”时加上主键约束，在列较多的情况下不利于索引覆盖

#3

谢谢版主DBA_Huangzj的回答。
我的表在fName字段本来就没有索引，所以两个查询里面都是表扫描，按理来说效率应该是一样的。
insert应该不会消耗很多资源吧！
请指正。

#4

不好意思，表结构的图片有点问题，【fType】字段应该是主表的。

#5

fName LIKE '%bad%' 这种写法有索引也用处不大。但是你的性能数据真的是类似上面语句出来的？insert方面应该不存在那么明显的差异

#6

引用 5 楼 DBA_Huangzj 的回复:

fName LIKE '%bad%' 这种写法有索引也用处不大。但是你的性能数据真的是类似上面语句出来的？insert方面应该不存在那么明显的差异

肯定，我还特地新建了数据库，将数据导入测试的。
完整的sql语句复杂，我经过详细的排查，确定性能问题出在这个语句中。
相差这么大让我很费解。

#7

我这边测试没发现你的问题

#8

谢谢你的耐心回答。
主要问题应该是在like上面，我之前试着把like这个条件去掉，两个的性能几乎一样。

#9

但是like与否都是同一个语句啊。如果是数据量，我这里有100万比你的还多

#10

等会儿。我做个Demo

#11

    SET STATISTICS TIME ON 
    CREATE TABLE #tmp
        (
          fid INT ,
          fflowid INT ,
          fflowtype INT ,
          fdate DATETIME ,
          fdetailid INT ,
          famount DECIMAL(18, 4) DEFAULT ( 0 )
        )
    INSERT INTO  #tmp
            ( fid ,
              fflowid ,
              fflowtype ,
              fdate ,
              fdetailid ,
              famount

            )
            SELECT  T1.fId ,
                    T1.fOutFlowId ,
                    T1.fOutNodeType ,
                    T1.fDate ,
                    T2.fId ,
                    T2.fAmount
            FROM    dbo.uMarketOfficeStock T1
                    INNER JOIN dbo.uMarketOfficeStockList T2 ON T1.fId = T2.fParentId
            WHERE   T1.aId = 650
                    AND t2.fProductName LIKE '%bad%'
        
    DROP TABLE #tmp
    
SET STATISTICS TIME OFF 

结果：

SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。

(73 row(s) affected)

(1 row(s) affected)

SQL Server 执行时间:
   CPU 时间 = 2701 毫秒，占用时间 = 191 毫秒。

SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 4 毫秒。

SET STATISTICS TIME ON 
DECLARE @tmp TABLE
    (
      fid INT ,
      fflowid INT ,
      fflowtype INT ,
      fdate DATETIME ,
      fdetailid INT ,
      famount DECIMAL(18, 4) DEFAULT ( 0 )
    )
INSERT  INTO @tmp
        ( fid ,
          fflowid ,
          fflowtype ,
          fdate ,
          fdetailid ,
          famount

        )
        SELECT  T1.fId ,
                T1.fOutFlowId ,
                T1.fOutNodeType ,
                T1.fDate ,
                T2.fId ,
                T2.fAmount
        FROM    dbo.uMarketOfficeStock T1
                INNER JOIN dbo.uMarketOfficeStockList T2 ON T1.fId = T2.fParentId
        WHERE   T1.aId = 650
                AND t2.fProductName LIKE '%bad%'
SET STATISTICS TIME OFF 


结果：

(73 row(s) affected)

(1 row(s) affected)

SQL Server 执行时间:
   CPU 时间 = 1404 毫秒，占用时间 = 1469 毫秒。

SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

#12

这个是使用真实的数据来测试的，两个的效率相差很大。

#13

除了数据之外，其他都是根据你的来写的，没看出差异

#14

我去掉   AND t2.fProductName LIKE '%bad%'之后

临时表

SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。

(293 row(s) affected)

(1 row(s) affected)

SQL Server 执行时间:
   CPU 时间 = 782 毫秒，占用时间 = 157 毫秒。

SQL Server 执行时间:
   CPU 时间 = 15 毫秒，占用时间 = 5 毫秒。

表变量

(293 row(s) affected)

(1 row(s) affected)

SQL Server 执行时间:
   CPU 时间 = 234 毫秒，占用时间 = 292 毫秒。

相差也不会很大，但是加上  AND t2.fProductName LIKE '%bad%'之后，差别就很明显了
不知道怎么回事？

#15

两个写法的执行计划贴来看看

#16

临时表：

表变量：


谢谢。

#17

你这是包括insert 和like的了吧？

#18

临时表：

INSERT  INTO #tmp          ( fid ,            fflowid ,            fflowtype ,            fdate ,            fdetailid ,            famount                )          SELECT  T1.fId ,                  T1.fOutFlowId ,                  T1.fOutNodeType ,                  T1.fDate ,                  T2.fId ,                  T2.fAmount          FROM    dbo.uMarketOfficeStock T1                  INNER JOIN dbo.uMarketOfficeStockList T2 ON T1.fId = T2.fParentId          WHERE   T1.aId = 650                  AND T1.fIsClose = 0                  AND ( T1.fOutFlowState > 5                        AND T1.fOutNodeType = 99                        OR T1.fOutFlowState > 30                        AND T1.fOutNodeType = 150                      )                   AND T2.fProductName LIKE '%bad%'
  |--Table Insert(OBJECT:([tempdb].[dbo].[#tmp]), SET:([tempdb].[dbo].[#tmp].[fid] = [WAROM201].[dbo].[uMarketOfficeStock].[fId] as [T1].[fId],[tempdb].[dbo].[#tmp].[fflowid] = [WAROM201].[dbo].[uMarketOfficeStock].[fOutFlowId] as [T1].[fOutFlowId],[tempdb].[dbo].[#tmp].[fflowtype] = [WAROM201].[dbo].[uMarketOfficeStock].[fOutNodeType] as [T1].[fOutNodeType],[tempdb].[dbo].[#tmp].[fdate] = [WAROM201].[dbo].[uMarketOfficeStock].[fDate] as [T1].[fDate],[tempdb].[dbo].[#tmp].[fdetailid] = [WAROM201].[dbo].[uMarketOfficeStockList].[fId] as [T2].[fId],[tempdb].[dbo].[#tmp].[famount] = [WAROM201].[dbo].[uMarketOfficeStockList].[fAmount] as [T2].[fAmount],[tempdb].[dbo].[#tmp].[finvoiced] = [Expr1008],[tempdb].[dbo].[#tmp].[freturned] = [Expr1009],[tempdb].[dbo].[#tmp].[ftransfered] = [Expr1010]))
       |--Compute Scalar(DEFINE:([Expr1008]=(0.0000), [Expr1009]=(0.0000), [Expr1010]=(0.0000)))
            |--Top(ROWCOUNT est 0)
                 |--Parallelism(Gather Streams)
                      |--Hash Match(Inner Join, HASH:([T1].[fId])=([T2].[fParentId]), RESIDUAL:([WAROM201].[dbo].[uMarketOfficeStockList].[fParentId] as [T2].[fParentId]=[WAROM201].[dbo].[uMarketOfficeStock].[fId] as [T1].[fId]))
                           |--Parallelism(Distribute Streams, Broadcast Partitioning)
                           |    |--Clustered Index Scan(OBJECT:([WAROM201].[dbo].[uMarketOfficeStock].[PK_uMarketProductStock] AS [T1]), WHERE:([WAROM201].[dbo].[uMarketOfficeStock].[aId] as [T1].[aId]=(650) AND [WAROM201].[dbo].[uMarketOfficeStock].[fIsClose] as [T1].[fIsClose]=(0) AND ([WAROM201].[dbo].[uMarketOfficeStock].[fOutFlowState] as [T1].[fOutFlowState]>(5) AND [WAROM201].[dbo].[uMarketOfficeStock].[fOutNodeType] as [T1].[fOutNodeType]=(99) OR [WAROM201].[dbo].[uMarketOfficeStock].[fOutFlowState] as [T1].[fOutFlowState]>(30) AND [WAROM201].[dbo].[uMarketOfficeStock].[fOutNodeType] as [T1].[fOutNodeType]=(150))))
                           |--Clustered Index Scan(OBJECT:([WAROM201].[dbo].[uMarketOfficeStockList].[PK_uMarketProductStockList] AS [T2]), WHERE:([WAROM201].[dbo].[uMarketOfficeStockList].[fProductName] as [T2].[fProductName] like N'%bad%'))

表变量：

INSERT  @tmp          ( fid ,            fflowid ,            fflowtype ,            fdate ,            fdetailid ,            famount                )          SELECT  T1.fId ,                  T1.fOutFlowId ,                  T1.fOutNodeType ,                  T1.fDate ,                  T2.fId ,                  T2.fAmount          FROM    dbo.uMarketOfficeStock T1                  INNER JOIN dbo.uMarketOfficeStockList T2 ON T1.fId = T2.fParentId          WHERE   T1.aId = 650                  AND T1.fIsClose = 0                  AND ( T1.fOutFlowState > 5                        AND T1.fOutNodeType = 99                        OR T1.fOutFlowState > 30                        AND T1.fOutNodeType = 150                      )  --转库                  AND T2.fProductName LIKE '%bad%'
  |--Table Insert(OBJECT:(@tmp), SET:([fid] = [WAROM201].[dbo].[uMarketOfficeStock].[fId] as [T1].[fId],[fflowid] = [WAROM201].[dbo].[uMarketOfficeStock].[fOutFlowId] as [T1].[fOutFlowId],[fflowtype] = [WAROM201].[dbo].[uMarketOfficeStock].[fOutNodeType] as [T1].[fOutNodeType],[fdate] = [WAROM201].[dbo].[uMarketOfficeStock].[fDate] as [T1].[fDate],[fdetailid] = [WAROM201].[dbo].[uMarketOfficeStockList].[fId] as [T2].[fId],[famount] = [WAROM201].[dbo].[uMarketOfficeStockList].[fAmount] as [T2].[fAmount],[finvoiced] = [Expr1008],[freturned] = [Expr1009],[ftransfered] = [Expr1010]))
       |--Compute Scalar(DEFINE:([Expr1008]=(0.0000), [Expr1009]=(0.0000), [Expr1010]=(0.0000)))
            |--Top(ROWCOUNT est 0)
                 |--Hash Match(Inner Join, HASH:([T1].[fId])=([T2].[fParentId]), RESIDUAL:([WAROM201].[dbo].[uMarketOfficeStockList].[fParentId] as [T2].[fParentId]=[WAROM201].[dbo].[uMarketOfficeStock].[fId] as [T1].[fId]))
                      |--Clustered Index Scan(OBJECT:([WAROM201].[dbo].[uMarketOfficeStock].[PK_uMarketProductStock] AS [T1]), WHERE:([WAROM201].[dbo].[uMarketOfficeStock].[aId] as [T1].[aId]=(650) AND [WAROM201].[dbo].[uMarketOfficeStock].[fIsClose] as [T1].[fIsClose]=(0) AND ([WAROM201].[dbo].[uMarketOfficeStock].[fOutFlowState] as [T1].[fOutFlowState]>(5) AND [WAROM201].[dbo].[uMarketOfficeStock].[fOutNodeType] as [T1].[fOutNodeType]=(99) OR [WAROM201].[dbo].[uMarketOfficeStock].[fOutFlowState] as [T1].[fOutFlowState]>(30) AND [WAROM201].[dbo].[uMarketOfficeStock].[fOutNodeType] as [T1].[fOutNodeType]=(150))))
                      |--Clustered Index Scan(OBJECT:([WAROM201].[dbo].[uMarketOfficeStockList].[PK_uMarketProductStockList] AS [T2]), WHERE:([WAROM201].[dbo].[uMarketOfficeStockList].[fProductName] as [T2].[fProductName] like N'%bad%'))

#19

你表变量的执行计划里面看到你注释了like哦

#20

一样的，表变量那个是受之前的注释语句影响的，其实它们不在一行的

#21

这个问题很是困扰，看执行计划， 表变量的计划甚至比 临时表的效率更高。
但是结果反差这么大。不知道是不是我什么地方设置有问题！

#22

你把鼠标移到【compute Scalar】到【table insert】的那个箭头，分别看看两个的实际行数和预估影响行数

#23

我私信给你了。

#24

谢谢你帮忙。我在家里无法连接公司的server。明天我再试试

#25

USE [WAROM201]
GO
CREATE NONCLUSTERED INDEX NonIX_uMarketOfficeStock_MissInx
ON [dbo].[uMarketOfficeStock] ([aId],[fIsClose])
INCLUDE ([fId],[fDate],[fOutFlowId],[fOutNodeType],[fOutFlowState])
GO

由于你的是一整个语句，所以优化器会一并优化，导致语句间有差异，你试试加上这个索引之后，对比一下两者是否差异还是很大，如果还是的话，再发执行计划给我看看

#26

我发现你表变量里面有个option(maxdop 1)什么回事？

#27

引用 26 楼 DBA_Huangzj 的回复:

我发现你表变量里面有个option(maxdop 1)什么回事？

是我昨天测试的，好像没什么效果。

#28

晕，对比差异必须一模一样啊，你临时表不加但是表变量加了，就强制剥夺了表变量可能的并行操作，没有对比可言

#29

你重新发一个一模一样的写法过来吧。别加hints

#30

我重新发了一份在你邮箱了！

#31

看到了。。。

#32

USE [WAROM201]
go 
UPDATE STATISTICS dbo.uMarketOfficeStock PK_uMarketProductStock;

先执行一下这个试试

#33

引用 32 楼 DBA_Huangzj 的回复:

USE [WAROM201]
go 
UPDATE STATISTICS dbo.uMarketOfficeStock PK_uMarketProductStock;

先执行一下这个试试

同样的结果。

这个问题是不是因为 表变量的关系，而导致 计划没有缓存呢？

#34

这个有点诡异，我查下资料先

#35

25楼的脚本执行过了没？

#36

16楼的两个图明显不一样啊！
无论那种方案，最后一步才是插入，那么前面的步骤应该完全一样才是。
版主上当了！

#37

引用 35 楼 DBA_Huangzj 的回复:

25楼的脚本执行过了没？

执行过了，结果还是跟之前的那样！

#38

引用 36 楼 Tiger_Zhao 的回复:

16楼的两个图明显不一样啊！
无论那种方案，最后一步才是插入，那么前面的步骤应该完全一样才是。
版主上当了！

没有，理论上就如你所说。
但是实际情况却不是这样的！
你可以实验一下。

#39

执行过应该不会有missing index的啊，怎么会一样呢？

#40

数据多的话，就要用临时表好一些

#41

没有找到问题的答案，先结贴吧，我自己研究研究！