Best Practices for Designing for Performance from the Start - Professional SQL Server 2005 Performance Tuning - page 508

SELECT INV.*, CUST.CUSTOMERDESC as BILLABLEENTITYDESC
FROM INVOICE INV
INNER JOIN CUSTOMER CUST
ON CUST.CUSTOMERID = INV.ENTITYID
AND INV.ENTITYTYPEID = 100001
In contrast, the second example models a super type entity called a BillableEntity. Each subentity asso-
ciates itself with the billable entity. This attribute is common to both subentities, namely the description
is moved up into the super type. This enables the Invoice entity to have a single relationship to a billable
entity. The query is also easier and more efficient from an optimizer perspective.
SELECT INV.*, BI.BILLABLEENTITYDESC
FROM INVOICE INV
INNER JOIN BILLABLEENTITY BI
ON INV.BILLABLEENTITYID = BI.BILLABLEENTITYID
The query plans will show you what you already know. The first query has to pass the clustered index
twice to examine the invoice table under each of the subtype conditions. Look for the Clustered
Index Scan operations in this plan for the first UNION-based query.
Merge Join(Union)
|--Nested Loops(Inner Join, OUTER REFERENCES:([INV].[EntityId]))
| |-- Clustered Index Scan (OBJECT:([Invoice1].[PK_Invoice1] AS [INV]),
WHERE:([Invoice1].[EntityTypeId] as [INV].[EntityTypeId]=(100000))
ORDERED FORWARD)
| |--Clustered Index Seek(OBJECT:([Carrier1].[PK_Carrier1] AS [CAR]),
SEEK:([CAR].[CarrierId]=[Invoice1].[EntityId] as [INV].[EntityId])
ORDERED FORWARD)
|--Nested Loops(Inner Join, OUTER REFERENCES:([INV].[EntityId]))
|-- Clustered Index Scan (OBJECT:([Invoice1].[PK_Invoice1] AS [INV]),
WHERE:([Invoice1].[EntityTypeId] as [INV].[EntityTypeId]=(100001))
ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([Customer1].[PK_Customer1] AS [CUST]),
SEEK:([CUST].[CustomerId]=[Invoice1].[EntityId] as [INV].[EntityId])
ORDERED FORWARD)
By abstracting the carrier and customer entities into a billable entity, only one pass is needed on the
invoice index. This can be seen in the difference in the total subquery costs of .018 for the multi-use
column option and .006 for the single-use — a 66 percent difference.
Nested Loops(Inner Join, OUTER REFERENCES:([INV].[BillableEntityId]))
|--Index Scan(OBJECT:( [Invoice2].[IX_INVOICE2_BILLABLE_ENTITY_ID] AS [INV]))
|--Clustered Index Seek(OBJECT:( [BillableEntity].[PK_BillableEntity] AS [BI]),
SEEK:([BI].[BillableEntityId]= [BillableEntityId] as [INV].[BillableEntityId])
ORDERED FORWARD)
You may think that you haven't really done anything by pushing the abstraction down one layer, but
there is a difference. If you needed attributes from the subtypes, you don't need any information about
the types of the subtypes to create a query to do this. It would simply use a LEFT OUTER JOIN for each
potential subtype like this.
SELECT INV.*, BI.BILLABLEENTITYDESC, CAR.*, CUST.*
FROM INVOICE2 INV