# Is there a better way to create dynamic time averaged series over a dynamic frequency?

I've got a series of tables with lots of high precision data collected from various devices. The intervals that they were collected on varies and even wanders over the time series. My users want the ability to pick a date range and get an average / min / max over these variables at a specific frequency. This is the second stab I've taken at this, and it works, but I wonder if there is a better/faster way to accomplish this?

``````declare @start datetime
declare @end datetime
set @start = '3/1/2012'
set @end = '3/3/2012'
declare @interval int
set @interval = 300

declare @tpart table(
dt datetime
);

with CTE_TimeTable
as
(
select @start as [date]
union all
from CTE_TimeTable
)
insert into @tpart
select [date] from CTE_TimeTable
OPTION (MAXRECURSION 0);

select t.dt, avg(c.x1), min(c.x1), max(c.x2), avg(c.x2), min(c.x2), max(c.x2)  from clean.data c ,
@tpart t
where
ABS(DateDIFF(ss, t.dt , c.Date) ) <= @interval /2
and
Date >= @start
and
Date <= @end
group by t.dt
``````

Right now over 32721 rows for this 3 day period this query takes about 43 seconds to run and gives me the 577 rows I expect but I'd like to get this faster. The big hit comes from the nested loop to do the inner join.

• Great first question and welcome to DBA.SE. What indexes (both clustered and nonclustered) do you have on `clean.data`? Also, can you explain why you are cross joining it to `@tpart`? – Nick Chammas May 14 '12 at 17:20
• clustered index on clean.data.Date. I've tried doing an inner join and cross and the query plan didn't change. The bugger here is that abs(datediff(ss, t.dt, c.date)) statement. – chris.w.mclean May 14 '12 at 17:28
• What is the purpose of that `DATEDIFF` statement? – Nick Chammas May 14 '12 at 17:58
• What about using a computed column for the value of `ABS(DateDIFF(ss, t.dt , c.Date) )`? You could index it. From my understanding (which may be WAAAAY wrong), doesn't the operation on the `c.Date` column in the `WHERE` basically render the index useless? – swasheck May 14 '12 at 18:28
• @swasheck - The interval is dependent on user input, so a computed column is not possible. Also, the index should still be usable by the range search in the latter part of the `WHERE` clause. – Nick Chammas May 14 '12 at 18:38

Your join between the tables is embedded in a function making it really hard for the optimizer to do anything smart with it. I guess it has to compare every row in one table against every other row in the other table.

Rewriting your join with a range check should be a lot faster. I also added a primary key to your table variable to remove a sort operation from the query plan and I turned your table variable into a temp table instead. The difference in my tests was that the query plan started to use Parallelism.

``````declare @start datetime;
declare @end datetime;
set @start = '20120301';
set @end = '20120303';
declare @interval int;
set @interval = 300;

create table #tpart
(
dt datetime primary key
);

with CTE_TimeTable
as
(
select @start as [date]
union all
from CTE_TimeTable
where dateadd(second, @interval, [date]) <= @end
)
insert into #tpart
select [date]
from CTE_TimeTable
option (maxrecursion 0);

select t.dt, avg(c.x1), min(c.x1), max(c.x2), avg(c.x2), min(c.x2), max(c.x2)
from clean.data c
inner join #tpart t
on c.Date >= t.dt and
group by t.dt;

drop table #tpart;
``````

Note: This query does not return exactly the same intervals as your query does. It will divide the date range into equally sized parts where your query had one half interval at the start and one half interval at the end of the range. It is of course possible to modify the query to do be equivalent to your query if that is desired.

Update

I tested on a table with a total of `1036801` rows and with `34560` in the interval `2012-03-01` to `2012-03-03`. In my tests the original query takes 4.1 seconds. The query above takes 0.1 seconds.

Script to generate the test data:

``````create table clean.data
(
Date datetime primary key,
x1 int,
x2 int
);

go

with C as
(
select cast('20120201' as datetime) as D
union all
from C
where D < '20120401'
)
insert into clean.data(Date, x1, x2)
select D, checksum(newid()) % 1000, checksum(newid()) % 1000
from C
option (maxrecursion 0);
``````

Mikael Eriksson's answer inspired me to modify my original code somewhat. This is what I came up with.

``````declare @tpart table(
dtStart datetime,
dtEnd datetime,
midTime datetime
);

with CTE_TimeTable
as
(
as [edate] , @start as [middate]
union all
from CTE_TimeTable
)
insert into @tpart
select [sdate] , [edate] , [middate] from CTE_TimeTable
OPTION (MAXRECURSION 0);
``````

So now the @tpart table variable has precomputed both the start and end times of the query as well as the midpoint to group on. So now my query looks like this:

``````select t.midtime, avg(c.x1), min(c.x1), max(c.x2), avg(c.x2), min(c.x2), max(c.x2)  from
clean.data c inner join
@tpart t
on c.Date >= t.dtStart and c.Date < t.dtEnd
group by t.midtime
``````

This gave me a query time of approx .9 seconds. Not quite as good as Mikael's but much better than what I had started with and good enough for my users. This was over approx 80,000 rows for a single day averaging into five minute chunks.