Forum: Building VoltDB Applications

Post: Stored procedure vs SQL query

Stored procedure vs SQL query
snowball
Mar 24, 2015
Hi all,

I am new to VoltDB and I am trying to understand what it is the best way to use it.
I was being told that I should always use stored procedure instead of SQL query but
it is not clear to me why it has to be like that so can someone please explain to me?

Thank you
bballard
Mar 24, 2015
Hello Snowball,

Stored procedures are pre-compiled and the queries within them are pre-planned, whereas Ad Hoc SQL statements must be parsed and planned on the fly which adds overhead to each transaction. Typically it is fine to use Ad Hoc SQL statements when the requests are generated at traditional OLTP scale, such as employees filling out forms and managers viewing dashboards, but for Fast Data workloads where requests are generated at web scale or IoT scale, you would want to use stored procedures.

CRUD-style stored procedures are auto-generated for every table. You can also declare a procedure with a DDL SQL statement. More complex procedures that involve multiple SQL statements and logic are coded in java.

To learn more about the architecture of VoltDB, you might enjoy some of our developer videos and VoltDB University lessons:

http://voltdb.com/resources/video/developer
http://voltdb.com/volt-university/tutorials

Best regards,
Ben
snowball
Mar 24, 2015
Hi Ben,

Thank you for replying
From what you are saying, it seems that it is only overhead of parse and plan the query, I am wonder if this part have big impact on the performance?
What about the benefit of utilizing partition? I saw the document regarding partitioning stored procedure but I don't see any document regarding ad hoc SQL.

Thank you
bballard
Apr 7, 2015
The overhead of parsing and planning AdHoc queries is significant if you are trying to process thousands of Ad Hoc queries per second. Ad Hoc queries are automatically partitioned by the planner if they use WHERE conditions that set the partitioning key to a value. Partitioned Ad Hoc queries will run in a single partition, which reduces the impact of the Ad Hoc queries on other parts of the workload.