使用与 vs 声明一个临时表:性能/差异? [英] Using with vs declare a temporary table: performance / difference?

问题描述

我在 SQLServer 2008 中创建了一个 sql 函数，它声明了一个临时表并使用它来计算内部值的移动平均值

I have created a sql function in SQLServer 2008 that declared a temporary table and uses it to compute a moving average on the values inside

declare @tempTable table 
    (
        GeogType nvarchar(5),
        GeogValue nvarchar(7),
        dtAdmission date,
        timeInterval int,
        fromTime nvarchar(5),
        toTime nvarchar(5),
        EDSyndromeID tinyint,
        nVisits int
    )
insert @tempTable select * from aces.dbo.fEDVisitCounts(@geogType, @hospID,DATEADD(DD,-@windowDays + 1,@fromDate),
                @toDate,@minAge,@maxAge,@gender,@nIntervalsPerDay, @nSyndromeID)


    INSERT @table (dtAdmission,EDSyndromeID, MovingAvg) 
    SELECT list.dtadmission
        , @nSyndromeID
        , AVG(data.nVisits) as MovingAvg
    from @tempTable as list 
        inner join @tempTable as data  
    ON list.dtAdmission between data.dtAdmission and DATEADD(DD,@windowDays - 1,data.dtAdmission) 
    where list.dtAdmission >= @fromDate
    GROUP BY list.dtAdmission

但我也发现你可以像这样声明临时表:

but I also found out that you can declare the tempTable like this:

with tempTable as 
(
    select * from aces.dbo.fEDVisitCounts('ALL', null,DATEADD(DD,-7,'01-09-2010'),
        '04-09-2010',0,130,null,1, 0)
)

问题:这两种方法有什么主要区别吗?一个比另一个更快或更常见/标准吗?我认为声明更快，因为你定义了你正在寻找的列是什么......如果我省略那些列，它会更快吗?没有用于计算移动平均线?(不确定这个，因为无论如何它都必须获得所有行，尽管选择较少的列从直觉上来说会更快/更少做)

Question: Is there a major difference in these two approaches? Is one faster than the other or more common / standard? I would think the declare is faster since you define what the columns you are looking for are.. Would it also be even faster if I were to omit the columns that were not used in the calculations of moving average?(not sure about this one since it has to get all of the rows anyways, though selecting less columns makes intuitive sense that it would be faster/less to do)

我也从这里找到了一个创建临时表@table如何在 MySQL 中声明内部表? 但我不希望该表在函数之外持久化(我不确定创建临时表是否这样做.)

I also have found a create temporary table @table from here How to declare Internal table in MySQL? but I don't want the table to persist outside of the function (I am not sure if the create temporary table does this or not.)

推荐答案

@table 语法创建一个表变量(tempdb 中的一个实际表)并具体化结果到它.

The @table syntax creates a table variable (an actual table in tempdb) and materialises the results to it.

WITH 语法定义了一个公用表表达式 没有具体化，只是一个内联视图.

The WITH syntax defines a Common Table Expression which is not materialised and is just an inline View.

大多数情况下，您最好使用第二个选项.你提到这是在一个函数内.如果这是一个 TVF，那么大多数时候您希望它们是内联而不是多语句，以便优化器可以扩展它们 - 这将立即禁止使用表变量.

Most of the time you would be better off using the second option. You mention that this is inside a function. If this is a TVF then most of the time you want these to be inline rather than multi statement so they can be expanded out by the optimiser - this would instantly disallow the use of table variables.

然而，有时(假设底层查询很昂贵并且您想避免它被多次执行)您可能会确定在某些特定情况下实现中间结果可以提高性能.有 目前没有办法为 CTE 强制执行此操作(至少没有强迫计划指南)

Sometimes however (say the underlying query is expensive and you want to avoid it being executed multiple times) you might determine that materializing the intermediate results improves performance in some specific cases. There is currently no way of forcing this for CTEs (without forcing a plan guide at least)

在这种情况下，您(通常)有 3 个选择.一个 @tablevariable、#localtemp 表和一个 ##globaltemp 表.但是，在函数中只允许使用其中的第一个.

In that eventuality you (in general) have 3 options. A @tablevariable, #localtemp table and a ##globaltemp table. However only the first of these is permitted for use inside a function.

有关表变量和#temp 表之间差异的更多信息 见这里.

For further information regarding the differences between table variables and #temp tables see here.

这篇关于使用与 vs 声明一个临时表:性能/差异?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！