How Do I Implement High Volume Key Assignment?

Today I am sharing some specifics for building your own key assignment implementation, allowing you to allocate multiple key values in advance of actually creating records. When you use the database engine to create unique keys for you data you usually follow the following sequence.

1. Add data to a table
2. Capture the assigned key value
3. Return the key value to the client so it can be used to identify the record or assign a foreign key value to child related records

Using this technique of pre-capturing a range of keys for a process, assuring that no other process will assign the same key values, you can add data at your own pace, but not have to make round trips to the database per record added in order to know the assigned key value. The process looks as follows

1. Acquire a range of keys. For example, keys 101 through 200

That process has been completed as an ACID transaction. The only thing blocked was some other process wanting to acquire a range of keys. That process is quite light weight as you will see later. Now, you simply use the range of keys assigned to your process as needed. So when adding a record the process now looks as follows:

1. Create a new record to be added to the database, assigning the next key from the range assigned to your process
2. Add the data to a table

You do not have to perform any other actions to know what the key was. Also, if you are adding related data, you already know the foreign key value, because you just assigned it from your pool of key values.

Before I get into the mechanics, let me share that this process does not work well in a stateless system where there is no in memory resident values. You need to have a singleton object holding the keys so that the batch may be consumed fully as individual requests come in on different threads. You client code must be thread safe in order to take advantage of this capability.

Here is a very simple implementation of this process. First I start with a table to track key values and populate it with some sample data. Note the TABLE_NAME column. This allows me to maintain keys for multiple tables. You could get more complicated and add schema, or even database. Second is the CURRENT_KEY which maintains the next key value to assign. Finally, there is a BATCH_SIZE column which determines the size of each batch for when the consumer requests a set of keys. Each table may have its own batch size. Here is the table definition

CREATE TABLE Keys
(
    TABLE_NAME  SYSNAME NOT NULL
   ,CURRENT_KEY BIGINT  NOT NULL
   ,BATCH_SIZE  BIGINT  NOT NULL
   ,CONSTRAINT PK_Keys PRIMARY KEY CLUSTERED (TABLE_NAME)
)
GO

INSERT INTO KEYS (TABLE_NAME, CURRENT_KEY, BATCH_SIZE) VALUES
 ('SHOPPING_CART', 2182811, 100)
,('SHOPPING_CART_ITEM', 12938127, 300)
GO

Now I create a simple stored procedure to get a range of keys. To use the stored procedure you simply call it with the name of the table for which you need keys and output variables for the first value and the last value.

CREATE PROCEDURE prGetKeyRange 
(
    @TABLE_NAME  SYSNAME
   ,@BEGIN_RANGE BIGINT OUTPUT
   ,@END_RANGE   BIGINT OUTPUT
) 
AS

    DECLARE @KEYS TABLE
    (
        BEGIN_RANGE BIGINT
       ,END_RANGE BIGINT
    )

    UPDATE Keys SET 
        CURRENT_KEY += BATCH_SIZE
    OUTPUT  DELETED.CURRENT_KEY
           ,INSERTED.CURRENT_KEY
    INTO    @Keys
    FROM    Keys WITH (ROWLOCK)
    WHERE TABLE_NAME = @TABLE_NAME

    SELECT @BEGIN_RANGE = BEGIN_RANGE
          ,@END_RANGE = END_RANGE
    FROM   @KEYS

    SELECT  *
    FROM    @KEYS

GO

I have written the stored procedure to be used in two different ways. You can call it with output parameters, which is the most efficient method of getting scalar values from a stored procedure.

DECLARE @BEGIN  BIGINT
DECLARE @END    BIGINT
EXEC prGetKeyRange 'SHOPPING_CART', @BEGIN OUTPUT, @END OUTPUT
SELECT @BEGIN, @END

The procedure also returns a table with the start and end values for keys. If you simply want to use the table method you can just provide bogus values for the OUTPUT parameters as shown below.

EXEC prGetKeyRange 'SHOPPING_CART', 1, 1

This is s tool that can be tuned over time. You can tweak the number of keys assigned in a batch to match use. If your Shopping Cart table has a 1 to 3 ratio with the Shopping Cart Items table, then set the batch size for Shopping Cart Items to three times that of the batch for Shopping Cart.

I hope that clarifies the problem being solved, some of the issues you will find with implementation, and an example to get you started in designing something that works for you.

Cheers,

Ben