Databases Reference
In-Depth Information
Figure 11-13.
Well-balanced execution plan
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 over time.
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 Database 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 Database 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.
■
performance tuning can be fun. however, you may end up tuning forever if you don't set yourself
performance objectives.
Note
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
