Hey all,
I have a scenario where I have 3 tables that have the same parent object (TABLE_PER_CLASS), but the performance is very slow.

Tested both hibernate 3.6.1 and 4.1.7 with MS SQL 2005, sun jdk6.



Each of the individual objects map to their own table, but the primary key is unique spanning all 3 (if a PK of '12345' exists in any one table, it does not exist in the others). There is no 'animal' table itself, such as the above there are three tables.

To be clear - a farm will only have zero or one 'goose' object, one 'chicken' object, one 'duck' object for the scenario I'm testing, but an unknown number of different types of animals. Although I think the many of the same objects should also work, I'm focusing on current scenario.


When I have a named query for a Farm entity that contains many animals.




The problem: named query "select farm from Farm farm join fetch farm.animals ....(other join fetch non-inheritance)... where farmid=:farmid"

When I have many farms (>100) and each farm has several animals (0-3) I'm running into some serious performance problems.

1) setMaxResult is *not respected* unless I remove the join fetch on the inheritance animals. So it always returns all farms and all animals (in my scenario, hibernate 3.6.1 was 33 seconds, hibernate 4.7.1 was 18 seconds for 308 farms)

2) removing the animals inheritance in the join-fetch (while having 5 other 'non-inheritance' objects) allows setMaxResults to properly 'top' in MS SQL, as well as speeds of 150ms for 100 paginated results (and 300ms for all 308, to compare to above 33sec and 18 sec). The raw query time was insignificant.

3) the raw query (using show_sql) directly through the database is very fast even with the join fetch to the inheritance animals (worst case scenario) and was only 225ms (total result of 655 records for the 308 farms). the 655 results was from the join to the (Select... union all select ...) for the individual animal tables.

I could get by with 'all', but something significantly increases the 'all results' time from 300ms for Farm with other objects and no animals, to 18 seconds (33 seconds older version) when including animal objects.

question: is this expected behavior when using TABLE_PER_CLASS inheritance, and what are some options to resolve the performance problems (while still trying to stay as close to JPA if possible).


thanks!
-Darren

RESOLVED:

Using JTDS 1.2.4 or JTDS 1.2.6 JDBC drivers with the above scenarios continue to be a problem.

However, swapping to Microsoft's JDBC drivers (3.0.1301 version, specifically sqljdbc4.jar for jdk6/JDBC4 support) resolved the performance issues (full result in 670ms)

This appears to not have been a hibernate, JPA, or configuration problem, but a problem involving the JTDS jdbc driver. Sorry for the alarm, however I hope this helps someone else who runs into similar challenges!

UPDATE/RESOLVED EDIT:
For some reason, the microsoft JDBC driver started behaving similar to the prior JTDS drivers without any changes....

It appears, there is more -- at the end of the jdbc url, also add ';sendStringParametersAsUnicode=false'


NOTE: This does *not* correct the setMaxResults still not sending a 'top' for MS SQL when using TABLE_PER_CLASS inheritance joins, but this may be an unrelated problem.
reference: HHH000104: firstResult/maxResults specified with collection fetch; applying in memory!

-Darren

The problem started occurring again, and it wasn't gradual -- suddenly went back to the old performance numbers (after a couple of days of testing and other changes).

Now, after trying things like clean temp (old JTDS tmp file days), restart box, change JDK6 to JDK7, monitoring sql server 2005 (seeing SUSPENDED/CXPACKETS), just on a hunch tried *removing* the sendStringParametersAsUnicode=false that originally corrected the issue...now back to fast/expected performance?!

I'm sharing this in case other people run into similar issues (and seems to be prevalent only in the solution that uses TABLE_PER_CLASS strategy, but may not be the only scenario) where Sql Server query is fast, but 'using hibernate' suddenly becomes very very slow, it may be something in-between and not necessarily hibernate...although do not know what that is at the moment.

EDIT: although I can not share the solution, it is using NamedQueries, all JPA code (not hibernate specific).

(potentially useful reference for later: http://stackoverflow.com/questions/8948872/jpa-hibernate-native-query-for-prepared-statement-slow)

LAST EDIT: Although not a good resolution, the SQL server DBA's did a 'flush cache' (which I guess is on the full database server) in our test environment that has the issue, and suddenly everything is good now. Current prognosis is a bad preparedStatement plan, which when you change Unicode or SelectMethod will use a different plan anyway (why it sometimes helped, then went downhill later).

-Darren