Database Reference
In-Depth Information
Figure 18-19.
Execution plans showing the benefit of defining DRI between the two tables
As you can see, the execution plan of the second
SELECT
statement is highly optimized: The
Person.StateProvince
table is not accessed. With the declarative referential integrity in place (and
Address.StateProvince
set to
NOT NULL
),
the optimizer is assured that for every record in the child table, the parent table contains a corresponding record.
Therefore, the
JOIN
clause between the parent and child tables is redundant in the second
SELECT
statement, with no
other data requested from the parent table.
You probably already knew that domain and referential integrity are Good Things, but you can see that they not
only ensure data integrity but also improve performance. As just illustrated, domain and referential integrity provide
more choices to the optimizer to generate cost-effective execution plans and improve performance.
To achieve the performance benefit of DRI, as mentioned previously, the foreign key columns in the child table
should be
NOT NULL
. Otherwise, there can be rows (with foreign key column values as
NULL
) in the child table with no
representation in the parent table. That won't prevent the optimizer from accessing the primary table (
Prod
) in the
previous query. By default—that is, if the
NOT NULL
attribute isn't mentioned for a column—the column can have
NULL
values. Considering the benefit of the
NOT NULL
attribute and the other benefits explained in this section, always mark
the attribute of a column as
NOT NULL
if
NULL
isn't a valid value for that column.
You also must make sure you are using the
WITH CHECK
option when building your foreign key constraints. If the
NOCHECK
option is used, these are considered to be untrustworthy constraints by the optimizer and you won't realize
the performance benefits that they can offer.
Summary
As discussed in this chapter, to improve the performance of a database application, it is important to ensure that
SQL queries are designed properly to benefit from performance-enhancement techniques such as indexes, stored
procedures, database constraints, and so on. Ensure that queries don't prevent the use of indexes. In many cases,
the optimizer has the ability to generate cost-effective execution plans irrespective of query structure, but it is still
a good practice to design the queries properly in the first place. Even after you design individual queries for great
performance, the overall performance of a database application may not be satisfactory. It is important not only to
improve the performance of individual queries but also to ensure that they don't use up the available resources on the
system. The next chapter will cover how to reduce resource usage within your queries.
