SELECT耗时1秒INSERT @TableVar SELECT耗时45秒

Question

我有一条select语句，它在一个有2.55亿行的表上执行亚秒级的操作。结果大约是50行。

当我尝试执行INSERT @Tbl SELECT ...时，查询耗时45秒。

有人能给我解释一下吗？

这是完整的批次。计时器是127秒。当插入行被注释掉时，计时器为2秒。

DBCC DROPCLEANBUFFERS
GO

DBCC FREEPROCCACHE
GO


declare @fr datetime = '2013-01-01', @to datetime = '2013-09-01'
declare @TempTable table (Title varchar(50), PlayCount int, Wt float)

declare @t1 datetime = getdate()

    insert @TempTable
    select Title, PlayCount, MaxCount * 1.0 / PlayCount as Weight
    from (
        select l.SkinDescription as Title, count(*) as PlayCount, max(count(*)) OVER() AS MaxCount
        from LegalConfiguration l 
        join Play p on p.LegalConfigNumber = l.SequenceNumber
        where p.CurrentDate between @fr and @to
        group by l.SkinDescription
    ) sub

declare @t2 datetime = getdate()

select * from @TempTable
select datediff(ss,@t1,@t2) as timer

Answer 1

我的猜测是您的次秒级select是缓存结果。而Insert @Tbl Select强制执行缓存刷新，因此45秒更接近实际时间。此外，次秒级select意味着所有2.55亿行都可以放入内存中，或者它被部分缓存，并且您只返回sql developer副本中的前N个结果，从而允许应用程序分页。

Answer 2

像@Tbl这样的表变量将查询执行限制为单个线程。更改代码以使用临时表。如下所示：

select EPSName, count(*) as PlayCount, max(count(*)) OVER() AS MaxCount
into #Tbl
from LegalConfiguration l 
join Play p on p.LegalConfigNumber = l.SequenceNumber
where p.CurrentDate between @StartDate and @EndDate
group by EPSname

或

CREATE TABLE #Tbl (EPSName VARCHAR(100),PlayCount INT, MaxCount INT)

INSERT #Tbl
select EPSName, count(*) as PlayCount, max(count(*)) OVER() AS MaxCount
from LegalConfiguration l 
join Play p on p.LegalConfigNumber = l.SequenceNumber
where p.CurrentDate between @StartDate and @EndDate
group by EPSname

您还可以尝试将OPTION(RECOMPILE)添加到原始插入查询中。

here解释了为什么并行性不适用于表变量的原因。这是这篇文章的一个重要摘录：

编译查询时，既不填充表变量，也不填充临时表，查询优化器假定“最小”行数，这在SQL Server中始终为1。当SQL Server生成“估计”的查询计划时，将使用此估计值。虽然“估计的”和“实际的”查询计划在大多数情况下是相同的，但也有一些例外。如果在与查询相同的批处理中创建表(临时或永久)，则SQL Serve必须重新编译查询，因为在第一次编译批处理时，查询定义是未知的。这不是表变量的情况。