Re: Sequence Numbers as Primary Keys

gordonh_at_acslink.net.au (Gordon E. Hooker) writes:
>[...]
>Test it and see, if performance is enhanced use it...
>[...]

Not so fast! If performance is already adequate, you shouldn't go messing around with non-"business" columns just to make it snappy. The overhead of having the database deal with multi-column primary keys must be weighed against the overhead of having people deal with the decreased "obviousness" of the database structure and associated applications.

We have a few cases where we suspected a multi-column key to be the main culprit in a long-running query (anywhere from five to twenty minutes). We debated putting in some "data optimization" to try to make it faster, but decided against it. We have already used arbitrary numbers as the primary key in a number of tables, and we've come to regret the added maintenance hassles of having the "real" data just "tag along" with the key. When we migrated from Oracle V6 on OS/2 1.3 (16-bit) to Oracle V7 on OS/2 Warp (32-bit), we were amazed at how much better the new system dealt with the query. It runs MUCH faster now (less than 3 minutes), without any intervention on our part to tweak things.

Don't fall into the trap of "if it's faster, use it." Remember that maintenance takes time too. Sure, if performance improves from unusable to usable, then use it. If performance is enhanced from uncompetitive to competitive, use it. If performance increases from acceptable to snappy, think about it.

• === === === = = = === === === === = = === = = = === = = === = # Alan Anderson # Ignorance can be fixed, but stupidity is permanent. # (I do not speak for Delco Electronics, and DE does not speak for me.)