Effect of where clause order on SQL query speed

Question

I have a problem where a complex query is running unacceptably slowly (roughly 10 seconds per transaction), on SQL-Server. I am not sure of the version, but at least 2005. The query matches some 40 fields (columns) against corresponding variables, and returns those rows where all fields either match, or both the field and the variable are null. At least the first few fields are indexed. As it stands, the first field in the query will match on almost every record. Would reordering the query, so that fields less likely to match are placed first, allow early termination of the query on a given row when a non-match is found? What other speed-ups could anyone suggest?

The query, with variables re-named, follows:

SELECT _pk FROM FinancialDetail 
WHERE   ( Field01 = @Field01 OR ( Field01 IS NULL AND @Field01 IS NULL ) )
    AND ( Field02 = @Field02 OR ( Field02 IS NULL AND @Field02 IS NULL ) )
    AND ( Field03 = @Field03 OR ( Field03 IS NULL AND @Field03 IS NULL ) )
    AND ( Field04 = @Field04 OR ( Field04 IS NULL AND @Field04 IS NULL ) )
    AND ( Field05 = @Field05 OR ( Field05 IS NULL AND @Field05 IS NULL ) )
    AND ( Field06 = @Field06 OR ( Field06 IS NULL AND @Field06 IS NULL ) )
    AND ( Field07 = @Field07 OR ( Field07 IS NULL AND @Field07 IS NULL ) )
    AND ( Field08 = @Field08 OR ( Field08 IS NULL AND @Field08 IS NULL ) )
    AND ( Field09 = @Field09 OR ( Field09 IS NULL AND @Field09 IS NULL ) )
    AND ( Field10 = @Field10 OR ( Field10 IS NULL AND @Field10 IS NULL ) )
    AND ( Field11 = @Field11 OR ( Field11 IS NULL AND @Field11 IS NULL ) )
    AND ( Field12 = @Field12 OR ( Field12 IS NULL AND @Field12 IS NULL ) )
    AND ( Field13 = @Field13 OR ( Field13 IS NULL AND @Field13 IS NULL ) )
    AND ( Field14 = @Field14 OR ( Field14 IS NULL AND @Field14 IS NULL ) )
    AND ( Field15 = @Field15 OR ( Field15 IS NULL AND @Field15 IS NULL ) )
    AND ( Field16 = @Field16 OR ( Field16 IS NULL AND @Field16 IS NULL ) )
    AND ( Field17 = @Field17 OR ( Field17 IS NULL AND @Field17 IS NULL ) )
    AND ( Field18 = @Field18 OR ( Field18 IS NULL AND @Field18 IS NULL ) )
    AND ( Field19 = @Field19 OR ( Field19 IS NULL AND @Field19 IS NULL ) )
    AND ( Field20 = @Field20 OR ( Field20 IS NULL AND @Field20 IS NULL ) )
    AND ( Field21 = @Field21 OR ( Field21 IS NULL AND @Field21 IS NULL ) )
    AND ( Field22 = @Field22 OR ( Field22 IS NULL AND @Field22 IS NULL ) )
    AND ( Field23 = @Field23 OR ( Field23 IS NULL AND @Field23 IS NULL ) )
    AND ( Field24 = @Field24 OR ( Field24 IS NULL AND @Field24 IS NULL ) )
    AND ( Field25 = @Field25 OR ( Field25 IS NULL AND @Field25 IS NULL ) )
    AND ( Field26 = @Field26 OR ( Field26 IS NULL AND @Field26 IS NULL ) )
    AND ( Field27 = @Field27 OR ( Field27 IS NULL AND @Field27 IS NULL ) )
    AND ( Field28 = @Field28 OR ( Field28 IS NULL AND @Field28 IS NULL ) )
    AND ( Field29 = @Field29 OR ( Field29 IS NULL AND @Field29 IS NULL ) )
    AND ( Field30 = @Field30 OR ( Field30 IS NULL AND @Field30 IS NULL ) )
    AND ( Field31 = @Field31 OR ( Field31 IS NULL AND @Field31 IS NULL ) )
    AND ( Field32 = @Field32 OR ( Field32 IS NULL AND @Field32 IS NULL ) )
    AND ( Field33 = @Field33 OR ( Field33 IS NULL AND @Field33 IS NULL ) )
    AND ( Field34 = @Field34 OR ( Field34 IS NULL AND @Field34 IS NULL ) )
    AND ( Field35 = @Field35 OR ( Field35 IS NULL AND @Field35 IS NULL ) )
    AND ( Field36 = @Field36 OR ( Field36 IS NULL AND @Field36 IS NULL ) )
    AND ( Field37 = @Field37 OR ( Field37 IS NULL AND @Field37 IS NULL ) )
    AND ( Field38 = @Field38 OR ( Field38 IS NULL AND @Field38 IS NULL ) )
    AND ( Field39 = @Field39 OR ( Field39 IS NULL AND @Field39 IS NULL ) )
    AND ( Field40 = @Field40 OR ( Field40 IS NULL AND @Field40 IS NULL ) )

