Forum: Building VoltDB Applications

Post: Is there a difference between defining sql in java or project file?

Is there a difference between defining sql in java or project file?
francis
Jul 17, 2012
I'm currently designing (more converting a database schema from MySQL) on Volt. I've converted my schema and all seems to be good, however I am now creating procedures.
A lot of my procedures are quite simple, some no more than a "SELECT name FROM assets WHERE disabled=0". I'm wondering if there is a performance difference at scale from defining the SQL in my project.xml versus doing it in a SP.java.
The far more complicated ones are all done in a stored procedure, such as all my inserts and updates.
... the same
rbetts
Jul 17, 2012
There isn't any performance difference. Also if you are doing a lot of simple CRUD operations, you might be able to use some of the auto-generated procedures we create.
http://www.voltdb.com/community/documentation.php
Ryan.
Hi, There is no big
aweisberg
Jul 17, 2012
Hi,
There is no big performance difference. If I had to guess which was faster I would guess that defining them in the project.xml is faster by a very small margin since it avoids running some code.
If you find yourself doing a lot of these single statement things it can be a hint that your not pushing as much logic into the DB as you should. If this results in extra requests to the DB it will have a larger negative impact than doing the work inside a procedure.
Generally what we are doing
francis
Jul 17, 2012
Generally what we are doing for example is getting a list of all items. We add some logic on our code side to figure out which items should be removed, based on locks and the user's profile. Once we know which challenges we keep, we get their full info, including properties, images, etc.
Are you suggesting that it may work to our advantage to get the full item information prior to weeding out the bad ones? In most cases what we have is actually quite simple. Only a few of our modules are complex, haven't gotten to those yet.
Thanks for the info!