wood burning stoves 2.0*
The moose likes JDBC and the fly likes H2 Database speed: file vs. tcp Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCM Java EE 6 Enterprise Architect Exam Guide this week in the OCMJEA forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "H2 Database speed: file vs. tcp" Watch "H2 Database speed: file vs. tcp" New topic
Author

H2 Database speed: file vs. tcp

Bobby Smallman
Ranch Hand

Joined: Sep 09, 2010
Posts: 107
Hello Ranchers,

I have encountered something which I find counter-intuitive and therefor probably has a direct problematic cause. I am getting significantly slower performance from my h2 database when connected to the database directly to a file (embedded only mode) vs local server (tcp://localhost:8082). Using the exact same identical program with the only change being the url of the database there is a massive performance hit when connected straight to the file instead of through a local server. My question is, what factors could cause the database to have slowdown when accessed via embedded mode that would not be a factor when connected via a local server connection.

Thank you for your time in reading my post.

EDIT: After more testing, it seems the culprit is not the difference between file and local server connections necessarily. While connected to the local server via tcp, the program moves very fast ONLY if I am also logged into the database in the h2 console. If I am not logged in in the h2 console, the speed of the applications connections are slow again. Hopefully someone here can explain that :-)

EDIT2: Solved this one with some help from a friend and many hours of searching. My problem was the database was closing its connection after every single query. Two solutions I found to be effective for those of you stumbling upon this post looking for answers. Firstly you can manually set your database to either delay automatic closing of connections or even make it NEVER do so. A more sophisticated approach is to use connection pooling.


Everyday in every way, we get a little better.
 
jQuery in Action, 2nd edition
 
subject: H2 Database speed: file vs. tcp