Forum: VoltDB Architecture

Post: How to choose several partitions for query

How to choose several partitions for query
Anton
Jun 4, 2015
Hi,

I would like to know how VoltDB executes query for many partitions.

For example, I have partitioned table - number of partitions 10. I want to execute multipartition SP, but I know that SP will not change all partitions because I have full list of values of column for partition and I see that SP does not have to "touch" all partitions. Can I tune SP for such case (use only some partitions instead of all) or SP will lock all cluster for it and execute queries for all partitions ?

Thank you.
bballard
Jun 11, 2015
Hi Anton,

One option is you could make multiple separate calls to a single-partition stored procedure, one for each partition column value. This way only those affected partitions are involved. This would have the least impact on the rest of your workload.

Another option is to run it as a multi-partition stored procedure as you have described. In this case, there is no way to prevent the procedure from running in ALL of the partitions, this is how multi-partition procedures currently work. But, you can use an optimization to minimize the impact on the partitions that are involved but have nothing to do. In the stored procedure code when you call voltExecuteSQL(), assuming this is the last -- perhaps the only --- call, you can set the parameter "true" which will indicate to the execution engines that this is the last batch of SQL statements to be executed for this transaction. There are some optimizations that use this, essentially allowing the partitions that don't have anything to do to proceed with other work a bit sooner.

voltExecuteSQL(true)

Best regards,
Ben