Forum: Building VoltDB Applications

Post: What kind of SQL may be slower in VoltDB in large concurrent situation?

What kind of SQL may be slower in VoltDB in large concurrent situation?
guotong1988
Sep 28, 2015
As I learn from the VoltDB online lessons . I think these SQL may be fast in large concurrent situation:
select from where =
update from where =
delete from where =
guotong1988
Oct 7, 2015
Sorry to reply my post.I think my question is not clear .

If I run a ORDER BY SQL for all rows ,at the same time I update one row .Does it need to wait for each other ,or does the ORDER BY SQL slow the update SQL?
bballard
Oct 7, 2015
If you run 3 SQL statements within a procedure, they are executed one at a time. If you run them as separate calls to the database, if they are multi-partition, each request will be put into the same queue and the transactions will run one at a time. If they are single-partition, then they are routed to the appropriate partition's queue, and if they were for separate partitions, then they could run simultaneously, but if they do, they are in separate partitions so they don't affect one another.

Running one transaction at a time is what allows VoltDB to not use the locking, latching, buffering, and much of the logging processes that a traditional RDBMS uses in order to maintain ACID compliance for multiple simultaneous transactions. Those processes create bottlenecks that impede throughput, so VoltDB uses partitions that have single-threaded execution of transactions, and a single-threaded coordinator for multi-partition writes as well.

The advantage of this is that with much less overhead, the VoltDB can devote most of its resources to executing the SQL. If the workload is made up of discrete transactions that involve small sets of records as is typical for traditional OLTP workloads or similar modern Fast Data transactions where each transaction is generally focused on a particular entity (user, machine, ip address, etc) then these transactions are executed against data in memory very quickly and you achieve extremely high throughput.

The disadvantage is if you want to run longer running SQL queries that involve scanning many records, as would be typical of traditional OLAP workloads. Such a long-running transaction would cause other transactions to wait for it to complete first before they can be executed. Even if VoltDB can answer some of these queries in a fraction of the time that a disk-based database can, it may not be something you want to run in VoltDB because of the delaying affect on other requests. We have some features such as materialized views that can be leveraged to provide much faster execution time for some of these queries, aggregations in particular. For more complex OLAP queries that cannot be made to run quickly in VoltDB, we typically recommend using export to stream data from VoltDB into an OLAP or "Big Data - Volume" data store, which is designed for those types of queries, but not for high throughput transaction processing.