Jaa

Limit Non-order Preserving Expressions

  • Artikkeli

Expressions with column transformations that do not retain the original column order do not benefit from column statistics. This can lead to poor plans. Assume for purposes of illustration that we've added a column to Sales.SalesOrderHeader in AdventureWorks as follows:

ALTER TABLE Sales.SalesOrderHeader ADD SalesOrderFlag tinyint

For example, consider the following:

      SELECT * FROM Sales.SalesOrderHeader h

      WHERE h.SalesOrderFlag & 0x0001 = 1

If a predicate on a column performs a bit-wise AND on the column, the selectivity of the predicate cannot in general be estimated using the column histogram. Here, it would be better to separate out the bit flag as its own column on which statistics could be built. For example:

ALTER TABLE Sales.SalesOrderHeader ADD SalesOrderValidFlag tinyint

In the latter case the selectivity of a predicate on the flag which is no longer bit-wise ANDed could be estimated, as shown below.

SELECT * FROM Sales.SalesOrderHeader h

      WHERE h.SalesOrderValidFlag = 1

Alternatively, a computed column could be defined on the bit-wise ANDed value, and a statistic could be built on the computed column. The computed column would then be referenced in the query.

There are many cases of non-order preserving expressions. Consider the query shown below.

SELECT * FROM Sales

      WHERE Price * ( 1 + Tax ) > 100

If you see slower-than-desired query performance in such a case, consider creating a computed column with the equivalent expression, and creating statistics or an index on the computed column. Auto create statistics will also create statistics for the computed column if it exists, so you need not create the computed column statistics manually if auto create statistics is enabled. You do not have to modify the query to reference the computed column explicitly because query expressions are matched to identical computed column expressions automatically.

Comments