Forum: Building VoltDB Applications

Post: stored procedures always needed?

stored procedures always needed?
twine
Feb 1, 2013
Are stored procedures only required to be written to optimize performance? Can I start without them? Are there any "default" stored procedures?
I am starting with simple requirements: just need to store data in an in memory RDBMS and be able to access it using C/C++ code, HTTP/JSON, Java, Python. The helloworld example seems to work w/o a stored procedure (or default stored procedures?).

Appreciate help from this forum!
pmartel
Feb 1, 2013
Yes. VoltDB client processes have the option of calling the "@AdHoc" procedure to pass a complete SQL statement to the database. This is a way to get started, but using a default stored procedure will perform better than the corresponding ad hoc SQL statement, and defining even a trivial stored procedure to queue and execute an SQL statement of your own will perform better than the exact same ad hoc SQL statement from the client. In addition to this performance benefit, a stored procedure that contains multiple related SQL statements also provides an implicit transaction scope for those statements.

Paul Martel
VoltDB
pmartel
Feb 1, 2013
Yes. VoltDB client processes have the option of calling the "@AdHoc" procedure to pass a complete SQL statement to the database. This is a way to get started, but using a default stored procedure will perform better than the corresponding ad hoc SQL statement, and defining even a trivial stored procedure to queue and execute an SQL statement of your own will perform better than the exact same ad hoc SQL statement from the client. In addition to this performance benefit, a stored procedure that contains multiple related SQL statements also provides an implicit transaction scope for those statements.

Paul Martel
VoltDB
twine
Feb 1, 2013
Thanks Paul. Do the adhoc procedures also get created for JSON queries if I did not write one specifically and a default wasn't there?