I'm using some code similar to that listed below for batch updating a database. Note, I know that I should be using batch updates and that's what I plan on doing. However, setting aside updates, I want to know why I'm seeing slow performance with queries. When I run the following code on a data file which has about 23,000 rows in it and the rows already exist in the SQL table, for some reason the queries get progressively slower as it goes through the list of user ids. The user ids are in sorted order. The user id column in the table has a unique index on it and performance is good when updating the table using plain JDBC with SQL. Also, I noticed that the CPU is maxed out on the client when running the program while the CPU on the database server is low. I'm guessing that the problem is maybe cache related? I'm also seeing the following warning about ehcache which I'm wondering might have something to do with it. The other thing that I'm wondering is is maybe my hashCode() method might be implemented poorly. Currently, the hash code returned for the User class is just the id value of the object. I put debug statements in that method and it doesn't appear to be being called, so, I don't think that's what it is. Has anyone else run into this problem or have any ideas as to what might be the problem?

Feb 15, 2005 6:53:26 PM net.sf.ehcache.config.Configurator configure
WARNING: No configuration found. Configuring ehcache from ehcache-failsafe.xml found in the classpath: jar:file:/C:/hibernate-3.0beta4/lib/ehcache-1.1.jar!/ehcache-failsafe.xml

Hibernate version: hibernate-3.0beta4

Code between sessionFactory.openSession() and session.close():



Name and version of the database you are using: MySQL 4.0.13

The generated SQL (show_sql=true):

select user0_.id as id, user0_1_.userId as userId2_, user0_1_.department as department2_, user0_1_.emailAddress as emailAdd4_2_, user0_1_.firstName as firstName2_, user0_1_.lastName as lastName2_, user0_1_.middleName as middleName2_, user0_1_.telephoneNumber as telephon8_2_, user0_1_.buildingName as building9_2_, user0_1_.city as city2_, user0_1_.roomNumber as roomNumber2_,
user0_1_.stateAbbreviation as stateAb12_2_, user0_1_.streetAddress as streetA13_2_, user0_1_.ZIPCode as ZIPCode2_, user0_1_.name as name2_, user0_1_.title as title2_, user0_1_.organization as organiz17_2_, user0_.userId as userId4_ from User_ user0_ inner join Person user0_1_ on user0_.id=user0_1_.id where (user0_1_.userId=? )

(1) understand the use of Session.clear()
(2) understand the use of FlushMode.NEVER
(3) in HB3 understand the use of Query.setReadOnly()

The ehcache log message just says you don't have a config for that entity, so is using the default. No problem there.
As Gavin implied, you need to clear the session occasionally. You are currently hydrating everything you select into objects, and not clearing them from the session. 23,000 of these could get a tad slow! Keep a count and session.clear() every 20-100 or so (test it for the optimal value).
Hibernate is not optimized for batching. Use JDBC if you need huge batches, or an SQL script. Hibernate is an O/R tool.
Look up Gavin's suggestions in the docs. It's all there.

see Gavin's batch suggestions at http://blog.hibernate.org/cgi-bin/blosxom.cgi/index.html?find=batch&plugin=find&path=

Thanks guys, much appreciated. I tried it with calling clear() every 100 rows and that made a big difference. Currently, I'm just calling evict() after updating a given object and committing it's transaction. Since the objects are processed in order, I figure I may as well just evict it immediately after the insert/update has been performed. Now, it takes about 00:02:59 to do an initial load of the table which has 25,000 rows and 00:01:51 to do an update with the rows already having been loaded (basically just doing queries at that point). If I remember correctly, when I did it using JDBC batch updates manually, it took about 00:01:30 to do the initial load. So, it's a bit slower, but, I'm not complaining at all. I don't mind a little bit of slowness with a program that is easier to maintain.

What I'm wondering though, is why would having a lot of entries in the cache slow it down so much? I was thinking that it would probably just be looking the objects up using a Hashtable, so, the lookups would be fast and it wouldn't cause a major performance hit. But, I guess there is a lot of other stuff going on that slows things down like checking to make sure the cache is in sync?

Think of it not as a Hash of objects, but as a persistence system. It is watching for changes to objects, managing their associations, calculating foreign constraints, etc. Hibernate manages the whole thing, so I am not surprised it is slow for mass operations. It is not built for that, nor does such work need Hibernate.

I am still not convinced Hibernate fits your use case, unless their are other cases it is useful for. A few tables with tens of thousands of rows that need to be updated regularly is not a use case for Hibernate. SQL scripts are the appropriate tool, I think. Same with batch inserts. The speed difference is enormous compared to Hibernate or JDBC. Databases are very powerful, not just a place to put your objects when you reboot.

Ask any DBA, and they'll argue that such work is best done in the DB itself. Of course, they think everything should be done in the DB!
Good luck!

One question that I have regarding Gavin's example is about how everything is wrapped in one transaction. Wouldn't that cause performance issues in terms of locking? I didn't think it was recommended to wrap such a massive update all in one transaction. Wouldn't be be better to do something like make the transaction size the same as the batch size (i.e. 25 to 50 rows)?

The other issue that I ran into when trying to implement something similar to what he has is that I needed to use "fetch" like in the following HQL in order to prevent additional queries from being executed while processing each row. However, I received an exception when trying to do that using Query.scroll() stating that you can't use fetch with scroll or something along those lines.

from User u left join fetch u.departments

So, what I did was a Query.list() and set the FlushMode to NEVER. Then, I just had a single flush() and commit() after my while loop. This isn't the most efficient memory-wise, but, the number of rows that I'm processing will always be around 25,000, so, it isn't too much of a problem.

The only issue at this point is with regard to my question about transactions. I have everything wrapped in a single transaction. I'm thinking it would be better if I could make the transaction size about 50 rows instead to improve concurency?

You can try "select new MyClass(...) " it must be better.