Database Reference
In-Depth Information
Whereas logical operators are used in a SQL statement, such as
LEFT JOIN
,
physical operators
tell you
which technique SQL Azure is using to solve a given logical operation or to fetch additional data. The
most common physical operators SQL Azure uses to represent
JOIN
operations are listed in Table 12-4.
Table 12-4.
Physical
JOIN
operators
Operator
Symbol
Comment
Nested loop
A loop is performed in SQL Azure to retrieve data. For each
record in Table 1 matching the
WHERE
clause, find the
matching records in Table 2. On large recordsets, loops can be
costly.
Hash match
A hash is calculated for each record in each table participating
in a
JOIN
, and the hashes are compared for equality.
Merge
Merge operators are usually the fastest operators because they
perform a single pass of the tables involved by taking
advantage of the order in which the data is stored or retrieved.
You can give SQL Azure certain hints to use a specific physical operator, but using them isn't
generally recommended. You have three proper ways to influence SQL Azure to select an effective
physical operator:
Review your
WHERE
clause
. This is perhaps the most overlooked aspect of
performance tuning. When you have the choice, applying the
WHERE
clause on the
tables that have the most rows gives you new opportunities for indexing.
•
•
Optimize your database design
. Highly normalized databases force you to create
more
JOIN
statements. And of course, the more
JOIN
statements, the more tuning
you need to do. You shouldn't plan to have a database design at first normal form;
however, in certain cases, denormalizing has its benefits.
•
Create better indexes
. Having a good indexing strategy is important. The order of
your columns and the number of columns can make a world of difference for SQL
Azure. This chapter reviews indexing shortly.
■
Note
To run the following examples, you need to execute the
Tuning.sql
script. It creates a few sample tables
with test data. Make sure to select a user database when running this script.
To show which physical
JOIN
operators have been selected, execution plans provide insights into the
volume of data being worked on and the relative cost of certain operations. For example, execute the
following SQL statement (after running the
tuning.sql
script):
SELECT T.Name, T.UserType
FROM TestUsers T INNER JOIN TestUserType UT
ON T.UserType = UT.UserType
WHERE T.AgeGroup > 0 AND UT.UserTypeKey = 'Manager'
