Many times it is valuable to have a different key generator other than the internal tools built natively in your database engine. One key reason is simply performance. When you are using system assigned keys generated by your database engine it requires multiple round trips to store data that is normalized in any fashion. When you have related data where the parent record may have many child records, before you can save a single child record you must first save the parent record, capture the assigned key, and then save all the child records using the new key.
As you data structure becomes more normalized, this round trip requirement continues to grow more, and performance degrades. You can still find batch methodologies, but they are more difficult as you reach better forms of normalization that are essential for high performance, high volume database systems.
What if you could have an authoritative key generator, and apply the keys to your data so that you can submit everything in batch modes, while still maintaining the necessary Primary Key/Foreign Key requirements of your schema? Here is one solution I have found works extremely well under high levels of activity.
I like having a central authority for key assignment, much as you get with IDENTITY columns. So, what about having your database continue to assign keys? However, instead of generating keys one at a time, we can generate keys in a batch. What I have used is a batch key generating implementation in the database where the client requests a configurable number of keys. The keys granted to the client may not be used by any other process, and if not implemented will be dropped on the floor.
In this fashion, the client, when it has data to add to a table, will simply use one of the keys from the pool it holds. If the pool is empty, it requests a new batch of keys, and works through that batch until the keys are all used, or the client process terminates.
If you use a key pool for multiple tables, you can assign all of the primary keys and foreign keys for your data prior to adding it to the database. Key management can’t get any easier, and performance is greatly improved by reducing the need for round trips to assign key values for individual records. You already know what the key is, because you assign it in your client.
This kind of key generation is easy to implement in any SQL engine by using a table to store the last key value for each table, and the number of keys to include in a batch.
If you have a database that you know will experience periods of high transaction volume, this is a great performance boost, with a simple implementation strategy.
Cheers,
Ben