# 19. TPC C Benchmark Kit

Chapter Contents | Prev | Next
Keyword Search:
www.openlinksw.com
docs.openlinksw.com
Book Home
Contents
Preface
TPC C Benchmark Kit
Building the Test Database
Using the Test Program
Tuning Parameters and Number of Users
Omissions, Exceptions from the Definition
Sample Configuration
Other Factors
TPC C Procedures
Introduction New Order Payment Delivery Order Status Stock Level
DDL Statements
Stored Procedures

## 19.7. TPC C Procedures

### 19.7.1. Introduction

This document goes through the TPC C sample and explains how and why the transaction procedures are written as they are.

This describes the five transactions in the benchmark and points out how the procedures are written and which features of the language are used where. See the sample/tpcc.sql and sample/tpcctrx.c files along with this commentary.

See Also:

For a formal benchmark definition, see the documentation at the TPC Web Site.

### 19.7.2. New Order

- Passing parameters

- Using positioned operations

- Using read for update

- Order of locking

- Application-defined SQL STATE

The new_order procedure implements this transaction. It accepts the warehouse, district and customer data and the item, quantity and supply warehouse id for up to ten order lines.

The transaction profile requires this to update the stock level for each order line, add a row to ORDERS and NEW_ORDER and a row to ORDER_LINE for each order line. This also reads the customer, updates the district and reads the warehouse. This all needs to take place as one transaction with a high integrity requirement.

The procedure begins by updating the stock levels. This is the part with lowest locality and thus most likely to cause IO and least likely to cause lock contention. Most of the transaction's real time will be spent inside ol_stock. If the order lines are sorted in order of item id, new order transactions will never deadlock on the stock level part. This will maximize the number of concurrent new orders on one warehouse.

    	CREATE PROCEDURE OL_STOCK (
    		INOUT	OL_I_ID		INTEGER,
    		IN	OL_SUPPLY_W_ID	INTEGER,
    		IN	OL_QTY		INTEGER,
    		OUT	AMOUNT		FLOAT)
    	{
    	    IF (OL_I_ID = -1) RETURN;

    	    DECLARE S_DATA,
    		S_DIST_01, S_DIST_02, S_DIST_03, S_DIST_04, S_DIST_05,
    		S_DIST_06, S_DIST_07, S_DIST_08, S_DIST_09, S_DIST_10 VARCHAR;
    	    DECLARE S_QUANTITY INTEGER;
    	    DECLARE I_PRICE FLOAT;

    	    WHENEVER NOT FOUND GOTO NO_ITEM;

    	    SELECT I_PRICE INTO I_PRICE FROM ITEM WHERE I_ID = OL_I_ID;

    	    DECLARE S_CUR CURSOR FOR
    		SELECT
    		    S_QUANTITY, S_DATA,
    		    S_DIST_01, S_DIST_02, S_DIST_03, S_DIST_04, S_DIST_05,
    		    S_DIST_06, S_DIST_07, S_DIST_08, S_DIST_09, S_DIST_10
    		FROM
    		    STOCK
    		WHERE
    		      S_I_ID = OL_I_ID
    		  AND S_W_ID = OL_SUPPLY_W_ID;

    	    WHENEVER NOT FOUND GOTO NO_STOCK;

    	    OPEN S_CUR (EXCLUSIVE);

    	    FETCH S_CUR INTO
    		S_QUANTITY, S_DATA,
    		S_DIST_01, S_DIST_02, S_DIST_03, S_DIST_04, S_DIST_05,
    		S_DIST_06, S_DIST_07, S_DIST_08, S_DIST_09, S_DIST_10;

    	    IF (S_QUANTITY < OL_QTY)
    		S_QUANTITY := S_QUANTITY - OL_QTY + 91;
    	    ELSE
    		S_QUANTITY := S_QUANTITY - OL_QTY;

    	    UPDATE STOCK SET S_QUANTITY = S_QUANTITY WHERE CURRENT OF S_CUR;

    	    AMOUNT := OL_QTY * I_PRICE;

    	    RETURN;

        NO_STOCK: SIGNAL ('NOSTK', 'NO STOCK ROW FOUND.');
        NO_ITEM:  SIGNAL ('NOITM', 'NO ITEM ROW FOUND.');
        }

