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:

  1. Add [isHourly,isDaily,isWeekly...] boolean columns in the measurments table and when I store the data I'll update those flags.
  2. 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&cons of those aproches?

what is the most recomanded / best practice?

  • If you would have just a daily table that would contain the consumption (not the counters) for the day, wouldn't that be enough? Or at least I wouldn't create that many different tables since you can just sum up the figures -- although an indexed view would be simple to add. – James Z Jun 21 '15 at 9:30
  • @JamesZ another issue is that sometimes I need to query the sum of many conters (all building consumbtion for instance) and that's why I think many tables are needed. but mainly Im looking for any other solution – yossico Jun 21 '15 at 10:34

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.

  • 1
    Thanks, tell me please, Isnt paratitioning is what I did in the exampled code? ... what I think is that this query runs over all the records (to give them row_number) am I wrong? – yossico Jun 22 '15 at 8:33
  • 1
    No, you partition the result set, yes. But in fact your server retrieved all rows from the disc. Table partitioning is another thing. You can compare it with disc partitioning in your operating system. You divide your table in smaller slices (defined by a partition function). After that, you'll be able to query just the rows from one partition, which will reduce the disc I/O massively. Just as an example: Partition your table by month. If you try to check your data for this month, only the partition for the current month will be touched and read. Not the whole table. – Ionic Jun 22 '15 at 8:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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