I have a fairly simple use case where I've implemented this:



I had Netbeans 5.5.1 generate an Entity Class for theTable backed with Hibernate + Annotions + EntityManager and then executed this:

log.debug("Creating query now...");



That named Query is:



Now:

1. The JDBC takes about 3-5 seconds
2. The Hibernate / JPA version takes about 19-20 seconds

As a side note, I had:

1. Perl plain DBI take about 4-5 seconds
2. Rose::DB::Object with a manager took < 1 second to count them
* but iterating took about 4-5 seconds

I did attempt to force almost all the rows on the TheTable to be lazy load and this didn't seem to make a difference. Using a DB connection pool seemed to make no difference.

Any comments?

DSL

There is a difference between those two queries. Have you checked the SQL generated by Hibernate? E.g. in case TheTable extends some other persistent classes, that content will be fetched as well. If the generated SQL code is almost as plain as "SELECT * FROM theTable" there should not be a big difference in speed.

Actually, I've done a simpler test and it does appear that my auto-generated classes might be doing something

Firstly, I created an Entity "Address" with:



This is the only entity managed by the EntityManager/Persistence Unit and I coded it myself. Consequently, there's NO relationship mapping. I stacked 50 000 records in, double checked they were there using "mysql" and then ran the count.

It took only 3 seconds.

I'll check what my code was doing when I get access to it (it's at work) :)

Thanks for your response,

DSL

Sounds simple, but you should think about of really having 50.000 objects that need to be created and kept in memory. You should either choose to get the count of the collection if you're interested in the total number of entries or setting the maxResults for your query. That should be much faster anyway. I can't image you would like to really iterate over all 50.000 entries ;).

Look at the generated SQL to be sure what's executed.

Cheers,

Christopher

Back at work, I had Netbeans regenerate just the single table that has the > 50K entries. I based this on the theory that JPA/Hibernate won't load associations for entities that don't exist.

Result:

20-23 seconds

I then annotated a heap of fields with:



This took it to:

18-23 seconds

Subsequently, I took a look at the actual SQL that Hibernate was performing, copied it directly into my JDBC version and then got:

3-5 seconds

The usual number this thing would have to iterate through is about 2500-5000 with 25 000 being the sensible maximum. The service that might cause that many transactions to go through at once is unlikely to go down for more than 5 consecutive time periods (so I got 25K from 5 x Sensible Max).

I just find it interesting that a Perl solution using Rose::DB::Obeject actually ran significantly faster. I strongly suspect it's because the Perl ORM doesn't need to objectify and translate the DB values as much as Hibernate (or any other Java solution) would need to.

Thanks for your help, again!

DSL

[code]
Now:

1. The JDBC takes about 3-5 seconds
2. The Hibernate / JPA version takes about 19-20 seconds
[/quote]

But did you count the orm mapping time? o just the code ?, i've made complex querys with hibernate and the code generated wasn't bad at all