0
votes

I'm trying to optimize aggregate querry on two tables:

UpdateHistory (<5000 rows): Id, UserId, Date, Param...

AccessHistory (>100 000 rows): Id, UserId, TimeStamp, Value, many others...

(additional Index IX_AccessHistory_UserId_TimeStamp: UserId, TimeStamp, include Value, non clustered)

I'm looking for max sum value for user, counted since update date.

I've got 3 queries:

declare @TmpTable table (UserId int, TmpDate Date);                              
  insert into @TmpTable(UserId, TmpDate)                                  
  select  UserId, convert(date, max(UpdateDate)) as TmpDate from [ClientDatabase].[dbo].[UpdateHistory] 
  where Param = 1 group by UserId;

  select top 3 tmp.UserId, sum(Value) as RESULT from @TmpTable tmp                             
  join [ClientDatabase].[dbo].[AccessHistory] a on tmp.UserId= a.UserId                              
  where TimeStamp > TmpDate                              
  group by tmp.UserId order by RESULT DESC;


with tmp as 
  (                             
  select  UserId, convert(date, max(UpdateDate)) as TmpDate from [ClientDatabase].[dbo].[UpdateHistory] 
  where Param = 1 group by UserId
  )

  select top 3 tmp.UserId, sum(Value) as RESULT from tmp                             
  join [ClientDatabase].[dbo].[AccessHistory] a on tmp.UserId= a.UserId                              
  where TimeStamp > TmpDate                              
  group by tmp.UserId order by RESULT DESC;


  select top 3 tmp.UserId, sum(Value) as RESULT from [ClientDatabase].[dbo].[AccessHistory] a join 
      (select  UserId, convert(date, max(UpdateDate)) as TmpDate from [ClientDatabase].[dbo].[UpdateHistory] 
      where Param = 1 group by UserId ) tmp  
      on tmp.UserId = a.UserId                              
      where TimeStamp > TmpDate                              
      group by tmp.UserId order by RESULT DESC;

The differences in proceesing times are drastic: with 8% for the temp table (4% for insert and 4% for querry), and 46% for the other two. The difference seems to mainly sit in the non clustered Index Seek on AccessHistory table.

Can someone explain to me this difference and suggest some fix? Or can I just leave the temp table be?

https://i.stack.imgur.com/k6hVc.png

Your title is pretty confusing. DECLARE and aggregate functions have no relation at all. A table variable variable doesn't care if you put aggregated data in it or not. - Larnu
i meant I'm aggregating filled table not inserting agreggated data (although I'm doing both those things). - kallinea rae
To understand performance issues you start by examining the execution plans. To understand those, you need to also include DDL for the objects and some indication about the distribution of values used for filtering, the number of rows accessed generally, and the number of rows returned. - SMor