Databases Reference
In-Depth Information
• Contradictions are detected and removed. Since these parts of the query are not
executed at all, SQL Server saves resources like I/O, locks, memory and CPU, making
the query to be executed faster. For example, the Query Optimizer may know that no
records can satisfy a predicate even before touching any page of data. A contradiction
may be related to a check constraint, or may be related to the way the query is written.
Both scenarios will be shown in an example later in this section.
The output of the simplification process is a simplified logical operator tree.
Let's see a couple of examples of the simplification process, starting with the Foreign Key
Join elimination. The query we used on Listing 5-19 joins three tables and shows
the execution plan seen in Figure 5-5. Let's see what happens if we comment out the
CustomerType
column, as shown in Listing 5-23.
SELECT
FirstName
,
LastName
- -, CustomerType
FROM
Person
.
Contact
AS
C
JOIN
Sales
.
Individual
AS
I
ON
C
.
ContactID
=
I
.
ContactID
JOIN
Sales
.
Customer
AS
Cu
ON
I
.
CustomerID
=
Cu
.
CustomerID
Listing 5-23.
If you run the query again, this time only two tables are joined, and the
Customer
table
has been removed, as can be seen in the execution plan in Figure 5-6.
Figure 5-6:
Foreign Key Join elimination example.
