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?
DECLAREand aggregate functions have no relation at all. A table variable variable doesn't care if you put aggregated data in it or not. - Larnu