Forum: Building VoltDB Applications

Post: Ad Hoc Queries

Ad Hoc Queries
henning
Mar 1, 2010
How "bad" are ad hoc queries? The manual mentions "interference" and suggest they not be used on a database in production:

"Therefore @AdHoc should not be called in a production environment. The system procedure is provided for development and debugging purposes only."


Is this a cautious legal disclaimer or are ad hocs as evil as this sounds? I can think of quite some situation where checking the database content in an arbitraryway would be a big help. Especially in full fledged production mode.


Could you possibly describe what happens on a deeper level, when an ad hoc is fired - to allow for a better understand of the consequences?


Thanks!
Henning
Ad-Hoc Queries
tcallaghan
Mar 1, 2010
Henning,

Ad-Hoc queries are not optimal for many reasons:

1. They are always executed as multi-partition (even if they could be single-partition). There is no ability to provide partition information (@ProcInfo) to an Ad-Hoc query.

2. They are not constrained by your defined application security. If a user is granted Ad-Hoc privileges then they can select/alter all data in your system. This is the main reason that AdHoc is secured separately from the rest of the System Procedures.

3. A large concern I have is that the Ad-Hoc invocation is a single transaction. If you make a mistake there is no ability to roll it back if you accidentally updated many more rows than you intended.

Having said that, they exist to support the development process (and occasionally production).

-Tim