In memory database or Caching solution

[dinsush]

Hello we have an application running, using Spring and database is SQLServer.
The application is giving us problems wrt to performance.
We are thinking of using some caching mechanism in our application.

Any thoughts which is better In memory databases like HSQLDB or H2 or
java caching solutions like ehcache or JCS.

We want to cache lots of data in memory/disk that is primarily queried again and again from database and run our application validations against that data. Basically mostly reads very little writes.

Any suggestions, tips will be very helpful

Thanks
Diana

[Alarmnummer]

The easiest solution would be to use a cache.

What kind of technology do you use for database communication? direct JDBC, and OR Mapper like Hibernate or something else? And do you need to select collections, or just single items?

[dinsush]

We are using Spring JDBC directly. no hibernate or anything else.

More than easy we would like to know which will give me the best results.

we are also thinking of using JDBC directly instead of using Spring JDBCTemplate.

Would appreciate your inputs on this.

Also sometimes we need to access collections, like the entire row from the database currently. or sometimes single item like one column value from the DB.

[Alarmnummer]

More than easy we would like to know which will give me the best results.

I would go for good enough Best results are difficult at best.

we are also thinking of using JDBC directly instead of using Spring JDBCTemplate.

Why? Spring JDBC support only makes JDBC easier to use but doesn't add much by itself.

Also sometimes we need to access collections, like the entire row from the database currently. or sometimes single item like one column value from the DB.

I really don't know enough about your application to point you in the good direction that suits your application best.

But there are many ways to speed up data access. Data that is expensive to calculate, and is allowed to be a little stale (for example a few minutes old is acceptable, a report containing all trains that are too late for a certain station). This can be improved by caching that information. If there is a lot of information to be cached, you could go for a datagrid (tangosol, javaspaces).

So it really depends on the type of applications. And I don't have enough experience to point you in the good direction. Maybe someone with more experience could jump in?

[wpoitras]

If your queries are trivial then an in memory database should be all that necessary. But if that's not the case you can add caching.

I found that a properly designed DAO structure would allow you to change either decision pretty easily. The DAO itself would either implement direct calls to the database or in memory database. You would use the declarative caching support in Spring Modules to add caching with AOP.

For my application, I chose in memory database data and caching. That was because our database doesn't allow direct selects, we have to use stored procedures. Initially I was only going to use the in memory database, but for logic that had a involved dozens of non-trivial queries per operation, the remote stored procedure was more efficient. Once I added caching the performance improved dramatically.

[karldmoore]

I would agree with lots of the previous points. I would also add however, do some profiling and see where the problems are. I helped out on a project where people were introducing all kinds of crazyness to improve performance, turned out changing the SQL had much more impact than all the other changes.

[Paul Newport]

Hello we have an application running, using Spring and database is SQLServer.
The application is giving us problems wrt to performance.
We are thinking of using some caching mechanism in our application.

Any thoughts which is better In memory databases like HSQLDB or H2 or
java caching solutions like ehcache or JCS.

I would take a step back and profile your application to identify where the bottlenecks are, for example, what methods are called the most and what take the longest time and so on.

Your application may well be IO bound but it is much better to check out what is happening first and then identify a solution, rather than trying out a solution first.

It is quite likely that caching would speed up your application (be careful, it needs to be cluster safe), but you could waste a lot of time implementing this if your problem was caused by something else, like, for example, a small connection pool, memory leaks or record locking.

Also bear in mind with Hibernate you tend to cache at the class level, so it makes sense to know which classes are the culprits in any persistence.

[dinsush]

Thanks a lot to everyone for suggestions.
We did some profiling, and found that most of the time was taken in making database trips either for selects or updates.

Well updates we are now using batch processing and for selects, we are planning to use caching.

The reason I am hesitant in using in - memory database is mainly because the amount of data I would like to cache is huge and with large chunk of data HSQLDB, that we evaluated seems to become slow or marginally faster than what we have been doing so far.

I haven't explored tangolsol or javaspaces, would like to explore that, any pointers towards that will be helpful.

Diana.

[wpoitras]

Tangosol, Javaspaces et. al. are certainly options. It how you integrate those technololgies that's important.

Spring Modules caching integrates with Tangosol as well as other free caches (OsCache, EHCache, JBossCache). The issue becomes one of cache capability. What kind of clustering, extra abilities, etc.

[Paul Newport]

Thanks a lot to everyone for suggestions.
We did some profiling, and found that most of the time was taken in making database trips either for selects or updates.

Well updates we are now using batch processing and for selects, we are planning to use caching.

The reason I am hesitant in using in - memory database is mainly because the amount of data I would like to cache is huge and with large chunk of data HSQLDB, that we evaluated seems to become slow or marginally faster than what we have been doing so far.

Diana.

There is a big section in hibernate in action all about caching and performance tuning.

I don't think you'll gain anything from using an in memory database to be honest.

On a final note, don't forget, "Tin is cheap". Sometimes it's just easier to be pragmatic and throw some hardware at a problem. A monster server is after all about the same price as a contractors wages for a week.