This procedure first reads the I_PRICE from ITEM and update the S_QUANTITY in STOCK. The price of the order line is returned as output parameter amount.

Note

- Use inout parameters if possible. The inout (call by reference) is the fastest way of passing parameters.

- When reading a table with the intention of updating the row afterwards, use a cursor and positioned update.

- Use the EXCLUSIVE option in opening the cursor. This causes the read to put an exclusive lock on the row, which eliminates deadlocks caused by a shared read followed by an update. This deadlocks if there are more than one readers at the time of the update.

- Use `whenever not found' and signal to signal exceptions (no item or stock line).

When the stock level for all order lines has been updated this reads the customer data.

The bottleneck in terms of serialization is the read-update of the district, where the order gets an O_ID. This must be done as late as possible but has to precede the inserts, since these will use the O_ID. Note the exclusive cursor again.

To finish the transaction new_order insert ORDERS, NOW_ORDER and ORDER_LINE. These are basically in ascending order for each district and have high locality. Note the call by reference (inout) for ol_insert.

### 19.7.3. Payment

The payment transaction reads and updates the customer. The customer may either be identified by its last name or its C_ID. In the case the last name this chooses the middle row of the set of customers sharing the same last name, ordered by first name. Note the select count and the cursor.

The transaction profile does not offer possibilities of optimization.

### 19.7.4. Delivery

The delivery transaction reads and deletes a line from NEW_ORDER and updated the corresponding ORDERS and ORDER_LINE rows. The rules allow committing the transaction after processing each order. The client calls this procedure ten times with a different d_id parameter once every ten new orders. It is better to have the 1 to 10 loop in the client in order to keep locks on for the least time possible.

We use a cursor to read NEW_ORDER. Note the open no_cur (exclusive, prefetch 1). The prefetch 1 means we only intend to get one row. This prevents the cursor from prefetching more rows, which would be useless here.

Otherwise the transaction does not leave room for optimization.

### 19.7.5. Order Status

This transaction picks the last order of a given customer. It uses a select in descending order to locate it.

This is a read only transaction. Note the use of SQL_CONCURRENCY in the client code for specifying historical read mode. This eliminates locking.

Note

The ORDER BY clause must list all key parts of the index to be used, all with either ASC or DESC ordering in order to specify that index.

This transaction could be implemented in principle using the ORDERS primary key, O_W_ID, O_D_ID, O_ID. This would read in descending order until finding a row with a given C_IC. There being 3000 customers per district this could cause an average 1500 lines to check before finding the right one. Since the distribution of O_C_ID is not even the above is not entirely correct but close enough. The other possibility is having a separate index, O_W_ID, O_D_ID, O_C_ID, O_ID for this transaction. The trade-off being 1500 serial reads against 10 random insert (10 new order per one order status) we choose to have the extra index.

### 19.7.6. Stock Level

This is a complex read-only transaction. This finds all distinct items which have been ordered within the last n orders from a district having a stock level lower than a given threshold. The SQL statement:

    	SELECT
    	    COUNT (DISTINCT S_I_ID) INTO N_ITEMS
    	FROM
    	    ORDER_LINE, STOCK
    	WHERE
    	        OL_W_ID = W_ID
    	    AND OL_D_ID = D_ID
    	    AND OL_O_ID < LAST_O
    	    AND OL_O_ID >= LAST_O - 20
    	    AND S_W_ID = W_ID
    	    AND S_I_ID = OL_I_ID
    	    AND S_QUANTITY < THRESHOLD;

is the fastest way of getting this. Note that the ORDER_LINE is the driving table of join (leftmost in FROM). Also not the use of historical read in the client (SQL_CONCURRENCY option).

Previous Other Factors | Chapter Contents | Next DDL Statements