Query Complexity Factors

  • Article

A query issued through Entity Framework undergoes three main transformations:

1. Compilation

2. Query view expansion

3. Native SQL generation

1. Compilation

Compilation is the process of producing a Command Tree.

1.1. Entity SQL

1.1.1. Navigation Properties

Navigation is not natively supported by database servers. It is implemented using APPLY operators to bring in the related data.

1.1.2. Queries Nested in Projection

Entity SQL supports nesting full-blown queries in the projection clause. However, that is not commonly supported by data stores. Instead, it is implemented via a pattern of JOIN’ing the nested query with the main source.

1.2. ObjectQuery

1.2.1. Entity Span

Entity Span (Include) in ObjectQuery is the equivalent of navigation. Similarly ObjectQuery needs to compile one or more APPLY operators to bring in the spanned over entities.

1.2.2. Relationship Span

When one end of a relationship is referenced, the other end is implicitly brought in using a APPLY operator.

1.3. LINQ To Entities

LINQ has a variety of operators that don’t exist in Command Trees and that are compiled to patterns of Command Tree constructs:

· Aggregates and GroupBy

· Anonymous types

· Entity collections

2. Query View Expansion

The complexity of the expanded view depends solely on the subject model. The following mapping scenarios by definition produce complex queries:

2.1. Inheritance (Table-Per-Type)

In order to construct a whole entity, rows that represent the ancestor types must be JOIN’ed together.

2.2. Inheritance (Table-Per-Concrete Type)

In order to construct a whole entity UNION ALL operators are used to bring in the rows for derived types.

2.3. Entity Splitting

Similarly, the parts of split entities must be JOIN’ed together.

2.4. Many-To-Many Relationships

Navigating a many-to-many relationship requires at least 2 JOIN operators since it can only be modeled with a separate, “link”, table.

3. Native SQL Generation

3.1. Store Capabilities

Command Trees are designed with the capabilities of SQL Server 2005 in mind. And their capabilities go even further. Other stores, like SQL Server 2000, have to translate some Command Tree operations to complex patterns. Such operations may be:

· SKIP (paging)

· APPLY

· Query nesting

· Navigation

· Canonical functions

4. Other Aspects of Complexity

The data store may not be able to carry on with a relatively simple native SQL query if the query requires an intermediate temporary storage and that intermediate projection exceeds the store’s capabilities. That may happen as a result of too many JOIN operators as well as of few JOINs of large projections.

 

 

Zlatko Michailov

Program Manager, Data Programmability Runtime

Microsoft Corp.