Pramatr Blog

Embedded databases are proving pretty popular with many teams finding their usage extremely appealing. Many embedded databases have impressive features; easy to integrate into the software, low administration requirements, very low footprint and generally extremely fast. It is for this last reason that some people look to these databases, but it is also for this reason that they need to be handled with care.

Just like OpenOffice, a previous project I worked on used an embedded version of HSQLDB. We had started to have several performance problems so engaged in some profiling to find potential areas for improvement. After staring at several screens full of SQL debug, one thing became apparent; our system was producing a huge number of SQL statements. This was accompanied by the realisation that it was also producing a huge number of exactly the same SQL statements.

As the embedded database was extremely fast, there had never been any performance problems before. In fact the database was not the bottleneck in many of the application operations. As the application grew more complex however, performance problems began to arise quite quickly. These problems only got worse when the numbers started to increase. Complexity + Scaling != Success. The extemely fast embedded database was now starting to be a real problem, some serious changes needed to be made.

Many of the database performance problems were resolved quite quickly by simply re-learning the most basic best practices, just because the embedded database was quick these should not have been ignored;

• Connection pooling can improve performance
• Statement caching can improve performance
• Batching statements can improve performance
• Performing SQL n + 1 selects are a bad idea regardless of how quick the database is
• Caching common SQL results can improve performance
• The database needs to be profiled to see exactly what SQL is getting executed against it

It may be possible to ignore these issues early in the project, but as the complexity increases and the application is expected to scale they can begin to be exposed. Embedded databases performance can be impressive, but make sure you handle with care.

Over the past few years there have been numerous posts on Spring Forum regarding the performance of JdbcTemplate. The majority of these are complaining about poor performance of JdbcTemplate in comparison to pure Jdbc. I thought this topic had been done to death already, but a friend of mine told me his developers were having performance problems with JdbcTemplate as recently as last week. They had switched back to Jdbc after some performance comparisons had proven JdbcTemplate was significantly slower.

So what makes JdbcTemplate so much slower than pure Jdbc? In a word…….. nothing. JdbcTemplate does a great job of removing the try/catch/finally/try/catch code we used to write with Jdbc, but under the covers it’s just doing the same old stuff we used to do. It’s getting hold of a connection, executing a statement, possibly mapping a result set to an object, converting any exceptions for us and releasing the connection. That’s pretty much it, it’s a thin wrapper around Jdbc that makes it usable in the real world.

So why do people report performance problems with JdbcTemplate? I took the opportunity to review the comparison that had reported such terrible performance compared to Jdbc. After a couple of minutes reviewing the comparison it was quite obvious it was fatally flawed, not only was it configured differently, it wasn’t testing the same thing. Looking at the comparisons that have been posted in the past on Spring Forum, they typically suffer from the same problems. The most common is they generally don’t perform a fair test. Lets look at the typical problems the comparisons face before seeing the comparison results once the problems were fixed.
Read the rest of this entry »