Embedded Database Performance: Handle With Care
Posted by pramatr on February 10th, 2009
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.