0

Is there a way, to optimize this query, where several IsNull in relation with dates are used? Unfortunately the query is a way slow and actually I have no glue, how to make it way faster.

case
when IsNull(A.TD, '1900-01-01') > IsNull(B.TD, '1900-01-01') then IsNull(A.TD, '1900-01-01')
when IsNull(A.TD, '1900-01-01') < IsNull(B.TD, '1900-01-01') then IsNull(B.TD, '1900-01-01')
when IsNull(A.TD, '1900-01-01') = IsNull(B.TD, '1900-01-01') then S.TE end as DZ

Thank you.

  • 2
    You can start here. – Erik Darling Jan 5 '18 at 21:12
  • 1
    It can help to specify your DBMS. If you think you did - the sql tag indicates a question about the SQL language. If you meant MS SQL Server, then use the sql-server tag. – RDFozz Jan 5 '18 at 23:22
  • Thank you Erik. Very informative to me and brought me an improvement of performance by 35%. Kind regards. Tobias – Tobias A. Jan 8 '18 at 5:49
0

As sp_BlitzErik noted in his comment, he has an excellent post on this topic.

However, his query was something of a special case, and some of the things he did only worked because he wanted a COUNT. As you only present an expression from your WHERE clause, we can't make any particular assumptions about the rest of your query. I do recommend looking Erik's post over, in any case. It's very good.

Given what you've presented, I come up with the following:

case
when A.TD IS NULL and B.TD IS NULL THEN S.TE
when A.TD IS NULL and '1900-01-01' > B.TD THEN '1900-01-01'
when A.TD IS NULL and '1900-01-01' < B.TD THEN B.TD
when A.TD IS NULL and '1900-01-01' = B.TD THEN S.TE
when A.TD > '1900-01-01' and B.TD IS NULL THEN A.TD
when A.TD < '1900-01-01' and B.TD IS NULL THEN '1900-01-01'
when A.TD = '1900-01-01' and B.TD IS NULL THEN S.TE
when A.TD > B.TD then A.TD
when A.TD < B.TD then B.TD
when A.TD = B.TD then S.TE end as DZ

Basically, ISNULL(A, n) = m can be broken down into two parts:

(   (A IS NULL and n = m)
 OR A = m
)

As you know, SQL technically uses trinary logic - an expression can be TRUE, FALSE, or NULL. For two expressions joined by OR to evaluate to TRUE, one of them must be true - the other can be either FALSE or NULL, it doesn't matter.

So, if A is not NULL, the first expression will always be FALSE, and the result of the second expression (which will not be NULL as long as m is not NULL) will determine the value of the pair.

And, if A is NULL, the second expression will be NULL, and the first expression (which will not be NULL as long as neither n nor M is NULL) determines the value of the pair.

Since we're looking at multiple comparisons on two expressions, things get a bit more complicated, but not necessarily unmanageably so.

Note that you may be able to simplify the expression. If your dates can never be lower than the hardcoded date, then you don't need to see if A.TD or B.TD is less than that date.

You may also want to rearrange the various WHEN clauses. When it doesn't hurt maintainability, I'm inclined to put the clause that represents the most likely occurrence first; in your cases, if NULLs are rare, that may be the last three checks. I'm not sure if query optimizer perform the checks in the order they're presented, but (if they do) it's a good idea to minimize the number of checks that have to be done before the right one is hit.

A final note: you didn't specify what DBMS you're using, and it's possible that something I've presented wouldn't work globally. I mostly use MS SQL Server, and this would work there.

  • Hello RDFozz. Indeed I am using MS SQL Server. Unfortunately I missed the "sql-server" Tag right here. Your example above is very good. Thank you. Combined with Erik's informations provided, I have learned a lot. Thank you for that. Kind regards. Tobias – Tobias A. Jan 8 '18 at 5:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.