Forum: Building VoltDB Applications

Post: Optimizing the Query in VoltDB

Optimizing the Query in VoltDB
sanket
Oct 27, 2016
Hi,
I want to optimize the query in VoltDB, for that can I measure the performance of each query? I am running a set of queries of TPCC via JDBC java program, so can you please guide me for how to measure the performance of each query (i.e execution time, cpu usage and memory required for each query seperatly). Thanks.
bballard
Nov 28, 2016
VoltDB has a @Statistics system procedure that provides execution times for stored procedures. For AdHoc SQL statements, it aggregates the execution times into four catagories based on if it is read-only or read-write, and single-partition or multi-partition. If you have various queries that fit these categories, then the statistics will show the min, max and average values for multiple queries.

One way you can get more specific execution time statistics is to declare these SQL statements as procedures using the "CREATE PROCEDURE ... AS ..." syntax in your DDL. Then you can get statistics for each one by name. This will also perform better than using AdHoc SQL because the procedures are pre-parsed and planned.