Hibernate slowdown in Beta3

[jamoore]

All Hibernate access seems to be signficantly slower in Beta3. This may be due to lucene integration, db changes (annotations,...), etc. Profiling needed.

[jmoore]

r2680, r2681, r2685, r2687 closes for now.

In looking over various slow queries (like those pointed out by Chris while working on #1051) 3 things showed up:

1. Annotation loading

When a query is made over annotation links, and the annotations themselves aren't loaded, Hibernate will load them one at a time to check the concrete class. I.e.:

    iQuery.findAllByQuery("select p from Pixels p left outer join fetch p.annotationLinks", null);

will results in 1 query for the pixels and annotation links and N (in my case 100) queries for each linked annotation. This can be solved by using:

   ...Pixels p left outer join fetch p.annotationLinks link left outer join link.child...

I tried changing the Hibernate annotations on the objects (@Fetch(JOIN) and @Fetch(EAGER) but couldn't achieve an automatic result.

For the moment this means: Always load annotations even if you don't need them.

2. Ordered collections

Due to an error on my part when I was converting to using Hibernate annotations (r2016) an index was added to ordered collections incorrectly. For example, for Pixels the constraint "UNIQUE (id, image_index)" was added rather than "UNIQUE (image, image_index". This has been corrected in database OMERO3A__9.

3. Adding UNIQUE (parent, child) to links

Once any link table grows sufficiently large, joins over the links start to suffer. Working with 15K pixels, 100K pixelannotationlinks and 100K annotations, EXPLAIN ANALYZE was returning 1.5+ second times. Adding UNIQUE(parent, child) reduced the times down to ~1.5 milliseconds. However, it is now somewhat difficult to add those unique constraints since duplicate links exist in most databases. Where those links are owned by the same person/group, we can automatically remove them. What do we do if they aren't?

The changes for all link types are available in OMERO3A__10.

Closing this issue. We can decide whether or not we move to OMERO3A__10 elsewhere. If other specific queries prove to be similarly slow, please open a new ticket and outline the problem.