Answer 1

This isn't dynamic search. All columns are being compared. It isn't possible to remove any without changing the semantics.

Field01 = @Field01 OR ( Field01 IS NULL AND @Field01 IS NULL ) 

Just means that both are equal where two Nulls compare as equal. This pattern doesn't normally cause SQL Server any problems. A cut down example is

CREATE TABLE #FinancialDetail
(
_pk INT PRIMARY KEY,
Field01 INT,
Field02 INT,
Field03 INT
)
CREATE INDEX IX ON #FinancialDetail(Field01, Field02, Field03)

declare @Field01 int, @Field02 int, @Field03 int;

SELECT _pk FROM #FinancialDetail 
WHERE   ( Field01 = @Field01 OR ( Field01 IS NULL AND @Field01 IS NULL ) )
    AND ( Field02 = @Field02 OR ( Field02 IS NULL AND @Field02 IS NULL ) )
    AND ( Field03 = @Field03 OR ( Field03 IS NULL AND @Field03 IS NULL ) )

Which shows an index seek on all three columns

The plan for this is in fact indistinguishable from the one for

SELECT _pk FROM #FinancialDetail 
WHERE   ( Field01 = @Field01 )
    AND ( Field02 = @Field02 )
    AND ( Field03 = @Field03 )

The fact that the equality operator in the index seek is using different semantics for NULL is not exposed to us as discussed in the comments here.

You need a composite index on some fairly selective combination of columns (SQL Server allows up to 16 key columns) and should be able to get a seek on that.

If you need to work with existing single column indexes and the optimum one might vary you could add OPTION (RECOMPILE).

You could also rewrite as below which is shorter and the same semantics but I doubt it will make any difference to the plan.

SELECT _pk FROM #FinancialDetail 
WHERE EXISTS (SELECT @Field01, @Field02, @Field03 /*.... , @Field40*/
              INTERSECT
              SELECT Field01, Field02, Field03 /*.... , Field40*/ )

Answer 2

This is a bit long for a comment. I am guessing that the OR conditions are confusing the optimizer. You have a complex (albeit repetitive) WHERE condition.

One big problem is that SQL Server caches execution plans, so the first time the query is run, the plan is set. If later values might make better use of indexes they won't necessarily get used.

I would recommend dynamic SQL, to eliminate the OR conditions. The resulting conditions would depend on the values of the variables. If @Field02 and @Field03 were NULL, then the conditions would look like:

WHERE ( Field01 = @Field01 ) AND
      ( Field02 IS NULL ) AND
      ( Field03 IS NULL )  AND
      ( Field04 = @Field04 ) AND
      . . .

These are strict AND conditions with equality, which should be much easier for the optimizer to handle.