Intervals query of huge data My question is more or less theoretical - what is the best approch to get query data by intervals.

The situation

I have a system that manage counters (thousands of counters - counts consumption of electricity/water etc...), every 15 minutes the counter is sampled and stored in the DB. there is one table that stores all counters data [id, counterID, Timestamp, CounterValue].

Now, I want to query the data about the Interval (hourly/daily/weekly/monthley) consumption.

The intervaled consumption defined as the first measurment (counting) in the start of the next interval minus the first measurment in the start of the current interval. since all measurments are agregated (counters) the delta between two counters represents the consumption in the interval.

Since there is ALOT of data and I want the queries to be fast I need a better way then just quering the whole table... (I think...)
quering the whole data (even with small amount of data - this query is slow...):

SELECT *, dateadd(hour,datediff(hour,0, dateOfData),0) as roundedTime
FROM (  SELECT *, ROW_NUMBER() OVER(PARTITION BY  counterId,datediff(hour, 0, dateOfData) ORDER BY dateOfData asc) row_number
        FROM counters_data) A
WHERE row_number = 1
Order by dateOfData;


I thought about some ways doing this:


Add [isHourly,isDaily,isWeekly...] boolean columns in the measurments table and when I store the data I'll update those flags.
Add tables for every interval with columns [id, date, counterId, counterDataRecord] so there will be tblDailyCounter, tblWeeklyCounter etc...


is there any other idea that Im missing?

what is the pros&amp;cons of those aproches?

what is the most recomanded / best practice?
Well this answer depends on how your database is designed and licensed.

If you have an Enterprise license, you should take a look at partitioning. This way you may partition your data which will help you in querying those table. If you make a good partitioning, you'll be able to just read a small portion of the table from the disc, instead of querying everything.

I don't know how your application writes to your database. But maybe it's possible to flag the first record of an hour so that you don't need to filter it later on that way? 

Another idea is a SQL Agent, which will perform those aggregations in the background for you. Maybe this could be hourly and write all aggregated values to another cumulative table, which could be queried by you even faster.