Forum: Building VoltDB Applications

Post: create temp table

create temp table
jobiwankanobi
Apr 23, 2011
Howdy,
In post http://forum.voltdb.com/showthread.php?364-dificult-implementation-of-web-table-paging-with-LIMIT-clause&highlight=dificult+implementation+table+paging+LIMIT+clause it was mentioned to create a temp table to accomplish pagination. Is there a specific method in voltdb for creating a temp table or do you just run sql "create table" and drop it at the end of the procedure? Also how do I avoid using the same name without creating a sqlstmt within the run method? Can I pass a placeholder as the table name?
Thanks
--jim
"Driving along" the PK
sebc
Apr 24, 2011
Hi Jim,
There are many ways to do paging and many different table layouts upon which you might have to do this. For the sake of concision I will stick to what should be the most general case.
Provided you have a primary key on your table (generically a good design), your best bet is to "drive along" it, as we used to need to before MySql came up with the extended LIMIT statement, or SQL Server introduced ranking functions.
Here's how you would go about it...
Suppose you have a table defined as:

CREATE TABLE MyTable ( MyTableId bigint NOT NULL , Field1 ... ... , FieldN ... , PRIMARY KEY ( MyTableId ) ); For this example, I will also suppose that MyTableId is managed in your code as a traditional IDENTITY(1,1) (not natively supported, but easy enough to implement), that is a monotomically increasing number starting at 1. Your pagination procedure could then look like:

package poc.procedures; import org.voltdb.*; public class MyTableGetPage { public final SQLStmt selectMinID = new SQLStmt("SELECT MIN(MyTableId) FROM MyTable;"); public final SQLStmt selectRange = new SQLStmt("SELECT MyTableId FROM MyTable WHERE MyTableId >= ? ORDER BY MyTableId LIMIT ?;"); public final SQLStmt select = new SQLStmt("SELECT * FROM MyTable WHERE MyTableId >= ? ORDER BY MyTableId LIMIT ?;"); public VoltTable[] run(int pageNumber, int pageSize) { // Return empty dataset for invalid paging parameters if (pageNumber < 1 || pageSize < 1) { this.voltQueueSQL(select, 0,0); return this.voltExecuteSQL(true); } // Figure out the ID of the first record (note: ugly 'loose' casting, but this will work regardless of the underlying PK's data type (tinyint, smallint, int or bigint)) long nextID = 1; this.voltQueueSQL(selectMinID); VoltTableRow row = this.voltExecuteSQL()[0].fetchRow(0); nextID = row.getLong(0); // If there is no record in the table, return empty dataset if (row.wasNull()) { this.voltQueueSQL(select, 0, 0); return this.voltExecuteSQL(true); } // Otherwise, let's find our page else { // Go fetch the next page's starting ID until we're on the right page int pageIndex = 1; while(pageIndex++ < pageNumber) { this.voltQueueSQL(selectRange, nextID, pageSize); VoltTable table = this.voltExecuteSQL()[0]; // If we're still iterating and find a partial page, then the game is over: requested page is out of bounds and we'll return an empty dataset if (table.getRowCount() < pageSize) { this.voltQueueSQL(select, 0,0); return this.voltExecuteSQL(true); } else { nextID = 1+table.fetchRow(pageSize-1).getLong(0); } } // We found our page - pull it out and return to caller this.voltQueueSQL(select, nextID, pageSize); return this.voltExecuteSQL(true); } } } As you probably noticed, while the code is a bit lengthy, it is also fairly generic - there is actually a way to make this into a generic function, although it requires using some undocumented features, so I won't go into those details here ;)
The principle is much the same if your primary key isn't a simple number, an identity or even if you do not have a primary key, and will works regardless of partitioning (though as you will understand, this specific procedure can rarely be single-partitioned - that is except if the partitioning is not on the primary key and you do pass your partitioning value as an additional parameter): you simply need to ensure a reliable ordering of your records so you can "drive along" in a deterministic fashion (otherwise, not being guaranteed the ordering of your records, your pages could repeat or skip data).
If you do not use the primary key for ordering, you will want to make sure you have some type of indexing in place for performance reason. Driving along the PK eliminates this additional need since the PK is, by nature, indexed.
There are obviously many other ways to do paging, but keep in mind you will have some limitations due to your inability to perform dynamic SQL statements (other than outside of a procedure using the @AdHoc system call). So for instance, it will not be possible to have a parametric ORDER BY list.
I hope this helps,
Seb