Performance Testing

Three different examples highlighting three different aspects of the performance issue are tested.  As you will see, neither synthetic nor natural keys win in all cases.  Note: the examples shown here are ‘corner cases’, designed to highlight performance differences.  In real world databases, the differences you see are likely to be smaller than those shown here.

Test Case 1: OLTP Data Insertion

A sample employee table is created, using SSN as primary key.  Two client sessions ( on separate machines) are simultaneously started, each inserting 100,000 rows of random test data.    The test is then rerun with an IDENTITY column as primary key, and a unique constraint added on SSN.

Test Table 1: Natural Key

CREATE TABLE Employees

(

      SSN               CHAR(9) PRIMARY KEY,

      Firstname         VARCHAR(50),

      Lastname          VARCHAR(50),

      Date1             DATETIME NOT NULL DEFAULT GETDATE(),

      Int1              INTEGER NOT NULL DEFAULT 0,

      Char1             Varchar(50),

)

Test Table 2: Surrogate Key

CREATE TABLE Employees

(

      EmployeeID        INT IDENTITY PRIMARY KEY,

      SSN               CHAR(9) UNIQUE NOT NULL,

      Firstname         VARCHAR(50),

      Lastname          VARCHAR(50),

      Date1             DATETIME NOT NULL DEFAULT GETDATE(),

      Int1              INTEGER NOT NULL DEFAULT 0,

      Char1             Varchar(50),

)

Test Results (average of three runs)

·         Natural Key Insert:          39.1 sec.
·         Surrogate Key Insert:     46.5 sec.   (19% slower)

Before I ran this test, I expected the vast majority of any performance differential would be due maintaining two indexes, rather than one.  However, when I reran it without the unique constraint on SSN, the difference was almost exactly half the original, meaning contention on the IDENTITY column is a significant factor.

Test Case #2: Narrowing a Wide Index

A query is used to join a child table to its parent by a 40-byte three-column foreign key.   The child table contains one million rows, and the parent 300K rows.   Key values are created randomly.  The test is then rerun using a 4-byte IDENTITY column to perform the JOIN.  To force an index seek (rather than an index scan) a WHERE clause is used to limit rows retrieved to 1% of the table total.

Table Schema:

CREATE TABLE Parent

(

      SurrogateKey      INT IDENTITY NOT NULL,

      NatKeyPart1       CHAR(32) NOT NULL,

      NatKeyPart2       INT NOT NULL,

      NatKeyPart3       INT NOT NULL,

      MiscData          VARCHAR(100),

)

CREATE INDEX ix_Parent ON Parent(ID)

CREATE INDEX ix_ParentParts ON Parent(KeyPart1,KeyPart2,KeyPart3)

CREATE TABLE Child

(

      SurrogateKey      INT NOT NULL,

      NatKeyPart1       CHAR(32) NOT NULL,

      NatKeyPart2       INT NOT NULL,

      NatKeyPart3       INT NOT NULL,

      ChildData         VARCHAR(100) PRIMARY KEY CLUSTERED

)

Test Results (average of three runs)

·         Natural Key Insert:          11.1 sec. (21% slower)
·         Surrogate Key Insert:     9.2 sec.  

Test Case #3: Join Elimination via Natural Key

The same data and methodology from Test Case #2 are used.  The difference here is that the query references only natural key values from the Parent table, rather than all columns.  This means when the natural key is used as the foreign key, no join to the Parent table is necessary; the query can be fully serviced by the Child table.

Test Results (average of three runs):

·         Natural Key Insert:          2.9 sec
·         Surrogate Key Insert:       9.2 sec.   (320% slower)

Conclusion

This article has hopefully demonstrated that the choice of natural vs. surrogate key is a complex issue, with no single answer that fits all scenarios.  Armed with the information above, however, you can make better decisions about which is the best solution for your own particular needs.