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)
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.)
As far as when to use each, they have very different use cases. If you will have a very large result set, or need to refer to it more than once, put it in a #temp table. If it needs to be recursive, is disposable, or is just to simplify something logically, a CTE is preferred.
The reason, temp tables are faster in loading data as they are created in the tempdb and the logging works very differently for temp tables. All the data modifications are not logged in the log file the way they are logged in the regular table, hence the operation with the Temp tables are faster.
Summary of Performance Testing for SQL Server Temp Tables vs. Table Variables. As we can see from the results above a temporary table generally provides better performance than a table variable. The only time this is not the case is when doing an INSERT and a few types of DELETE conditions.
The @table
syntax creates a table variable (an actual table in tempdb
) and materialises the results to it.
The WITH
syntax defines a Common Table Expression which is not materialised and is just an inline View.
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.
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)
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.
For further information regarding the differences between table variables and #temp tables see here.
In addition to what Martin answered
;with tempTable as
(
select * from aces.dbo.fEDVisitCounts('ALL', null,DATEADD(DD,-7,'01-09-2010'),
'04-09-2010',0,130,null,1, 0)
)
SELECT * FROM tempTable
can also be written like this
SELECT * FROM
(
select * from aces.dbo.fEDVisitCounts('ALL', null,DATEADD(DD,-7,'01-09-2010'),
'04-09-2010',0,130,null,1, 0)
) AS tempTable --now you can join here with other tables
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With