CODEWITHSUNDEEP
sql-server
Michael C. Gates
Michael C. Gates

Reputation: 992

Index on Multiple Date Fields

I have a script that is heavily used. The primary part of the script, which if I remove, speeds up immensely, is:

WHERE
(
        DateTimeField1 IS NULL OR
        DateTimeField2 IS NULL OR
        DateTimeField3 IS NULL
) AND
DateTimeField4 IS NULL

If I exclude two of the three in the OR statement, it speeds up. Seems that since I am only checking if null, it should be really efficient.

What is the best way to either 1) index these fields or 2) speed up the where clause?

Upvotes: 0

Views: 111

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

I would suggest that you alter your table to have a computed column, something like:

alter table t add
ValidDates as ((case when DateTimeField1 is null then 'N' else 'Y' end) +
               (case when DateTimeField2 is null then 'N' else 'Y' end) +
               (case when DateTimeField3 is null then 'N' else 'Y' end) +
               (case when DateTimeField4 is null then 'N' else 'Y' end)
              )

Then create an index on the computed column:

create index t_validdates on t(validdates);

And then in your query, replace your where condition with:

where ValidDates in ('YYYY', 'YYNY', 'YNYY', 'YNNY', 'NYYY', 'NYNY', 'NNYY')

This is assuming that you will want to test various combinations of the NULLs. If it is always what you have in the query, then you can make that condition the computed column instead.

Upvotes: 2

Der U
Der U

Reputation: 3314

try an index with included columns:

CREATE INDEX idx_datetimes on YourTable (DateTimeField4)
INCLUDE (DateTimeField1 , DateTimeField2 , DateTimeField3 );

this shoul find rows where DateTimeField4 is null quite fast. Then for each such row the OR for the other three can be done quickly. It isn't even all 3 that need to be checked in every row as per logic of OR the first one with a NULL will make the whole WHERE-clause true

Upvotes: 0

Related Questions