Professional SQL Server® 2005 Performance Tuning

9.5. 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.

9.5.1. 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.SalesOrderHeader 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'

SELECT CreditCardApprovalCode FROM Sales.SalesOrderHeader WHERE CreditCardApprovalCode = @CreditCardApprovalCode_UNICODE ...