Thank you for the reply. I made some additional experiments. The query was executed on SqlServer 2008 SP2. I found out that the execution plan was quite silly. After rebuilding statistics, the queries were quite ok, execution plan completely different, execution time from 7min to 16sec.
The left outer joined subclasses do not influence the performance anymore (they are most probably removed by the query optimizer). So for SqlServer this is not an issue. It might be for other databases if they do not optimize this way. Theoretically, this left outer joins could be "optimized away" by the criteria API (only in subqueries). As long as there is no known database having issues with it, it doesn't hurt. On 7 Jun., 16:22, Fabio Maulo <[email protected]> wrote: > the mix between "Table per class hierarchy" and "Table per class" works in > that way. > In practice: > You are querying an hierarchy. NH must query the main table to know the type > related each record and when the type is B NH should initialize all > properties; at this point it needs all fields in the joined table. > The select may end in a N+1 issue. > > We can study some alternative solution but the impl will be not > so immediate. > > On Mon, Jun 7, 2010 at 6:12 AM, Stefan Steinegger < > > > > [email protected]> wrote: > > I have a performance problem with a subquery using the criteria API. > > The subquery is based on a base class, which is mapped all into the > > same table, but uses <join>'s to separate data. > > > The problem is, the subquery always joins all the tables from all the > > subclasses, even if they are not used and do not influence the result > > (left outer join). > > > <class name="A"> > > <subclass name="B"> > > <join table="B"> > > </join> > > </subclass> > > </class> > > > Generated SQL: > > > select .... from X > > where A_FK in > > (select id from A > > left outer join B on ...) > > > What I could do is make the join fetching by select: > > > <join fetch="select"> > > > But this would influence fetching in general, which is not what I > > want. > > > Is there any way to optimize just this subquery? Could this be > > considered a bug, or should I report this as a feature request (to > > remove the joins in a subquery)? > > > PS: AFAIK, there isn't any (recommended) use for this joined tables in > > the subquery. You can't really address these tables. Theoretically you > > can use one of the properties of the inherited classes, but this is > > not very recommendable because criteria "guesses" the type to be > > filtered and just takes the first subtype declaring a property with > > that name. When there are other subtypes with such a property, the > > result will be unpredictable. > > > -- > > You received this message because you are subscribed to the Google Groups > > "nhusers" group. > > To post to this group, send email to [email protected]. > > To unsubscribe from this group, send email to > > [email protected]<nhusers%[email protected]> > > . > > For more options, visit this group at > >http://groups.google.com/group/nhusers?hl=en. > > -- > Fabio Maulo -- You received this message because you are subscribed to the Google Groups "nhusers" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.
