0

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
  • If the hourly values can't be more than 32,767, you could use SMALLINT – Scott Hodgin May 14 '18 at 9:10
  • 1
    Why not TINYINT? – Vérace May 14 '18 at 11:47
  • We really need an example of the query you are trying to improve in order to help. Ideally, including the execution plan. – Jonathan Fite May 14 '18 at 12:28
  • I see I didn't explain myself well, let me expand – aseques May 14 '18 at 13:14
1

If the values don't change too often, I would use and Slowly Changing Dimension (Type 2) approach. In this case, you will store a new row only when your value changes. This is mostly common used on Data Warehouses to track historical data.

In a nutshell:

1) Change your table to something like this:

CREATE TABLE dbo.CustomerHistory (
CustomerHistoryKey int identity (1,1),  -- PK and surrogate key
CustomerId int NOT NULL,
Value int,
StartDate datetime NOT NULL,
EndDate datetime NOT NULL 
)

ALTER TABLE dbo.CustomerHistory ADD  CONSTRAINT PK_CustomerHistory  PRIMARY KEY CLUSTERED 
(CustomerHistoryKey)

CREATE UNIQUE NONCLUSTERED INDEX [BK_CustomerHistory] ON dbo.CustomerHistory 
(CustomerId, StartDate)

CREATE UNIQUE NONCLUSTERED INDEX [IX_CustomerHistory_currentRow] ON dbo.CustomerHistory 
(CustomerId, EndDate)
WHERE EndDate = CAST('99990101' AS datetime)
  • Avoid using nulls on StartDate and EndDate. Instead, choose a lower and higher date value, such as 19000101 and 99990101

2) Continue running your routine every hour to check if Value was changed, but only add a new row for those customers with changes. To add a new row, do:

  • DECLARE @ChangeDate datetime = GETDATE()

  • Set the current row EndDate equals to @ChangeDate

  • Add a new row with the updated Value with StartDate equals to @ChangeDate and EndDate equals to '99990101'

How it works:

  • You will store much less data if your values doesn't change to often

  • If you need to create a relationship pointing to a specific point in time, use CustomerHistoryKey

  • If you need to check one value in time, you can search by CustomerId and time >= StartDate and time < EndDate

Check the following link for an quick overview: https://en.wikipedia.org/wiki/Slowly_changing_dimension#Type_2:_add_new_row

  • I get a whole new record per customer each day (with different values per hour), still, didn't know about this Slowly Changing Dimension, they seem very interesting – aseques May 14 '18 at 13:14
  • Suppose for custid=1,this moment value is 5 and next hour also value is 5 then guess I need to store both value. Also I think History table is not require in this case.@Aseques , need to explain his requirement and query. – KumarHarsh May 15 '18 at 3:38
0

Normalizing your table would be an improvement:

CREATE table #timevalues (
  date_hour datetime2,
  customerid int,
  hour_val int
)

It would allow to simplify your code too. Compare your formula for the average per hour for the previous week to this:

SELECT datepart(day,date_hour) d,
       datepart(hour,date_hour) h,
       avg(hour_val) a
  from #timevalues t
  where date_hour between dateadd(day, DATEDIFF(day,0,GETDATE()), -7)
                      and dateadd(day, DATEDIFF(day,0,GETDATE()), 0)
  group by datepart(day,date_hour), datepart(hour,date_hour)
  order by datepart(day,date_hour), datepart(hour,date_hour);
  • Sorry, but I don't understand where would be the per hour values stored in your example, also what is the advantage of datetime2 vs date (I only need the date)? – aseques May 14 '18 at 9:03
  • To determine the hour. Per hour values go into hour_val. – Gerard H. Pille May 14 '18 at 9:30
  • @Aseques.I go with this design. What all kind of query do you have ?what kind of output is required ?This is important question.In order to denormalize you can also keep one extra column "dateinsert date". – KumarHarsh May 14 '18 at 11:05
  • @GerardH.Pillem I expanded the original post, but basically I have 24fields filled each day per customer. – aseques May 14 '18 at 13:20

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.