Forum: Building VoltDB Applications

Post: Supported SQL statements document?

Supported SQL statements document?
steves
May 11, 2011
I've found some info on supported SQL statements such as INSERT UPDATE DELETE, but is there a document that lists the complete set of supported SQL statements?
For example, is Order By XYZ Desc / Asc, Group by, Rank, Rank Over, Max etc.

Thanks.
Steve.
re: Supported SQL Statement Documentation
tcallaghan
May 12, 2011
Steve,

The documentation for our SQL support is available online at AppxSQL

-Tim
Support SQL Statements 10 MB Limit
steves
May 12, 2011
Thanks Tim.

In the Select statement doc I noticed that: "Extremely large result sets (greater than 10 megabytes in size) are not supported. If you execute a SELECT statement that generates a result set of more than 10 megabytes, VoltDB will return an error."

10 MB isn't "extremely large". If I have 200 bytes in a row, that's only 50,000 rows that I can process (e.g. rank) in one shot. 50,000 rows is tiny.
What am I missing?

Steve.
re: 10MB result sets
tcallaghan
May 13, 2011
Thanks Tim.

In the Select statement doc I noticed that: "Extremely large result sets (greater than 10 megabytes in size) are not supported. If you execute a SELECT statement that generates a result set of more than 10 megabytes, VoltDB will return an error."

10 MB isn't "extremely large". If I have 200 bytes in a row, that's only 50,000 rows that I can process (e.g. rank) in one shot. 50,000 rows is tiny.
What am I missing?

Steve.


Steve,
VoltDB supports a maximum result set of 10MB from the server back to the client. VoltDB allows more than 10MB of row data to be processed and ranked, we can create internal/temporary tables of up to 100MB. Lets use the following SQL statement for an example:

select t1.c2
from t1
where t1.c1 < 500000
order by t1.c2
limit 50000;

This statement will filter the first 500,000 rows of t1 (if c1 starts at 1 and has no gaps) into a temporary table, order the results, and return the first 50,000 rows back to the client. It is the final return to the client that must be below 10MB.

Also, a VoltDB stored procedure actually returns an array of results to the client. This entire array must be less than 50MB, each individual table in the array is limited to 10MB.

-Tim
10 MB Query Limit
steves
May 14, 2011
Tim,
Thanks for the information.
I don't have a precise schema or SQL written yet. I'm trying to understand if I'm going to hit a ceiling within my application that I'm considering for VoltDB use.
I will have a very large user table (partition on USERID is fine) and statistics column(s) for each user that require summation, rank, and update across the ENTIRE population of users (not within a subset of users) so LIMIT in your code example is not ideal since the rank would be invalid if I don't consider the entire population of users. At 100 MB temp table and 200 byte rows, that's 500,000 userids max. Unfortunately, this is not web scale.

That said, I don't need more than 10 MB back to the client, so that's not a problem. It's the data processing, i.e. select, sum, update within the stored procedures where I need to handle the scale. I guess this is where shared nothing has a problem.

Thanks
Steve
re: Supported SQL statements document?
tcallaghan
May 16, 2011
Tim,
Thanks for the information.
I don't have a precise schema or SQL written yet. I'm trying to understand if I'm going to hit a ceiling within my application that I'm considering for VoltDB use...


Steve,

Much of what you are describing can be accomplished by using materialized views in VoltDB, have you tried them? Documentation is at ddlref_createview

If you'd like to discuss your schema and transactions in more detail please email me at tcallaghan@voltdb.com

-Tim
50 megabyte limit as of v2.0
jpiekos
Nov 16, 2011
Note that as of VoltDB v2.0 this limit is now 50mb