Database Reference
In-Depth Information
If you pay attention to the Actual Number of Rows value, you see that this statement fetches only 25
records from the underlying table, instead of 50; this reduces disk reads.
Last but not least, if you were to look at the
SELECT
statement from a design standpoint, you could
ask whether the UserTypeKey value should be unique. The table schema doesn't carry a unique index on
that field, but should it? Can there be only one Manager user type? If the answer is yes, then you know
the TestUserType table will always return a single record for a Manager user type, in which case you may
be able to remove the
JOIN
entirely and apply the
WHERE
clause on the TestUsers table directly:
SELECT T.Name, T.UserType
FROM TestUsers T
WHERE T.AgeGroup > 0 AND T.UserType = 1
Not only is this statement much simpler, but the execution plan becomes trivial, meaning that SQL
Azure can serve this request without spending much time optimizing it. This change means you're
moving the filter from a table with few records (only 3 in TestUserType) to a table with many records
(100 in TestUsers). And whenever you have the option to make such a move, you should. SQL Azure
spends far fewer resources this way. Of course, such a move isn't always possible, but you need to know
the importance of having a proper database design before you begin tuning.
■
Note
Performance tuning can be fun. However, you may end up tuning forever if you don't set yourself
performance objectives.
Indexed Views
Indexed views are an excellent alternative when you absolutely need to
JOIN
data, and traditional
indexing doesn't yield the performance you're looking for. Indexed views behave like tables; the data
covered is materialized to disk so it can be retrieved quickly. Before jumping on indexed views,
understand that they have certain limitations and that due to their nature, you may incur a performance
hit through the usual
Insert
,
Delete
, and
Update
statements. Taking the previous statement as an
example, let's see how to create an indexed view to support the
JOIN
operation.
First, create a view that contains the statement you want to tune. Make sure you include all the
columns you need in the
SELECT
clause:
CREATE VIEW vTestUsersType WITH SCHEMABINDING AS
SELECT T.Name, T.UserType, T.AgeGroup, UT.UserTypeKey
FROM dbo.TestUsers T INNER JOIN dbo.TestUserType UT ON T.UserType = UT.UserType
Next, create a unique clustered index on the view:
CREATE UNIQUE CLUSTERED INDEX IDX_VIEW_TestUsers
ON vTestUsersType
(UserTypeKey, AgeGroup, Name, UserType)
Et voilà. When you run the statement again, you see a beautiful execution plan like the one in Figure
12-9. Because the view contains all the necessary columns, and the clustered index contains all the
columns of the view, you obtain the fastest possible data-retrieval technique, next to caching.
