Browse Prior Art Database

Externalized Database Key Generation and Resolution Mechanism

IP.com Disclosure Number: IPCOM000168721D
Original Publication Date: 2008-Mar-21
Included in the Prior Art Database: 2008-Mar-21
Document File: 4 page(s) / 35K

Publishing Venue

IBM

Abstract

Disclosed here is a mechanism to minimize the number of database operations involved in the generation and resolution of keys, and to determine whether a key is present in a database table or not for insert or update.

This text was extracted from a PDF file.
This is the abbreviated version, containing approximately 54% of the total text.

Page 1 of 4

Externalized Database Key Generation and Resolution Mechanism

Names: Rohit Vats, George T Jacob

Problem Being Addressed

An Insert or Update operation on a database table usually goes through the below mentioned steps:
1. Identification of whether the operation is an insert or an update (1 DB Operation ).
2. Depending on the operation, either of the following is performed:
a. If the operation is an insert, a new unique key (primary key) needs to be generated for the record being inserted, & then the record needs to be inserted. This is usually done by the application logic, some of the common ways are:

i. Getting the max key from a database table & then forming the new key by incrementing the max key (1+1 DB Operation ).

ii. Having a separate table which keeps track of the next key to be used. Once a key has been used, the table is updated by the application with the next

available key (2+1 DB Operations) .
b. If the operation is an update, the record is updated (1 DB Operation) .

Thus, the total number of database operations required for insert & update operations are:

Insert: 3-4 operations

Update: 2 operations

This poses a huge performance bottleneck, especially in situations where bulk data is being loaded/updated.

For example , if 10,000 records are being inserted & 5000 records are being updated, the number of operations required would be:

10,000*4 + 5000*2
= 50,000 database operations

Proposed Solution

1

Page 2 of 4

0) Get CheckString (digest) of keys present

0) CheckString (digest)

DB Key Proxy

   3) Check if the
obtained numeric key
is present in DB

Database

Application


1) Get numeric key for natural key

2) Numeric key

4) True/False

5) Insert/Remove keys in the DB


6) Inform DB Key proxy of the key updates to let it update the CheckString (Digest)

The DB Key Proxy is the component which enables the externalization of:

Key generation, resolution and
Determination of the presence of a key in the database.

Generation/Resolution of keys

For this, a function would be used to generate a key based on a string input. The string input to the function would be the value of the natural key for the record, and it would return a numeric key which uniquely represents the input string (natural key).

Number keyGenerator (String uniqueColumnValue) {

return unique_number_representing_the_string_input
}

The keys returned by the function would be same every time the same natural key is passed to it, and it would be different for different natural keys.

Determining the presence of a key in the database

Once the numeric key value is obtained using the function as described above, the following approach is used to determine the presence (or absence) of a key in the database without the need of multiple database operation for each key:

Obtain a digest of all the keys present in the database.


1.

Algorithm 1 - Form the Check String (To be done on the DB)

The Check...