2.2. Designing the Stored Procedures

Documentation

VoltDB Home » Documentation » Planning Guide

2.2. Designing the Stored Procedures

Designing the schema and transactions to be single-partitioned is one thing. It is equally important to make sure that the stored procedures operate in a way that lets VoltDB do its job effectively.

The first step is to write the transactions as stored procedures that are loaded into the schema. Do not write critical transactions as ad hoc queries to the database. VoltDB provides the @AdHoc system procedure for executing arbitrary SQL queries, which can be helpful when building early prototypes to test queries or occasionally validate the content of the database. But @AdHoc queries often run as multi-partitioned transactions and, therefore, should not be used for critical or repetitive transactions.

The next step is to ensure that single-partitioned stored procedures are correctly identified as such in the schema using the PARTITION ON clause in the CREATE PROCEDURE statement and specifying the appropriate partitioning column.

Finally, when designing for maximum throughput, use asynchronous calls to invoke the stored procedures from within the POC application. Asynchronous calls allow VoltDB to queue the transactions (and their responses), avoiding any delays between when a transaction completes, the results are returned to the POC application, and the next procedure is invoked.

Chapter 5, Benchmarking later in this book provides additional suggestions for effectively designing and testing proof of concept applications.