Hibernate version:3.0.5, 3.1.3

Name and version of the database you are using: Oracle 10g

Cache implementation: JBoss Cache, OSCache, EhCache

I have a query that returns 1500 items as a result. I am caching enabled for both the query and the object. The retrieval of this dataset on subsequent request is very slow (200ms - 2s). The situation gets worse in the multithreaded environment. With 50 threads accessing this information simultaneously, the retrieval time starts approaching upto 1minute.

Has anyone encountered this behaviour with second level cache before? I would think that retrieving the data from the cache will be fast. I haven't had this issue with just using the JBoss Cache before. It seems like Hibernate is introducing this delay. Any ideas?

Regards,
Atif Khan

Atif,

A couple of questions for you:

1.) what is the query & supporting Java call that loads this data?
2.) Is your cache clustered or does it run in a single instance?
3.) what cache strategy are you using? Read-only, read-write, etc.


Ryan-

Ryan,

Here are the answers:

1). Query:
where CRONumberDetailsResult is a view in Oracle aggregating few tables. There are no joins defined on the mapped Object. It's pretty much a regular Java Bean with 16 attributes.
To get the data I get the named query first and then call list() on it.

2). Cache is not clustered.

3). Cache strategy is set to read-only.

--Atif Khan

Since your strategy is read-only, I'll assume that the data is immutable within the application? Some things to look for:

1.) even though you've enabled the query cache, each HQL query must set cachable = true:



If you don't set cachable = true, you'll always be hitting the database.

2.) There's a difference between query.list() & query.iterate(). Query.list() will get all of the elements in one pass and doen't hot the L2 cache. Query.iterate() will issue a query to load all of the PKs in the result set. As you iterate over the results, it will check the cache for an entry.

One last thing to check: how long does the plain JDBC query take to execute & return the same set of data?

Hope this helps.

Ryan-

1). Query is set as cacheable="true" in the mapping file. After the first request it's pulling the data from the cache.

2). I am not sure what you mean by "hot the cache" here, but I can try the iterate option as well.

3). On an average the direct JDBC takes 55ms.

--Atif Khan

Sorry Atif, my fat fingers got in the way. I meant to say "hit the cache."

Ryan-

The iterate() didn't solve my problem. In fact it introduced other issues, as the object type returned by the Iterator is not the mapped object type, but it is a CGLIB enhanced class.

Also, the list() method seems to insert/retrieve the query results properly into/from the cache. So, the only difference between the list() and iterate() is that in case of iterate() Hibernate does lazy loading

Thanks
Atif Khan

Hello,

I don't know if you're still watching this issue, but I am encountering the same performance problem as you.

Did you find any solution?

We decided to not use the Hibernate second level cache for this particular object. We instead cache it on our own now. Hibernate seems to have issues with a large collection of objects.

--Atif Khan