<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head profile="http://internetalchemy.org/2003/02/profile">
<link rel="foaf" type="application/rdf+xml" title="FOAF" href="http://www.openlinksw.com/dataspace/uda/about.rdf" />
<link rel="schema.dc" href="http://purl.org/dc/elements/1.1/" />
<meta name="dc.title" content="19. TPC C Benchmark Kit" />
<meta name="dc.subject" content="19. TPC C Benchmark Kit" />
<meta name="dc.creator" content="OpenLink Software Documentation Team ; " />
<meta name="dc.copyright" content="OpenLink Software, 1999 - 2009" />
<link rel="top" href="index.html" title="OpenLink Virtuoso Universal Server: Documentation" />
<link rel="search" href="/doc/adv_search.vspx" title="Search OpenLink Virtuoso Universal Server: Documentation" />
<link rel="parent" href="tpcc.html" title="Chapter Contents" />
<link rel="prev" href="otherfactors.html" title="Other Factors" />
<link rel="next" href="ddlstmt.html" title="DDL Statements" />
<link rel="shortcut icon" href="../images/misc/favicon.ico" type="image/x-icon" />
<link rel="stylesheet" type="text/css" href="doc.css" />
<link rel="stylesheet" type="text/css" href="/doc/translation.css" />
<title>19. TPC C Benchmark Kit</title>
<meta http-equiv="Content-Type" content="text/xhtml; charset=UTF-8" />
<meta name="author" content="OpenLink Software Documentation Team ; " />
<meta name="copyright" content="OpenLink Software, 1999 - 2009" />
<meta name="keywords" content="" />
<meta name="GENERATOR" content="OpenLink XSLT Team" />
</head>
<body>
<div id="header">
<a name="tpccprocs" />
<img src="../images/misc/logo.jpg" alt="" />
<h1>19. TPC C Benchmark Kit</h1>
</div>
<div id="navbartop">
<div>
<a class="link" href="tpcc.html">Chapter Contents</a> | <a class="link" href="otherfactors.html" title="Other Factors">Prev</a> | <a class="link" href="ddlstmt.html" title="DDL Statements">Next</a>
</div>
</div>
<div id="currenttoc">
<form method="post" action="/doc/adv_search.vspx">
<div class="search">Keyword Search: <br />
<input type="text" name="q" /> <input type="submit" name="go" value="Go" />
</div>
</form>
<div>
<a href="http://www.openlinksw.com/">www.openlinksw.com</a>
</div>
<div>
<a href="http://docs.openlinksw.com/">docs.openlinksw.com</a>
</div>
<br />
<div>
<a href="index.html">Book Home</a>
</div>
<br />
<div>
<a href="contents.html">Contents</a>
</div>
<div>
<a href="preface.html">Preface</a>
</div>
<br />
<div class="selected">
<a href="tpcc.html">TPC C Benchmark Kit</a>
</div>
<br />
<div>
<a href="tpcctestdb.html">Building the Test Database</a>
</div>
<div>
<a href="tpccusingtestprg.html">Using the Test Program</a>
</div>
<div>
<a href="tpcctuningparams4users.html">Tuning Parameters and Number of Users</a>
</div>
<div>
<a href="omissionsexcp.html">Omissions, Exceptions from the Definition</a>
</div>
<div>
<a href="sampleconf.html">Sample Configuration</a>
</div>
<div>
<a href="otherfactors.html">Other Factors</a>
</div>
<div class="selected">
<a href="tpccprocs.html">TPC C Procedures</a>
<div>
<a href="#intro" title="Introduction">Introduction</a>
<a href="#neworder" title="New Order">New Order</a>
<a href="#payment" title="Payment">Payment</a>
<a href="#delivery" title="Delivery">Delivery</a>
<a href="#status" title="Order Status">Order Status</a>
<a href="#stocklevel" title="Stock Level">Stock Level</a>
</div>
</div>
<div>
<a href="ddlstmt.html">DDL Statements</a>
</div>
<div>
<a href="storedprocs.html">Stored Procedures</a>
</div>
<br />
</div>
<div id="text">
<a name="tpccprocs" />
<h2>19.7. TPC C Procedures</h2>
<a name="intro" />
<h3>19.7.1. Introduction</h3>
<p>
This document goes through the TPC C sample and explains how and why
the transaction procedures are written as they are.
</p>
<p>
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.
</p>
<div class="tip">
<div class="tiptitle">See Also:</div>
<p>
For a formal benchmark definition, see the documentation
at <a href="http://www.tpc.org/">the TPC Web Site</a>.
</p>
</div>
<br />
<a name="neworder" />
<h3>19.7.2. New Order</h3>
<ul>
<li>
<p>Passing parameters</p>
</li>
<li>
<p>Using positioned operations</p>
</li>
<li>
<p>Using read for update</p>
</li>
<li>
<p>Order of locking</p>
</li>
<li>
<p>Application-defined SQL STATE</p>
</li>
</ul>
<p>
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.
</p>
<p>
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.
</p>
<p>
The procedure begins by updating the stock levels. This is the part
with lowest locality and thus most likely to cause I/O 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.
</p>
<div>
<pre class="programlisting">
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.');
}
</pre>
</div>
<p>
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.
</p>
<div class="note">
<div class="notetitle">Note</div>
<ul>
<li>
<p>
Use inout parameters if possible. The inout (call by reference) is the
fastest way of passing parameters.</p>
</li>
<li>
<p>
When reading a table with the intention of updating the row afterwards,
use a cursor and positioned update.</p>
</li>
<li>
<p>
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.</p>
</li>
<li>
<p>
Use `whenever not found' and signal to signal exceptions (no item or
stock line).</p>
</li>
</ul>
</div>
<p>
When the stock level for all order lines has been updated this reads
the customer data.
</p>
<p>
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.
</p>
<p>
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.
</p>
<br />
<a name="payment" />
<h3>19.7.3. Payment</h3>
<p>
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.
</p>
<p>
The transaction profile does not offer possibilities of optimization.
</p>
<br />
<a name="delivery" />
<h3>19.7.4. Delivery</h3>
<p>
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.
</p>
<p>
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.
</p>
<p>
Otherwise the transaction does not leave room for optimization.
</p>
<br />
<a name="status" />
<h3>19.7.5. Order Status</h3>
<p>
This transaction picks the last order of a given customer. It uses a
select in descending order to locate it.
</p>
<p>
This is a read only transaction. Note the use of SQL_CONCURRENCY in the
client code for specifying historical read mode. This eliminates locking.
</p>
<div class="note">
<div class="notetitle">Note</div>
<p>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.
</p>
</div>
<p>
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.
</p>
<br />
<a name="stocklevel" />
<h3>19.7.6. Stock Level</h3>
<p>
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:
</p>
<div>
<pre class="programlisting">
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;
</pre>
</div>
<p>
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).
</p>
<br />
<table border="0" width="90%" id="navbarbottom">
<tr>
<td align="left" width="33%">
<a href="otherfactors.html" title="Other Factors">Previous</a>
<br />Other Factors</td>
<td align="center" width="34%">
<a href="tpcc.html">Chapter Contents</a>
</td>
<td align="right" width="33%">
<a href="ddlstmt.html" title="DDL Statements">Next</a>
<br />DDL Statements</td>
</tr>
</table>
</div>
<div id="footer">
<div>Copyright© 1999 - 2009 OpenLink Software All rights reserved.</div>
<div id="validation">
<a href="http://validator.w3.org/check/referer">
<img src="http://www.w3.org/Icons/valid-xhtml10" alt="Valid XHTML 1.0!" height="31" width="88" />
</a>
<a href="http://jigsaw.w3.org/css-validator/">
<img src="http://jigsaw.w3.org/css-validator/images/vcss" alt="Valid CSS!" height="31" width="88" />
</a>
</div>
</div>
</body>
</html>
distrib
>
Fedora
>
14
>
x86_64
>
media
>
updates
>
by-pkgid
>
71d40963b505df4524269198e237b3e3
>
files
>
895
