I'm working on improving database performance of my application and now it comes to the part of clustered index. I don't have experience with it so I would like to ask.
My application caveats
In my application I have the concept of Show.
This means that a user can create/open a Show and then it will access only the objects related to that Show (Actors, clothes, items, etc). So it is really commom to have queries using "...WHERE ShowId = X"
Also we do have quite a lot of inserts because of integrations (we have a job running daily that would get +/- 30k new rows to the database, all in the same Show) and deletions are not that commom... The user may delete a Show (which is really taking quite a long time).
The usage of the application is usually like:
- User creates a Show based on a "Master Show" (which copies a lot of data changing only the ShowId)
- User navigates around some screens to check this data
- User calls an algorithm that proccess the input of this show and generate a lot of outputs (30k+ inserts again, on the same show)
- User analyses these outputs
Based on that, I thought that the best clustered key would be on [ ShowId, id ] for every table. But then, reading this documentation, I have doubts if this wouldn't make it worse given the number of inserts or if, because the inserts are made on the same ShowId, it would be ok.
I don't have access to the PRD database nor to a copy of it for testing. I tested with the dev database but it is so small it didn't seem to have any impact at all.
Can someone help?