Hi Group,

Queries (.find, .createCriteria both do this) seem to run slowly when the session cache is large. I'm running a query against a table with zero rows in it, and it takes 7 seconds! Any ideas why? Here's some test results:

-Run query in a junit test with a new session, 16 ms.
-Run query in the at the end of a business process that loads tens of thousands of records (none from this table, of course, since it is empty), 7.5 seconds.
-Same as above but do a clear() right before running, 16ms.

Is this expected behavior? Is there anything that I can do to avoid this problem besides clear()?

Big thanks to anyone who can help with a tricky situation!

Thanks,

Jeff
Silver Ridge


Hibernate version:
2.1.6
Mapping documents:
-super simple stuff-
Code between sessionFactory.openSession() and session.close():
either session.find("from MyClass") or session.createCriteria().....list();
Full stack trace of any exception that occurs:
-none-
Name and version of the database you are using:
IBM DB2 UDB 8.1.6 on AIX
The generated SQL (show_sql=true):
A perfectly good simple select against a single table. The table contains no rows.
Debug level Hibernate log excerpt:
-can't get hibernate debug logging to work in WebSphere unfortunately :(-

yes it seems normal.

All depends on what you mean by business process...

The session should be as atomic as the business process.

We know that some _application transaction_ requires many screens or a huge number of objects...

How are you managing your session?

Thanks Anthony,

Basically, we load data from table a (one record), and dependant tables b and c (20,000 records each). It is a total of approximately 40,000 records.

We iterate over the 20,000 records from table b, performing some validations and some calculations and frequently but not always updating the corresponding records from table c.

There is a fourth table, table d, that is dependant on table c. Occasionally we lazily load the records from table d when they are needed. This is why it is useful to leave all of this data on the session for the duration of the process, rather than immediately calling clear() after a, b, c are loaded.

About 2% of the time we also need to check table e as a part of the validation logic, but this query takes a very long time (even if we flush() immediately before running the query!).

Seven seconds of delay for each time table e is queried is enough to cause our application to fail (it adds 45 minutes to the whole process!)

I assume that this behavior is due to performing dirty checking on the entire session cache. Is there no way to prevent this behavior? The query that I am running is unrelated to the data in the cache. I know this for certain, and I would like to be able to run the query without considering the session cache in this case.

Perhaps we are using Hibernate for an inappropriate use-case?

Thanks!

Jeff

So, if the problem is that dirty checking on 40k objects requires 7 seconds, (is this right?) then a follow on question:

If we use a second-level cache, will we have the same problem? We had planned to use a second-level cache to pre-load a half million records that are used by a variety of processes, but if we had to suffer through dirty checking on 500k objects every time we wanted to do a trivial query then it would not be worth trying.

Am I way off base here?

Hey,

This is more a question to the Hibernate guys, but maybe it would work and maybe you could use it Jeff.

What if you constructed two sessions, both on the same connection (one of them however you normally do it, and one of them using the createSession(Connection) method).

Then you could do your really big query (the one with 40,000 objects) on one of the Sessions, and do all of your other queries and updates on the other session (which will ahve very few objects loaded into it and so perform well).

I mean, I've never tried anything like it, so I've no idea if it would work.

And there'd be plenty of complications, for instance for any of the objects you had loaded in the large-session, you'd need to re-load them in the small-session in order to manipulate them. Whether or not it would perform well (assuming the basic idea works) would still be entirely dependant on how much stuff you do in the small session.

Oh, wait, I just saw that you said that you frequently update the rows from table c, so I guess this pattern won't work for you.

But (sorry for hijacking your thread), to HB guys, would this work, if you didn't do too many updates on the small-session?

The reason I'm asking is that we will have to do this soon, and I was assuming we'd have to drop back to JDBC (ugh!); but if this pattern would work reasonable well, it would be much better (it'd save us heaps of re-coding/re-factoring business logic code).

We'd be reading as much if not more data than Jeff does, but would only be doing updates infrequently (on the order of a couple hundred updates at absolute maximum).

Shorn,

Very clever idea. I bet we can use this to our advantage. We have one table (table e) that we only read (no updates). If we move only that one table to another session we would probably gain enough performance to make this work-

Thanks for the brain cells!

Jeff

Let us know how it goes, eh?

Like I said, I've never tried anything like this, so I'd be interested to hear about any gotchas.

Coz if the basic idea is sound, it makes a big difference in our estimate of the work involved with doing batch update processing.
It'll take more time to actually run comapred to JDBC, but we've got a big window of opportunity to run it in, and the effort saving is very much worth a couple extra cpu minutes, for us anyway.

Just in case anyone else is interested:

I've confirmed that the dirty checking is the culprit. This behavior is expected in Hibernate because it is automatically flushing before running a query, to ensure that you get up-to-date results back in your query results.

Valid solutions include:
-use JDBC for additional SELECTs once the session is large (lame)

-periodically clear(). in my case this isn't great because i periodically fetch lazy associations. we can .lock(object, LockMode.NONE) to reassociate with the session before each time we do this. this works but seems to be polluting the application code with some trickery

- create a new session for read-only DB accesses, associate it with the thread as well, whenever using this session clear() before running a query. keep all data updates on the main session. it seems from a teeny junit test that I did that this will work, and creating a session seems to be lightweight. thanks stolley.

It sure would be neat if there was a way to run a query while giving Hibernate a hint "My dear friend, you can trust me, please do not flush() before running this query.". Something along the lines of FetchMode.GODDAMMIT_MAN_I_HAVE_NO_TIME_FOR_DIRTY_CHECKING_AT_THE_MOMENT.

:)

Thanks Hibernate Guys.

-Jeff

session.setFlushMode(FlushMode.COMMIT)

D'oh! Oh, yeah.

Jeff, you might wanna check out Gavin's post about this stuff: http://blog.hibernate.org/cgi-bin/blosxom.cgi/2004/08/
It's one of the things that originally made me think we'd have to do this in JDBC.

Gavin:
Yeah, sure, COMMIT is accurate, precise and concise.
But what GODDAMMIT_MAN_I_HAVE_NO_TIME_FOR_DIRTY_CHECKING_AT_THE_MOMENT lacks in those things it makes up for in "zazz".

Big thanks! All of my problems solved.

Well, one big problem solved anyway ;)

Thanks again Gavin and Stolley.