Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
select dateadd(ss,@interval, [date])
from CTE_TimeTable
where DateAdd(ss,@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 ,
@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.

share|improve this question
    
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
2  
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
2  
@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
show 3 more comments

2 Answers

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
  select dateadd(second ,@interval, [date])
  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
       c.Date < dateadd(second, @interval, t.dt)
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
  select dateadd(second, 5, D)
  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);
share|improve this answer
add comment
up vote 0 down vote accepted

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
(
select Dateadd(ss,-(@interval/2), @start) as [sdate], Dateadd(ss,(@interval/2),@start) 
as [edate] , @start as [middate]
union all
select dateadd(ss,@interval, [sdate]) , dateadd(ss,@interval,[edate]) ,    
dateadd(ss,@interval, [middate])
from CTE_TimeTable
where DateAdd(ss,@interval, [middate]) < @end
)
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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.