Re: Sequence of columns in primary key

(gilles27@xxxxxxxxxx) writes:
> I'm sure Gert-Jan is correct in what he states, however because the
> most likely filtering on OrderLines is by Ledger and OrderNumber, those
> two should come before OrderLineNumber in the primary key.
>
> I could ask the customer to demonstrate their findings but my pride
> prevents me from admitting to them that I don't know what they mean by
> "reduced index reads by a factor of 5". :-)

You don't? Good, then we are two!

But OK, let's say you have this query:

SELECT * FROM tbl WHERE Ledger = @ledger AND OrderNumber = @nbr

The key reorder certainly gave a drastic relief on the poor index. Rather
than wearing thing on the poor index pages, you get a table scan instead.
Or at least scan for a Ledger.

Anyway, what it's important in the end is wallclock time. The total number
of reads is a good indication, when wallclock time varies due to other
queries, blocking etc. But index reads alone is not of interest.

Ah, there is of course the interesting issue of what happens at INSERT.
Now, with the proper PK, and if this key is clustered, thanks to the Ledger
column, there will be six hot spots in this table, and there will be
plenty of page splits. If you change the order, the picture will be
different, and INSERTs will spread all over. Both will lead to
fragmentation. If you want to avoid this, best is probably to keep the
PK non-clustered and cluster on OrderDate, or even on an IDENTITY column.
Then again, if there are proper maintenance windows for frequent reindexing,
this is not an issue.

> I need some of way checking that my changes to primary keys are
> actually having a positive effect, if I could compare the number of
> index reads before and after modifying the table, that would help.

Rather take typical queries like

SELECT * FROM tbl WHERE Ledger = @ledger AND OrderNumber = @nbr


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

.

Relevant Pages

  • Re: Sequence of columns in primary key
    ... Ledger is likely to have a maximum of ... OrderNumber a maximum of 10 million and OrderLine up ... the order in a primary key should rather reflect the logical ... CONSTRAINT pk_bho PRIMARY KEY (coucode, ...
    (comp.databases.ms-sqlserver)
  • Re: What should I set for Primary Key
    ... A primary key is something that uniquely identifies a record. ... Two tables are Bank and Ledger. ... My queries are running against both tables, such as Match Query, ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Copying a field contents from one table to another
    ... Design your tblLedger to have its own primary key, ... A typical form design for this would be Customer record on ... the main form and related Ledger records in the subform. ...
    (microsoft.public.access.gettingstarted)
Loading