We have a table that stores a value per each hour per each per day per customer. A simplification of the table would be like this (removed some extra fields and the indexes):
CREATE table #timevalues (
dateinsert date,
customerid int,
hour0 int,
hour1 int,
....
hour23 int
)
The problem is that this table is huge, and altough we have an index with the dateinsert and customerid that allows us for a fast seek of the record, retrieving the record also takes time. Our issue is more with the speed and being future proof than with the space. What would be a recomendation to improve this? We were thinking about replacing the column format to store a json instead of the fixed columns, but the result would be less optimal I guess. We regularly need to get stuff like:
- Daily totals customer XXX in last month
- Average per hour per day
SMALLINT
– Scott Hodgin May 14 '18 at 9:10TINYINT
? – Vérace May 14 '18 at 11:47