April 3, 2008 at 10:00 am
Hi,
We have a system that generates automatic letters for clients, with an entry in the database for each letter, with a unique letter ID - the PK (not an identity), and the clientID
After the INSERT, only 1 field, the sent date, should ever be updated, and that is only once.
Generally, each letter record created will only be accessed 3 or 4 times during the creation of the letter, and only very occasionally after that, however a list of letters sent to the client will regularly be pulled off.
We have approximately 20,000 clients, who may have any number of letters relating to them.On average 500 letters are produced a day (Not all of which go to the Client!!)
Which column would be best to choose for the Clustered Index, the Letter ID or the Client ID?
April 3, 2008 at 11:04 am
peter.anderson (4/3/2008)
Hi,We have a system that generates automatic letters for clients, with an entry in the database for each letter, with a unique letter ID - the PK (not an identity), and the clientID
After the INSERT, only 1 field, the sent date, should ever be updated, and that is only once.
Generally, each letter record created will only be accessed 3 or 4 times during the creation of the letter, and only very occasionally after that, however a list of letters sent to the client will regularly be pulled off.
We have approximately 20,000 clients, who may have any number of letters relating to them.On average 500 letters are produced a day (Not all of which go to the Client!!)
Which column would be best to choose for the Clustered Index, the Letter ID or the Client ID?
Since Clustered indexes are good for range searches, I'd probably make the clustered index the ClientID (or - the clientID would probably be the "leading edge" of the clustered index). This will create some amount of page splits, which will then mean occasional table/clustered index rebuilds, but the volume you're mentioning doesn't sound so phenomenally high as to make that problem, especially if you use a 65-80% fill factor (depending on how big the rows might be).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 4, 2008 at 3:44 am
Thanks Matt - I was leaning that way, but having only started reading on it yesterday thought some experienced advice would be useful, especially as most of the articles seem to contradict themselves in this instance!
Pete
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply