Why doesn't NHibernate limit the sub query of a union subclass to a particular id?

Question

Ayende in his post on NHibernate Mapping – Inheritance outlines how the various NHibernate inheritance strategies work. His explanation of union-subclass shows the SQL generated as using a sub query which unions the various tables. This subquery is then selected from.

What I don't understand is why when referencing an object mapped this way the sub query is not limited by the referenced id. I am concerned because this seems terribly inefficient. As the number of records in the Companies and People tables increases selecting everything from each will take a long time. Simply limiting the sub query by the id of the Parties object would at least grab the specific record from each table rather than all records and then limiting after.

To better illustrate the point the SQL generated is approximately:

select this_.Id            as Id2_0_,
       this_.FirstName     as FirstName3_0_,
       this_.CompanyName   as CompanyN1_4_0_,
       this_.clazz_        as clazz_0_
  from (select Id,
               FirstName,
               null        as CompanyName,
               1           as clazz_
          from People
        union all
        select Id,
               null        as FirstName,
               CompanyName,
               2           as clazz_
          from Companies) this_
 where Id = 123

When referencing a particular Party why is the generated SQL not

select this_.Id            as Id2_0_,
       this_.FirstName     as FirstName3_0_,
       this_.CompanyName   as CompanyN1_4_0_,
       this_.clazz_        as clazz_0_
  from (select Id,
               FirstName,
               null        as CompanyName,
               1           as clazz_
          from People
         where Id = 123
        union all
        select Id,
               null        as FirstName,
               CompanyName,
               2           as clazz_
          from Companies
         where Id = 123) this_

The second seems more efficient and assuming appropriate indexing will not be harmed by growing Companies and People tables. This question was sparked by further research into my question: how to map a UnionSubclass so queries generated from it are limited.

Answer 1

Did you check the execution plan of the first query?

I found out that in similar scenarios sql server is surprisingly good at optimizing the query and knows that it should not do a full table (or even index) scan. It usually finds the relevant indexes of the queried tables. Maybe that is what NHibernate relies on. This will probably apply to other RDBMSs as well.