There are two problems with the "or null" work-around, both are performance issues. 1 - having the criteria in the where - means that the size of the join is not restricted by those criteria, unnecessarily increasing the number of rows initially pulled in. 2 - "or null" means the database won't be able to use the index on that column - which is bad in general, crushing in the case of a multi-tenant database.
Both of these problems are amplified the longer the chain of joins. Bob S On Friday, October 14, 2011 10:39:20 AM UTC-4, Gunnar Liljas wrote: > > Hi Adrian, > > This question pops up from time to time (search the archive), and the fact > that NH filters on the WHERE rather than on the JOIN has been reported as a > bug. In the meantime, using the "or null" workaround is a proven practice, > so go ahead and use it. I suggest that you add the tenant id to all tables > which aren't explicitly "non-tenant", i.e. even "child items" etc. It's > safer, more predictable and when time comes to extract/backup/restore/index > data for a specific tenant, you will be glad that you have the tenant id in > every table. > > /G > > 2011/10/14 Adrian <[email protected]> > >> I am using an NHibernate Filter to ensure that every query in my multi- >> tenant system includes the tenant id. Every object that has the >> tenant id column has the filter assigned, and it's enabled 100% of the >> time, because I can't afford to cross tenant data. >> >> This works great until I need to do an outer join to a table that has >> the tenant column. The filter is applied on the Where clause, >> effectively turning the outer join into an inner join. >> >> Is there a recommended way to approach this? Is a Filter the best >> approach? If so, I need the filter to allow either NULL or my tenant >> id (safe because the tenant id column is not nullable so a NULL value >> would only occur on an outer join), or I need the filter to be applied >> on the join itself (like a WITH clause). >> >> Thanks in advance, >> Adrian >> >> -- >> 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. >> >> > -- You received this message because you are subscribed to the Google Groups "nhusers" group. To view this discussion on the web visit https://groups.google.com/d/msg/nhusers/-/W55bIqig7bgJ. 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.
