CODEWITHSUNDEEP
sql-servert-sql
fdkgfosfskjdlsjdlkfsf
fdkgfosfskjdlsjdlkfsf

Reputation: 3303

Insert to #table much faster than insert to @table. Why?

This is the basic structure of my query. If I insert into a #temp table then the query runs in about two seconds:

IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp

declare @table table              
(
    locationKey int,
    DateKey date,         
    totalcount bigint,
    locationCount int,
    LastDateOnAir date,
    Aging int
)       

;with cteSum as 
(
    select 
    fact.locationid as 'locationKey'
    ,cast([datetime] as date) as 'datekey'
    ,sum(totalcount) as 'totalcount'
    ,count(fact.locationCell) as 'locationCount'
    ,sum(period) as 'period'
    FROM [dbo].[MasterTable] fact inner join Dim dim on
    fact.locationid = dim.location
    WHERE cast([datetime] as date) >= '2017-09-21'
    group by
    fact.locationid, cast([datetime] as date)
) 
select         
locationKey, datekey, totalcount, locationCount into #temp
FROM cteSum
--insert into @table
--(locationKey, datekey, totalcount, locationCount)
--select         
--locationKey, datekey, totalcount, locationCount
--FROM cteSum

If I insert into the @table variable, the whole query runs in about eight seconds. The insert to a table variable adds six seconds to the execution.

Are table variables that much slower than temp tables? We're talking about 2,000 rows that are being inserted.

Thanks.

Upvotes: 1

Views: 156

Answers (1)

Stanislav Kundii
Stanislav Kundii

Reputation: 2894

For testing performance:

  1. OPTION(RECOMPILE)
  2. OPTION(MAXDOP 1)
  3. SET STATISTICS TIME ON

Temp table & variable table objects are almost identical...

Upvotes: 1

Related Questions