Hibernate version:2.1.6
Windows XP SP1, 1Gb ram, 1.8Ghz
relationships generated with middlegen
Firebird database

Hi, I'm having trouble with performance of many-to-one relationships, for example:
1) I have a table with 430 records and if I fetch them all it takes 2.8 seconds (the second time it's 0.15 seconds so I guess everything is cached). The latter is acceptable, and about the same amount of time that it takes if I use jdbc & retrieve data into a ResultSet.
2) For another table with 9700 records, it takes 66 seconds (!) to retrieve them all within netbeans IDE and the app runs out of memory if I retrieve the records twice in succession. it takes 52 seconds running in a command shell, 37 seconds if I specify JVM use -Xms512m. CPU runs at 100%, 90-95% of that is java.

In the second example I mention the memory issue because the application takes a huuuge amount of memory - 60+MB?? and it would seem the GC does not have to run so often if the memory available to the JVM is increased, hence less CPU and the process finishes faster.

Anyway, concentrating on (1) which is simply the query "FROM Borrower", I saw with hibernate.show_sql=true that there is an enormous amount of sql running. It looks almost like one sql statement for every many-to-one relationship related to the 'Borrower' table, multiplied 430 times. This must be the problem.
For the one-to-many relationships (5), they specify lazy=true, but for the many-to-one relationships (8), this is not supported, but I'm sure this is what I need, i.e I don't want to automatically load data from tables that this 'Borrower' table is linked to.
I've tried hibernate.max_fetch_depth=0 but it still seems to run all this other SQL.

Can anyone possibly suggest why I hibernate is apparently running all this sql to retrieve data in tables related to my 'Borrower' table? Solve this and I think I'll be able to make all the hibernate go fast ;-)

Here's a small snippent from the Borrower hbm file:




Thanks,
Phil

BTW, I also saw mentioned that if using Firebird, then specify hibernate.statement_cache.size=0, but could not find a detailed reason why. But when I did anyway, it didn't seem to run any differently.

what are you seeing in generated sql? is the to-one side lazy?

Please search the forum and read the Hibernate documentation. TIA

Thank you Thank you! This is what I needed - I added on the to-one side lazy="true" and now the query has come down to 0.2 seconds, and 2.4 seconds for the bigger one with 9700 records (and only 0.35 seconds if only selecting one or 2 of the columns). And only the 1 SQL statement is running :)

This leads me to 2 more questions (which are still performance-related):

a) when I select * from the table with 9700 records, there is a huge amount of memory taken up - 40MB. (Infact I am impressed that hibernate does all this in 2.4 seconds!) Each record can have a 'comment' field which is varchar(2000), although there is often very little in the field. I see in the .hbm that the field is defined as a java.lang.String, length 2000. What does hibernate do - does it always allocate a length of 2000 in a StringBuffer or something? Does it need to know this length for efficiency of internal processing (?) because you don't normally specify the length of Strings...

b) there are many complex relationships in our data model; a good number of them are on the to-one side of many-to-one relationships. Is it ok to simply add lazy="true" to every table by default?
Since we have 105+ tables and the data model is still changing I wonder if I can do this automatically when running middlegen...?

Thank very much,
Phil

I did and I did, which is why I posted a message :)
But I did find my first answer when I revisited my search and added the word 'slow'....

Thanks again

also check fetch keywork (reference guide) in hql and lightweight pattern (wiki), you'll need it