The table does have an identity key (current CI), but it’s barely used to query. Because the natural key is not ever-increasing I’m afraid of insert performance, fragmentation or other problems that I don’t foresee now.
The table is not wide, with just a few columns. It has about 8 million rows and bringing our site to a halt during peak times. (+1000s concurrent users). The data is not easily cacheable, because it is quite volatile and essential that it’s up to date.
There are a lot of reads on one column of the natural key, but also quite active inserting and updating. Say 8 reads, vs 1 updates vs 1 inserts.
Id (PK) int UserId* int Key1* varchar(25) Key2* varchar(25) Key3* int LastChanged datetime2(7) Value varchar(25) Invalid bit * this combination is the natural primary key
I need to query the most of the time on:
- All rows for one UserId (most queried)
- All rows for a list of UserIds (a lot of rows)
- All rows for a list of UserIds with Key1 = X
- All rows for a list of UserIds with Key2 = X
- All rows for a list of UserIds with Key1 = X and Key2 = X
I know the final answer is always “profile it”, but we are quite on a time constraint here so any guidance or experienced opinions in advance would really be appreciated.
Thanks in advance,