High Volume Transaction SQL Tips

One of the most effective techniques I have found to optimize transactions in high volume OLTP databases is to persist your data in sets. Once you have a set of data available in SQL Server, you need to process that set using efficient SQL Syntax. Regardless of how you pass a set of data to SQL Server, there are some set processing techniques that work well.

Using SQL Server, one of the most effective set techniques, when normalizing set data into multiple tables, is the use of the OUTPUT clause. For any INSERT, UPDATE or DELETE query the OUTPUT clause returns two virtual sets of data representing the schema of the table being modified. It has a virtual set of INSERTED and DELETED. If you are familiar with triggers, these virtual tables work exactly the same. The difference is that you can handle the results yourself directly in your query or stored procedure.

If you were to use the example of the shopping cart from the earlier editorial, where you are saving the cart info and cart item info, each with version data to simplify the process, you could readily handle the persistence using set logic. Following is an example where the application saves a version of the data by submitting the entire cart each time the data is persisted. We’re going to using a user defined table type for the process.

Here are the four transaction tables we are populating. There is a Carts and CartItems table containing the selections from a current shopping cart for a user. The CartVersions and CartItemVersions tables are used so that you can perform INSERT, UPDATE and DELETE actions by only using INSERT actions. See TIPS for High Performing OLTP Databases for more details. Here are the transaction table structures we using for this example.

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

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

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
)
GO

CREATE TABLE CartItemVersions
(
   CartItemId       INT         NOT NULL
  ,CartVersionId    INT         NOT NULL
)
GO

I’m using a table called CartBatch as a staging table populated from the current cart selections from the users application. I’m going to save everything in the current cart with a single call by populating this batch table, and then merging the data to the appropriate transaction tables. The batch data may be presented to the query using a number of techniques as demonstrated in Tips for High Performing OLTP Databases 2. The batch set data structure I’m going to use follows.

CREATE TABLE CartBatch
(
   CustomerId       INT         NOT NULL
  ,CartDTM          DATETIME    NOT NULL
  ,ProductId        INT         NOT NULL
  ,Quantity         INT         NOT NULL
  ,ItemCost         MONEY       NOT NULL
  ,DiscountCode     VARCHAR(32) NOT NULL
)
GO

Once records are added to the batch table, I can process them. In my code, I am assuming the data in the batch table represents only the data for a single instance of a shopping cart. The following set of queries populate all four tables, and handle the identity columns created through the SCOPE_IDENTITY() function, or the OUTPUT clause during an insert statement. I am only going to insert

New Carts (the first time data is saved for this customer cart session)

A version for this instance of the cart being saved

New Cart Items where the product, quantity, or discount do not already exist

A version for each CartItem that was added or modified

The following SQL is one implementation for saving a user’s cart as a batch.

DECLARE @CartID         INT 
DECLARE @CustomerID     INT
DECLARE @CartDTM        DATETIME
DECLARE @CartVersionId  INT

DECLARE @CartItmeInsert TABLE
(
   CartItemId       INT         NOT NULL
  ,CartId           INT         NOT NULL
  ,ProductId        INT         NOT NULL
)

SELECT TOP 1
        @CustomerID = CustomerId
       ,@CartDTM = CartDTM
FROM    CartBatch

SELECT  @CartID = CartId
FROM    Carts
WHERE   CustomerId = @CustomerID
AND     CartDTm = @CartDTM
        
if @@ROWCOUNT = 0
BEGIN
    INSERT INTO Carts (CustomerId, CartDTM)
    VALUES (@CustomerID, @CartDTM)

    SET @CartID = SCOPE_IDENTITY()
END

INSERT INTO CartVersions (CartId, VersionDTM)
VALUES (@CartId, GETDATE())

SET @CartVersionId = SCOPE_IDENTITY()

-- NOTE: I'm using a Discounts table in the following query that
--       I have not defined above. It is used to apply discounts
--       for a product at sale time, and to compute the extended
--       amount for each product. Notice the ISNULL function for
--       the discount amount for those products without one.
INSERT INTO CartItems (
         CartId
        ,ProductId
        ,Quantity      
        ,ItemCost      
        ,DiscountCode  
        ,DiscountAmount
        ,ExtendedCost  
)
OUTPUT  INSERTED.CartItemId
       ,INSERTED.CartId
       ,INSERTED.ProductId
INTO    @CartItmeInsert
SELECT 
        @CartId
       ,b.ProductId
       ,b.Quantity
       ,b.ItemCost
       ,b.DiscountCode
       ,ISNULL(d.DiscountAmount, 0)
       ,(ItemCost - ISNULL(d.DiscountAmount, 0))  * b.Quantity
FROM        CartBatch   b
LEFT JOIN   Discounts   d   on b.DiscountCode = d.DiscountCodes
LEFT JOIN   CartItems   i   ON  i.CartId = @CartId
                            AND i.ProductId = b.ProductId
                            AND i.Quantity = b.Quantity
                            AND i.DiscountAmount = b .DiscountCode
WHERE   i.CartItemId IS NULL

INSERT INTO CartItemVersions (CartItemId, CartVersionId)
SELECT  CartItemId, @CartVersionId
FROM    @CartItemInsert

INSERT INTO CartItemVersions (CartItemId, CartVersionId)
SELECT      ci.CartItemId, @CartVersionId
FROM        CartBatch       b
JOIN        CartItems       ci  ON  ci.CartId = @CartId  
                                AND b.ProductId = ci.ProductId
LEFT JOIN   @CartItmeInsert cii ON  ci.ProductId = cii.ProductId
WHERE   cii.CartItemId IS NULL 

This implementation works well with small or large sets. The larger the set is being saved, the more efficient this technique becomes.

Cheers,

Ben