When talking about using natural keys the last couple of days, the most frequent comment against this technique is the issue of performance. I have databases where using natural keys outperforms using sequential, system assigned keys. Today I am going to posit a theory as to why that is so (other than I am just fooling myself, or my measurements are not adequate).
Let’s start with one concern raised about using natural keys with multiple columns as slowing down joins. This is not necessarily true. Yes, the number of columns in the keys do matter. However, with 64 bit machines the difference is difficult to measure, it is so small. Also, as you will see later on, the use of the natural key can actually speed up queries due to fragmentation.
Let’s take one fact all can agree on, at least with SQL Server. Microsoft has optimized the data engine to perform inserts faster when the clustered index is based on a sequential key value. Data is physically stored (think clustered index here, where the data pages are physically stored) in a data structure known as a b-tree. If you don’t know what a b-tree data structure is, you will need to read up on it in order to understand the rest of this editorial. Microsoft has optimized the node balancing of the b-tree, when data requires the tree to be re-balanced to make room for additional data, to work fastest from the right most child node of a b-tree (sequential numbers always fall on the node farthest to the right in the b-tree).
If your database has frequent inserts, and the inserts are not performed in a single logical batch, then data becomes fragmented when inserted always to the right most node. If a lot of inserts are happening on many threads, and your inserts are more granular, then your data for a logical object may be fragmented across many pages, or even extents. Using the sample schema from yesterday’s editorial, this means that when I need to retrieve a complete cluster object, by the time I go through the normalization of the Cluster Group and Cluster Group Items, I may have to hit the disk many times to get to the physical data for the detail records. What this means is that using sequential keys, writes are faster, but, depending on how the detail records are inserted, reads may be much slower.
Now, let’s use the same table definitions, with the modification of creating the clustered index on the natural keys. One advantage of using the natural keys is that you can externally assign keys for detail records in some cases, while still using a number.
CREATE TABLE Cluster (
ClusterID INT NOT NULL IDENTITY(1,1)
,CONSTRAINT PK_Cluster PRIMARY KEY CLUSTERED (ClusterID)
)
CREATE TABLE ClusterGroup (
ClusterGroupId INT NOT NULL IDENTITY(1,1)
,ClusterID INT NOT NULL
,ClusterGroupNumber INT NOT NULL
,MaxItemsAllowed INT NOT NULL
,CONSTRAINT PK_ClusterGroup PRIMARY KEY CLUSTERED (ClusterGroup, ClusterGroupNumber)
,CONSTRAINT FK_ClusterGroup_Cluster
FOREIGN KEY (ClusterId)
REFERENCES Cluster (ClusterId)
)
CREATE TABLE ClusterGroupItem (
ClusterGroupItemId INT NOT NULL IDENTITY(1,1)
,ClusterId INT NOT NULL
,ClusterGroupId INT NOT NULL
,ItemNumber INT NOT NULL
,CONSTRAINT PK_ClusterGroupItem PRIMARY KEY CLUSTERED (ClusterId, ClusterGroupNumber, ItemNumber)
,CONSTRAINT FK_ClusterGroupItem_ClusterGroup
FOREIGN KEY (ClusterGroupId)
REFERENCES ClusterGroup (ClusterGroupId)
)
Note that I have not removed the Identity columns. I like having a single value system assigned unique identifier in every table for the purposes of logging. I can even create alternate keys on these columns. That’s not really a requirement. I just like to have both worlds. So, now I just need to determine on what to base my clustered index.
Because you now have a clustered index on each table based on the value of the parent, or even the grandparent in the case of ClusterGroupItem, all of the physical data is stored contiguously on the disk. Disk reads may be drastically reduced because the desired data is bunched together. Think of it like this: If you have two sets, and want to join them together, it is a lot faster if the sets are already sorted in the same order. Well, sorting data is the end effect of a clustered index. What you can take away from this behavior of clustered indexes is that natural keys can out-perform sequential keys when performing reads. This is especially true when a logical group of data such as my Cluster Group Table as a batch. When records are inserted one at a time, and there are many threads doing the same thing for other groups, then your data is fragmented immediately. Yes, you have an alternate key in our schema from yesterday to enforce the business rules, and you can join on that. However, the only thing sorted in the natural key order is the alternate key index. So you must do an index read and a table read to get things together. If the clustered index is based on the natural key, all is optimized.
One thing you may have picked up on is that I have moved the focus from primary keys and foreign keys to the Clustered Index. The table can have unique keys on the sequence or the natural key. But, it can have only one be the clustered index. The one you choose is what is most significant, and drives your design.
If my argument is so compelling, then why don’t we just do it all of the time? The downside of the natural key design occurs during inserts. The natural key data will not always be added to the tail end of the b-tree. In fact, it can be inserted all over in the tree, depending on how your natural key is based. This results in re-balancing of the b-tree that is not as optimized, and may impact many more nodes than a sequential insert at the end of the tree. We have played with using fill factor to reserve extra space in the nodes for future inserts. Of course, the fill factor saves space in the table without data. That means when you read it back, there are less records in the page (potentially) and you have to read more pages to get your data. The plus side is that, although you may read more pages using fill factor, it still can be much less than using sequential keys.
My un-scientific tests have led me to believe that the highest performing schema for all situations is to use a sequential index, and utilize an ORM that writes logical groups of data I a single batch, not one record at a time. So, when I write my 20 Cluster Group Item records for a Cluster Group object to the database, only that thread has access to the table, and is blocking all other transactions, so my records are stored contiguously. Most systems don’t work this way.
When records are related in logical groups, then using natural keys with appropriate fill factor applies seems to provide the most consistent performance for both read and write activities, regardless of writing batches of data, or single records one at a time.
I don’t know that this helps you much in forming an opinion. What I hope it does is help you understand why different people have had different experiences that are opposite. This is an important decision, and it does impact database performance, so it really does matter. My wish is that you won’t simply accept a position blindly, but take into consideration regarding the data usage on your system. How does data get created, updated, deleted, and retrieved? Optimization, in my opinion, relies on the answer to these four questions.
Bring on the different opinions. Your comments are highly valued, especially if you don’t agree! Until I have time to put together a fair, scientific, comparison, I don’t have a dogmatic answer.
Cheers,
Ben