Posted by Gert-Jan Strik on 10/25/05 23:24

I created a large table (1306677 rows, 151888 KB) with the
characteristics as originally described for Ledger, OrderNumber and
OrderLineNumber. I then tried different primary key column orders (all 6
combinations). After reindexing, each combination resulted in the same
number of pages and same index depth. In other words: there will be no
performance difference for exact primary key matches.

Maybe the table was very fragmented and had much unused space. Changing
the primary key column order would effectively reindex the table, and
thus remove fragmentation and restore the original fill factor. This
could easily decrease the index depth and result in the performance
gain.

Gert-Jan


gilles27@talk21.com wrote:
>
> Our review of the primary keys was triggered by one of our customers
> complaining about the performance of the database. They claimed to have
> "re-sequenced the primary key which reduced Index reads by a factor of
> 5". Do you have any idea as to how they arrived at this conclusion?

[Back to original message]