TIPS for High Performing OLTP Databases

There are some specific things you can do to optimize your OLTP database performance allowing it to scale with the ability to handle a large load of concurrent transactions. Today I’m going to start with some basic tips that work with just about any database engine.

One of the most effective optimization techniques I know of is to prefer INSERT actions over UPDATE OR DELETE actions. UPDATES are worse than DELETES. At least avoid them during peak OLTP processing loads. Under certain circumstances an UPDATE is implemented as two actions, DELETE followed by INSERT. So, if you can always INSERT, you can move the DELETE work to times where the load is lower.

Here is an example of how this can be accomplished. Say you have an online store with a shopping cart. Individuals can add multiple items to the shopping cart prior to completing a purchase action. They may want to add new items to the cart. They may change their mind and remove items from the cart. You want to persist the activities they are performing in their cart to the database incrementally as they add or remove items so the data is not lost should they become disconnected. You can do this using cookies, but the data is lost if they come back from another computer. So, you decide to persist this activity to a database for durability and a better user experience.

So, there are three actions that can take place in this scenario. The customer can:

1. INSERT new items to the cart
2. DELETE items from the cart
3. UPDATE some property for an item in the cart such as quantity, or discount code

The INSERT is not so bad. In fact that is the preference. The DELETE can be physically removed, especially if you are not following a command pattern where the user can un-delete. But what do you do for UPDATE actions? One scenario I have found really works in these situations is to use versioning. Using versioning tables actually works well with all actions. To keep things simple, for this scenario lets use the familiar example of a shopping cart table and a items table. They could look like the following:

CREATE TABLE Carts
(
   CartId INT NOT NULL IDENTITY (1,1)
  ,CustomerId INT NOT NULL
)

CREATE TABLE CartItems 
(
   CartItemId INT NOT NULL IDENTITY (1,1)
  ,CartId INT NOT NULL
  ,ProductId INT NOT NULL
  ,Quantity INT NOT NULL
  ,ItemCost MONEY NOT NULL
  ,DiscountCode VARCHAR(32) NOT NULL
  ,DiscountAmount MONEY NOT NULL
  ,ExtendedCost MONEY NOT NULL
)

Using this structure you can start a shopping cart, and add, update, delete items from that cart using standard INSERT, UPDATE and DELETE commands. With a little ingenuity, you can make the cart work completely with INSERT actions, and retain a history, should you wish to use a command pattern for Undo/ReDo. In order to do this you add two additional tables. First you implement a version table for the Cart.

CREATE TABLE CartVersions
(
   CartVersionId INT NOT NULL IDENTITY(1,1)
  ,CartId INT NOT NULL
  ,VersionDTM DateTime NOT NULL
)

Every time you modify the items in the cart you create a new CartVersion record. The VersionDTM column can be used to locate what was the last version available, and each previous version in the order they were created.

What is included in each version? That data is contained in a new table…

CartItemVersions
(
   CartItemId INT NOT NULL
  ,CartVersionId INT NOT NULL
)

Any time you wish to modify the cart contents by adding, modifying or deleting cart items you start by creating a new CartVersions record for the users Cart. You then find the previous version in CartVersions for that Cart.

For an INSERT command you need to create new records in the CartItemVersions table for all of the records from the previous versiojn. You then add the new item to the CartItems table and get the ID assigned. Last, insert the ID assigned along with the new version in the CartItemVersions table. CartItemVersions now contains a list of all the previous items plus the new item you just added, all with a new CartVersionId.

For a DELETE command, you simply copy all the records in CartItemVersions for the previous version, excluding the CartItem to be removed. You didn’t do any deletes. You simply didn’t include the record no longer needed.

An UPDATE is a combination of the INSERT and DELETE. First copy previous history like the DELETE action, excluding the record that is going to be modified. Then follow the same process as the INSERT, using the new values for the record.

Using this structure you accomplish INSERT, UPDATE and DELETE activity with history using only INSERT actions.

Clearly this is a simple example. If you require the capability of UNDO and REDO in your application, and you wish to persist this completely in the database, a version schema like this works nicely. If you don’t need that capability, may be overkill. Instead, consider doing batch saves, and versioning only a complete batch instead of each individual transaction.

Once a cart is complete, using the version table, you can simply delete all of the version data older than the one that was completed for purchase. This deletion can be scheduled to execute during times of lower activity. You could even separate the deletion process. You could simply delete all but the last version records for the CartVersions or the CartVersions and CartItemVersions tables. Then, at a later time you could delete the Carts and CartItems records with no matching version records. The version tables are small and take less to delete. This model provides even more performance as the size of the tables increase
.

We’ll look at some other scenarios where you can optimize Transactional performance by preferring INSERTS over UPDATES and DELETES in future editorials.

Cheers,

Ben