Forum: Building VoltDB Applications

Post: @AdHoc query or stored procedures

@AdHoc query or stored procedures
shadybroker
Jul 31, 2015
I want to implement a search in Java using VoltDB. Say a user visits <my-url>/search?time_bfr=10&time_aft=20 so I was wondering whether I should use an @AdHoc query to implement this or a 3 stored procedures (two to handle time_bfr and time_aft individually and then one to handle their combination)

SELECT * FROM tableName WHERE time > ?;

SELECT * FROM tableName WHERE time < ?;

SELECT * FROM tableName WHERE time > ? AND time < ?;


or I could go with something like

List<String> whereClauses = new ArrayList<>();

if (timeAfter != null && timeAfter != 0L) {
    whereClauses.add("swiped_at > " + timeAfter);
}

if (timeBefore != null && timeBefore != 0L) {
    whereClauses.add("swiped_at < " + timeBefore);
}

final String adhocQuery = basicQuery +
        (whereClauses.size() > 0 ? " WHERE " + StringUtils.join(whereClauses, " AND ") : "") + " ;";
ClientResponse response = voltdbClient.callProcedure("@AdHoc", adhocQuery);


But if I am to go with AdHoc queries how will I convert timestamps to the proper format? SINCE_EPOCH(Millisecond, longTimestamp) doesn't seem to be working.
bballard
Jul 31, 2015
Hi,

The best way to do something like this in VoltDB is to use a stored procedure, either one based on a single SQL statement that you declare in DDL, or a java stored procedure, which provides more flexibility. A java stored procedure can have multiple SQL statements, as well as the logic to determine which SQL to use for a given set of input parameters, or date conversion.

For this search query, a java procedure could have 3 versions of the SQL, like the ones you listed first above, with similar logic to determine which statement to use.

Another variation worth trying might be to use a single SQL statement that includes both the before and after times, and in the procedure when either of those input parameters are null, replace the null value with something like 1900-01-01 for the before date, and 2100-01-01 for the after date. It's a range of values either way, and the performance would probably not differ very much from the variation using three separate statements.

To do the date conversion, if you are already using a java stored procedure, and these are inputs to the query, it's best to do the conversion of the values and timestamp arithmetic using Java and pass in a Date or Timestamp object to the query. There are SQL functions such as SINCE_EPOCH and TO_TIMESTAMP that could be used to do the arithmetic in SQL, but it carries the risk that the query engine might not optimize for the result of the expression being constant, it might repeat the calculation more than necessary, depending on the query. Doing the timestamp math in Java makes the query a dead-simple index range scan, which should perform very well.

Make sure the "time" column is indexed. If you are searching on other variables, such as WHERE user = ? and time > ?, use a multi-column index on (user,time).

Regards,
Ben
shadybroker
Aug 3, 2015

For this search query, a java procedure could have 3 versions of the SQL, like the ones you listed first above, with similar logic to determine which statement to use.


My actual query has a lot more variations. You sure there is not going to be a performance hit?

Also how do you suggest I create multi-column indices?
pzhao
Aug 3, 2015
shadybroker,

My actual query has a lot more variations. You sure there is not going to be a performance hit?

The best way is go about this is to run tests. To understand what how VoltDB is executing your queries, please use our @explain stored procedure and determining if tuning is necessary. I'd also suggest this blog post, best practices for indices.

Also how do you suggest I create multi-column indices?

There are a few examples in our documentation you can use for reference.

Peter Zhao