Forum: Building VoltDB Applications

Post: using stored procedure in a stored procedure

using stored procedure in a stored procedure
stdweird
May 30, 2014
hi all,

i have a partitioned stored procedure SP1 on table A, and i want to create a 2nd partitioned stored procedure SP2 on table B that uses the result of a SP1 query.
is this possible? and if so, any example code how to do this (i assume calling SP1 from SP2 as a regular client app is not the way to do it?)

the main idea is to call SP2 asynchronous from the client, and let SP2 do the work. i would opt for this instead of calling SP1 from the client, and then SP2 also from client (unless it is possible to call SP2 asynchronous from the (async) callback of SP1).

thanks a lot,

stijn
vtkstef
May 30, 2014
Hi Stijn,

Are table A, and B partitioned using the same key? If so you may incorporate SP1 SQL statements in SP2. If not, you may invoke SP2 (preferably async) in the SP1 completion callback. Be aware, though, that blocking the thread that executes completion callbacks may adversely effect VoltDB procedure invocation latencies and throughput.

Ciao
Stefano
bballard
May 30, 2014
Hi Stijn,

Stefano will assist you. I was typing this earlier and got interrupted, but it may still be useful.

There isn't a way to call one stored procedure from another. If the work can be done within a single stored procedure call, then you can merge the functionality into a single stored procedure. Some people create a super class stored procedure that extends VoltProcedure and contains various SQLStmt objects and some reusable methods, then they extend that class to create procedures that can draw on this reusable code in different ways. It's important that the SQLStmt objects are inherited by the stored procedure class so the voltdb compiler will plan them correctly.

The key question is whether the work can be done in the same single partition. You mentioned tables A and B, so in order to have one procedure access both tables within a single-partition the tables need to share a common partitioning key.

If not, it's generally best to keep them separate and return the results of the first procedure to be used as input to the second procedure.

You can make an asynchronous call to SP2 from the callback of SP1. The main concern is whatever you do within a callback should be a quick operation because by default all of the callbacks are received on a single thread, so if there is any significant work it is best to offload it to other threads, but making another asynchronous call is pretty quick so that should not be a problem If you needed a bit more capacity you could call .setHeavyweight(true) on the ClientConfig before instantiating the Client, and this will create a small pool of threads to receive the responses and execute the callbacks.



Ben
stdweird
May 31, 2014
thanks for the feedback and suggestions.

tables A and B are not partitioned with the same key, so i'll try the callback approach.

what are the limits of the async calls (and the additional callback work)?
in total i expect to make of the order of 500M calls to SP2 (implying another 500M calls to SP1). should i try to group them in say 100k (or 1M?) calls and then wait for app.drain() before continuing with next batch, or is the async call able to handle this gracefully (ie without blowing up on memory or other resources).

stijn
bballard
Jun 2, 2014
No, you don't need to call drain() before sending more requests. In our example applications, drain() is called at the end of the run to ensure that all the responses are received before the benchmark stops and prints final statistics. Unless you have some natural stopping point where you want to block the entire client until all outstanding requests are received, you only need to use .drain() right before close() before shutting down.