0

I have a very large table that will just continue to get larger. It contains hourly interval meter usage data for around 22,000 meters.

So, everyday 22,000 * 24 hours = 528,000 records are created. The current plan is to load new interval data into the table on a monthly basis.

One problem I'm having is that there's really no unique identifier. So, I'm not sure how to best setup the Primary Key.

Here's an example of data for 24 hours for two meters:

ServiceLocation MeterNumber IntervalDay IntervalHour Demand
111111 22222 2013-01-21 1 0.01
111111 22222 2013-01-21 2 0.01
111111 22222 2013-01-21 3 0.01
111111 22222 2013-01-21 4 0.01
111111 22222 2013-01-21 5 0.01
111111 22222 2013-01-21 6 0.01
111111 22222 2013-01-21 7 0.02
111111 22222 2013-01-21 8 0.02
111111 22222 2013-01-21 9 0.03
111111 22222 2013-01-21 10 0.03
111111 22222 2013-01-21 11 0.03
111111 22222 2013-01-21 12 0.04
111111 22222 2013-01-21 13 6.55
111111 22222 2013-01-21 14 6.39
111111 22222 2013-01-21 15 7.70
111111 22222 2013-01-21 16 8.52
111111 22222 2013-01-21 17 8.85
111111 22222 2013-01-21 18 6.88
111111 22222 2013-01-21 19 5.90
111111 22222 2013-01-21 20 5.90
111111 22222 2013-01-21 21 5.90
111111 22222 2013-01-21 22 6.06
111111 22222 2013-01-21 23 5.40
111111 22222 2013-01-21 24 5.73
555555 33333 2013-01-21 1 0.01
555555 33333 2013-01-21 2 0.01
555555 33333 2013-01-21 3 0.01
555555 33333 2013-01-21 4 0.01
555555 33333 2013-01-21 5 0.01
555555 33333 2013-01-21 6 0.01
555555 33333 2013-01-21 7 0.02
555555 33333 2013-01-21 8 0.02
555555 33333 2013-01-21 9 0.03
555555 33333 2013-01-21 10 0.03
555555 33333 2013-01-21 11 0.03
555555 33333 2013-01-21 12 0.04
555555 33333 2013-01-21 13 6.55
555555 33333 2013-01-21 14 6.39
555555 33333 2013-01-21 15 7.70
555555 33333 2013-01-21 16 8.52
555555 33333 2013-01-21 17 8.85
555555 33333 2013-01-21 18 6.88
555555 33333 2013-01-21 19 5.90
555555 33333 2013-01-21 20 5.90
555555 33333 2013-01-21 21 5.90
555555 33333 2013-01-21 22 6.06
555555 33333 2013-01-21 23 5.40
555555 33333 2013-01-21 24 5.73

And an example query that we would execute is:

SELECT
    IntervalDay,
    SUM(Demand)
FROM
    LoadData
WHERE
    MeterNumber = '33333' AND
    IntervalDay >= '2013-01-21' AND
    IntervalDay < '2013-01-22'
GROUP BY
    IntervalDay

And a lot of times we'd aggregate using the IntervalHour field too, or maybe not specify a MeterNumber in the WHERE clause to get all meter usage.

I'm having trouble figuring out what type of primary key I should have for this large table?

It currently has a non-clustered Primary key on the ServiceLocation, MeterNumber, IntervalDay, and IntervalHour fields. Does this make sense? There is no clustered index at this time.

The ServiceLocation is like the ID for the physical property, so it will never change for a location. However, the MeterNumber can change (ex, meter fails and needs replaced), but there will only ever be one MeterNumber at a ServiceLocation.

6
  • Just curious, why the range search IntervalDay >= '2013-01-21' AND IntervalDay < '2013-01-22' and not just the equality search IntervalDay = '2013-01-21' instead?
    – J.D.
    May 11 at 19:53
  • 1
    It's just an example query that had an actual range and I just modified it that way. Really not important for the question.
    – anon
    May 11 at 20:29
  • Ok cool, just checking. I'm sure you know that could affect performance in the cases where you'd only want a single day's worth of data then.
    – J.D.
    May 11 at 21:57
  • Also what is your clustered index on the table currently?
    – J.D.
    May 12 at 0:53
  • There actually isn't one. This table was created by someone else, so I don't know the history but there is currently only the non-clustered index on those 4 fields.
    – anon
    May 13 at 0:06

1 Answer 1

0

The primary key should be based on business logic - how do you uniquely identify a row in your table? Probably ServiceLocation, MeterNumber, IntervalDay, IntervalHour, that's most of your columns so I would probably make it the clustered index too - otherwise you'd be storing most of the table twice.

Indexes you create to support queries are based on those queries and the data they need to use. If you want to make as few indexes as possible then look at what selectivity you can get from common filters - if all of your queries use a small range filter for IntervalDay then having that as an index on its own is probably fine. If you need to do bigger ranges and you need other filters to be used to get the data set down to a manageable size then you'll want to add those columns to the index too. Remember the Golden Rule of Indexing : columns that will be used with range predicates should appear after columns that will be used with equality filters (so IntervalDay goes last).

1
  • When you say "probably make it the clustered index too" do you mean by creating the primary key like: CONSTRAINT [PK_MyKey] PRIMARY KEY CLUSTERED this?
    – anon
    May 19 at 22:05

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.