Tuning T-SQL - Professional SQL Server 2005 Performance Tuning - page 280

the select against the view is performing an aggregation after scanning the AccountNumber index and
then filtering out the results later in the plan.

|--Filter(WHERE:([AccountNumber] like N'10-4020-000210'))
|--Compute Scalar(DEFINE:([Expr1003]=CONVERT_IMPLICIT(int,[Expr1006],0)))
|--Stream Aggregate(GROUP BY:([AccountNumber]) DEFINE:([Expr1006]=Count(*)))
|--Index Scan(OBJECT:([IX_SalesOrderHeader_AccountNumber]), ORDERED FORWARD)

The straight SELECT is able to build a plan to seek the AccountNumber index on the predicate and then
aggregate the results. If you look at the results in SHOWPLAN_ALL, you'll see that the row count for the
straight SELECT is down to 20 estimated rows after the SEEK operation while the View SELECT is still
working with 32,465 estimated rows.

|--Compute Scalar(DEFINE:([Expr1003]=CONVERT_IMPLICIT(int,[Expr1006],0)))
|--Stream Aggregate(DEFINE:([Expr1006]=Count(*),
[AccountNumber]=ANY([AccountNumber])))
|--Index Seek(OBJECT:([IX_SalesOrderHeader_AccountNumber]),
SEEK:([AccountNumber] > = N'10-4020-000210'
AND [AccountNumber] < = N'10-4020-000210'),
WHERE:([AccountNumber] like N'10-4020-000210') ORDERED FORWARD)

The issue here is not with complex views, but with the difference between the way the LIKE operator
(and others) match the arguments and how GROUP BY statements summarize groups. If you change the
LIKE operator to an equal (

) operator, you'll notice that the optimizer recommends the same plan for
both. This is because the predicate can be pushed deeper into the plan because equivalency operators are
the same for the predicate matching and the grouping operations.

=

Tuning the T-SQL Predicate
Tuning the T-SQL predicate is usually the heart of the performance problem, if there is such a thing as a
quick fix. The next few sections will go over some common performance problems and provide details
on how to troubleshoot and remove them.

Removing Certain Implicit Conversions
If your databases aren't internationalized then you may not be aware of the implicit conversion issue
that degrades performance when non-Unicode and Unicode indexes and filters are mixed in predicates.
Implicit conversions aren't limited to just Unicode fields, and could occur with any compatible data
type. (Non-compatible data type conversions throw compiler errors). Implicit conversions occur when
parameters are provided to searchable arguments against columns, which have compatible, but not exact
data types. It can happen by accident if coders are used to using the Unicode data types and a database
schema has an occasional VARCHAR data type. The result is an extra operation to convert the values
for comparison.

The CreditCardApprovalCode column is a regular VARCHAR data type column in the Sales.SalesOrder-
Header table. Run the following query comparison, which uses NVARCHAR (Unicode) and VARCHAR data
type parameters to compare the effects of the parameter types:

DECLARE @CreditCardApprovalCode_UNICODE NVARCHAR(30)
SET @CreditCardApprovalCode_UNICODE = N'539435Vi62867'