Sorry for the late response, I'll try to anwser the questions now.
[/quote]
ScottPletcher (1/4/2013)
Do you have only a few clients with huge numbers of orders each?Or for a large number of clients, some with large numbers of orders, some with small numbers of orders?
A large number of clients with very unbalansed numbers of orders.
In the first case, your current clustering is likely fine.
In the second case, you likely want to go with the more "standard" clustering for order systems of:
Client clustered on Clientid (identity) Clientid.
ClientOrder clustered on Orderid (identity).
ClientLine clustered on the combination of ( Orderid, LineId ).
That drastically reduces fragementation but still keeps all info for the same order together.
You can use a nonclustered index on ClientOrder.Clientid for the relatively rare cases when you need to work with all orders for a given client.
Working with all orders is not rare, but common or even usual.
Number of times a row is accessed is far higher than the number the number of writes this is especially so for the clients with a large number of orders, they 'visit' far more frequently.
The system has been running very succesfully for over 10 years, with larger and smaller sites. So in reality the performance is good.
I only recently (just before Xmas) that there was a 'design-flaw', which was that for large clients, all in between pages would be filled for 50 percent. Allthough not a problem, it was an oversight from me and that bothers me. This situation must ocure with others as well, my hope was to learn from others.
Thanks,
Ben