asked
Database Table Design--Rows vs Columns
I have to store the energy consumption of a large number of meters with a 1 minute frequency. The data coming to me is as meter readings for every 1 minute and I have to convert that into consumption. I can either store the data in rows for each day with 1440 (60*24) rows for a single day OR I can store a single row for a day with 1440 columns. I have to report both graphical and tabular data for a year, month, week, day .... 5 minutes. What should be my approach to do so? Rows or columns. Regards
ASK SQL Server Central runs by you voting. For all helpful answers below, please indicate this by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate this by clicking on the check mark next that answer.
The best way is to store consuption in one column, and time in another column. 1440 columns it is not good idea... How would look that query? How you think to sum values for one week for example in that case? How you will store data for another day? Don't do that :) When you creating reports, you simply use one column for data, and another for timeline values in chart. You can sum values for hour, day, week, month, year... you can do all calculation if you choose two columns model.
On the data volume question, 262 million rows is not 'too much' for SQL to handle, although you could implement a strategy that stores aggregated values for each (or a selection) of the time periods. For example storing at '5 mins' and 'daily' will allow you to report on all those time periods. But then you may find that if 80% of the queries are at '60 mins' then aggregating to that may also make sense. You could also do the same for meter groupings too. Once you are sure that the base data (1 min) is either not going to ever change, or is not needed then you can archive (or delete depending on your requirements).
Dear Kev Thanks for your previous reply. I am trying with 100 million rows only (not 262 million which is actual number) but my queries are too slow to respond. My Table is Index - Identity MeterID - Int Time - Datetime Reading - Numeric (18,1) Controller - Int Site - Int Region - Int There are no keys. The Meter belongs to controller which belongs to Site which belongs to a Region. I can get rid of Controller,Site and Region columns but then there would be joins. My queries can be for a Meter,Controller, Site OR a Region for a specific date range and I have return the readings. None of my columns are unique . Would you please suggest if I can add some Indexes (Clustered - Non clustered) and if yes what could they be. There shall not be any delete or update on this table. Please help
17 People are following this question.
