Back to newsletter 040 contents

Why is a long primary key bad for performance?

There are always lots of new things to find out about Java performance, even for the performance experts. I recently had the pleasure of sitting through Carol McDonald's Sun tech day presentation on J2EE performance. Even despite having a power cut causing a longish delay in the middle, this was easily the best presentation of the two day event.

One point she made was that long primary keys were bad for performance. I can easily believe this but it was new to me, so later I asked Carol for clarification on why this should be. Back she came with a detailed listing of the performance effects of primary keys, and I'm delighted to share these details with you. The following tips and references are all courtesy of Carol.

From "Bitter EJB"

"a longer key will require more computation to hash or compare than a shorter primary key" chapter 7 page 234.

What Makes a Good Primary Key?

Good primary keys are essential to good database design. They let you query and modify each table row individually without changing other rows in the same table. When you evaluate candidates for a table's primary key, follow these rules:

• The primary key should consist of one column whenever possible.
• The name should mean the same 5 years from now as it does today.
• The data value should be non-null and remain constant over time.
• The data type should be either an integer or a short, fixed-width character.
• If you're using a character data type, the primary key should exclude differential capitalization, spaces, and special characters, which might be difficult to remember.

Tips on Optimizing SQL Server Indexes

Keep the "width" of your indexes as narrow as possible. This reduces the size of the index and reduces the number of disk I/O reads required to read the index, boosting performance.

If possible, try to create indexes on columns that have integer values instead of characters. Integer values have less overhead than character values.

Don't use FLOAT or REAL data types for primary keys, as they add unnecessary overhead and can hurt performance.

Indexes on narrow columns are preferable to indexes on wide columns. The narrower the index, the more entries SQL Server can fit on a data page, which in turn reduces the amount of I/O required to access the data.

SQL Server Performance Tuning for SQL Server Developers

Reduce the size of the keys, thus decreasing read I/O during the join process, and increasing overall performance.

My thanks to Carol for answering my question this month.

The JavaPerformanceTuning.com team

Back to newsletter 040 contents