SQL Server Optimization – Does WHERE Query Check Simpler Comparisons First?

optimizationsql server

If I write a query that includes a compound WHERE clause, e.g.:

SELECT *
FROM MyTable
WHERE BitField = 1
    AND VarcharField = 'asdf'

and the inclusion of that bit comparison simply excludes the same fields that the varchar comparison will exclude, will the presence of that bit field comparison render me a performance improvement?

Best Answer

It is important to recognize that SQL is a declarative language. The SELECT query you wrote specifies the logical results that should be returned. It is up to the database engine, specifically the query optimizer, to determine an efficient physical strategy to return those results.

The final physical execution plan will depend on the optimizer's reasoning abilities, the amount of time it is prepared to spend on the problem, the availability of suitable access methods (primarily indexes and materialized views), representative statistics information, and the particular code path your query specification takes through the optimization code.

In general, if your database design is relational, you provide good access methods & accurate statistical information, and the query is well-written, the optimizer will normally find a reasonable physical execution strategy without you needing to worry too much about the written form of the query specification too much.

There will always be cases where expressing the same logic requirement using different (but semantically identical) syntax will affect the physical execution plan , but that should be a secondary concern. Again, generally, only consider expressing the query differently if runtime characteristics are unacceptable, once all the basics mentioned above have been covered.

It would be rare in the extreme for the written order of simple conjunctive WHERE clause predicates (as in the question) to affect the physical execution plan in any measurable way. In short, this is not something you should spend time worrying about. Get the database design, indexes, and statistical information right first.

To answer the question directly (eventually!), adding the extra redundant condition might improve performance, but only if it enables use of a more efficient access method - for example if there is only an index on (BitField, VarcharField). If there were already an index on (VarcharField), it would add only overhead.

As an implementation detail, no, SQL Server does not consider the cost of comparisons depending on data type or apparent computational complexity. In fact, there is precious little costing of scalar operations at all, but that leads on to a whole separate topic.

Related questions:

Logical operators OR AND in condition and order of conditions in WHERE
SQL Server 2008 and constant expressions
Bitwise operators affecting performance
Strange SQL Statement Behavior

Related Question