Search code examples
sqlsql-servert-sqlsql-tuning

where isnull (DateX, DateY) >= getdate()

Could anyone let me know the ways to improve the performance of the SQL below? 

select A, B from T_XXX
where isnull(DateX, DateY) >= getdate()
  • No index on either DateX or DateY.
  • Currently No info about the proportion of NULL and non-NULL values in DateX.

(Added Comment) Thanks for the replies! If you consider "No Indexes" as prerequisite, is there any room for improvement of the SQL?

Solution

  • The simplest way to improve the performance is to create an index on ISNULL(DateX, DateY). Having individual indices on DateX and/or DateY probably won't help since the ISNULL function will likely negate the ability to use the index.

    If you did have an index on either column you could rewrite the clause as

    WHERE DateX >= GETDATE() OR (DateX IS NULL AND DateY >= GETDATE())

    which could take advantage of either index.

    SQL Server might internally convert ISNULL into an equivalent clause that would use the indices, but you'd have to try it both ways and see if there is a performance improvement to be certain,'

    If you consider "No Indexes" as prerequisite, is there any room for improvement of the SQL?

    Not directly. SQL is declarative, meaning you give it an idea of what you want and it comes up with what it thinks is the best plan to gather the data. There's nothing obvious that you can do to change performance just by changing the SQL in this case.