I have a Problem with a sql statement NHibernate generates when it comes to subquery joins. I have 2 tables, TableA and TabbleB, that are mapped to classes but without a mapped connection (OneToMeny/ManyToOne). These tables are actually complex views containing a huge amount of data each (several milions of rows).
In my code it is neccessary to build complex queries in both tables and connect them to one result. The conditions of those queries are different in each occurence in code but I managed to build them all in one big query class. That works perfectly and is very intuitive and readable code. I use a query like this simplified one to get the results of my requests: var sub = Dao.Session.Query<TableB>().Where( ...condition... ); var result = Dao.Session.Query<TableA>().Where( p=> sub.Any( q=> q.RefAId = p.Id ) && ... ).ToArray(); Of course both query trees of TableA and TableB are attached to complex where conditions but these aren't neccessary here. The sql statement genereted by NHibernate looks about like this: SELECT * FROM TableA a WHERE EXISTS( SELECT Id FROM TableB b WHERE (a.Id = b.RefAId OR a.Id IS NULL AND b.RefAId IS NULL) AND ... ) ... This is technically correct and works fine but is very slow because of the "...OR a.Id IS NULL AND b.RefAId IS NULL" part. The Oracle optimizer doesn't like "OR" whithin join conditions and the "... IS NULL" statements lead to full table scans because nulls are not content of any index. I have a runtime difference of 30Minutes with the OR part and 0,05 seconds without. I mapped both properties of the join as NOT NULL (TableA.Id is the Id column) but still NHibernate generats the "OR" part. If I add " && q.RefAId != null" in the join condition of the request it gets better because then the "NOT NULL" signals oracle to use a different execution plan and the runtime goes down to several seconds, still too long. Only way to get rid of the "OR" part is to make a mapped connection between the tables and rewrite my query class to statements like this: var result = Dao.Session.Query<TableA>().Where( p=> p.TableBRefs.Any( q=> ...condition... ) && ... ).ToArray(); Then the sql generated looks like this: SELECT * FROM TableA a WHERE EXISTS( SELECT Id FROM TableB b WHERE a.Id = b.RefAId AND ... ) ... This statement ist exactly the same as before but without the "OR" part and now it ist fast (0,05 secs). Only Problem I have is that my query class would be very ugly, containing a lot of copied parts to handle the different combinations of conditions of my complex requests. This would blow up my code unneccessarily... Is there a way that NHibernate realizes when the "OR" part is usefull and when it can be skiped (for exaple if both columns are NOT NULL mapped)? That would be a great help to speedup my project. -- You received this message because you are subscribed to the Google Groups "nhusers" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/nhusers/2c66ffe7-0c8e-416e-be1c-912c648c5761%40googlegroups.com.
