1

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.

fdkgfosfskjdlsjdlkfsf
  • 3,165
  • 2
  • 43
  • 110
  • 1
    See [here](https://stackoverflow.com/questions/11857789/when-should-i-use-a-table-variable-vs-temporary-table-in-sql-server) and [here](https://dba.stackexchange.com/questions/16385/whats-the-difference-between-a-temp-table-and-table-variable-in-sql-server/16386#16386) – MatSnow Oct 10 '17 at 13:36
  • 1
    For only 2,000 rows I would be looking into the performance of the select statement. 8 seconds is unacceptable for so few rows in almost every situation. – Sean Lange Oct 10 '17 at 13:36
  • Well, that's pretty strange. Both `DECLARE @table` and `CREATE ... #table` creates an actual table in `tempdb`. I guess the problem is in `cte` – gofr1 Oct 10 '17 at 13:55
  • probably something in your server config or db config – Antoine Pelletier Oct 10 '17 at 13:59
  • The `cte` runs in less than 2 seconds. The `INSERT` to `#temp` does not add any overhead. It's the insert to the @table that adds 6 seconds of overhead. – fdkgfosfskjdlsjdlkfsf Oct 10 '17 at 14:29

1 Answers1

1

For testing performance:

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

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Stanislav Kundii
  • 2,874
  • 1
  • 11
  • 17