1

I have tables where the primary key is something like this:

-StoreID
-BusinessDate

Typically, there is one row for every store (~100) for every single day. One row for each store is inserted on a daily basis and is never updated and almost never deleted.

I think is would be better if the field order would be:

-BusinessDate
-StoreID

My reasoning is that the sort order of this key would more naturally follow the typical insert order. I'm wondering if I should change the order of the fields in the key.

So far performance has been acceptable. Will the current field ordering cause serious performance problems in the future if I don't change anything?

Improve this question

1 Answer 1

Reset to default
1

The order of columns in a primary key does not affect the insert performance, since the combination of the values in the two columns of the primary key are meant to be unique.

However the order of index impacts the performance of SELECT queries. In the new order you will have a greater and increasing range of values (dates) across which the index is clustered (dates) as compared to clustering around 100 stores.

If most of your queries are focused on a single store with the new ordering your queries will be slower.

Improve this answer
4
  • I think overall you've got this right, but the first statement seems off. Both values are not unique, but the combination of the values is and yes, that does affect the order in which the inserts are done because it must sort them into the appropriate place within the index (clustered or not).
    – Grant Fritchey
    Mar 6, 2012 at 11:43
  • @GrantFritchey thanks for pointing that out, I have updated my statement to reflect that
    – Stephen Senkomago Musoke
    Mar 6, 2012 at 11:49
  • I still think your first statement is off. The order will affect how and where the data are sorted. It probably won't affect insert performance, though.
    – Dave Markle
    Mar 6, 2012 at 12:56
  • Well... it could affect insert performance too. If the structure you have encourages page splits, you're likely to see poorer performance. So, for example, the structure with StoreID as the leading edge is more likely to suffer from page splits than the one with the Date on the leading edge. So performance could suffer because of that, maybe.
    – Grant Fritchey
    Mar 6, 2012 at 13:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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