DaveWentzel.com            All Things Data

Object Relational Mappers Series - JOINs vs IN Clauses

This is the next post on my series on ORMs.  JOINs tend to be expressed in ORMs as subqueries.  Subqueries are much much easy to code dynamically than JOIN conditions if you think about it.  Think of an entity query like "Show me all salesmen with orders shipped internationally."  This is easier to express dynamically as a subquery than trying to assemble the JOIN.  In this example the performance will be similar because the RDBMS engine will likely rewrite the subquery as a JOIN.  But in complex cases it just can't.  It is well known that many types of subqueries are better expressed as JOINs for performance reasons. 

Now suppose the ORM generates code that is constantly looking at parent-grandchild or parent - great grandchild (or similar hierarchical) relationships.  It's likely a DBA will see these recurring relationships in queries and will carry the parent key into the lower level tables in the hierarchy.  Will the ORM be able to utilize this "denormalization"?  Highly unlikely since again, the ORM wants to build the SQL dynamically. 

When I see these types of performance issues I start to ask whether the ORM is making the developer's life easier or creating performant code for the customer.  Which is more important?