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.