Remove ISNULL FROM left join to make SARGAble

Question

Post reply

Remove ISNULL FROM left join to make SARGAble

juniorDBA13

Ten Centuries

Points: 1108
More actions
May 20, 2025 at 5:04 pm

#4594151

Hi,
I'm trying to make the below query SARGable by removing the ISNULL function part of the join. (Part of join in italic)
Any suggestions?
SELECT .....
FROM dbo.historicsummary hs
LEFT JOIN dbo.Status fs ON hs.ID = fs.ID
AND hs.TimeStamp >= fs.ValidFrom
AND hs.TimeStamp < ISNULL(fs.ValidTo, DATEADD(d, 1, SYSDATETIME()))

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

uratol SSC Veteran Points: 270 More actions · Answer 1

Your LEFT JOIN will be interpreted as INNER due to right table condition present in WHERE (fs.ValidFrom).
I don't think the index would be used at all under that condition
```
WHERE hs.TimeStamp >= fs.ValidFrom
```
So, there no difference between hs.TimeStamp < ISNULL(fs.ValidTo, DATEADD(d, 1, SYSDATETIME()))
and hs.TimeStamp < fs.ValidTo or (fs.ValidTo is null AND hs.TimeStamp < DATEADD(d, 1, SYSDATETIME()))
If I wrong, you can try:
```
SELECT ..
WHERE ... AND hs.TimeStamp < fs.ValidTo
UNION ALL
SELECT ..
WHERE ... fs.ValidTo is null AND hs.TimeStamp < < DATEADD(d, 1, SYSDATETIME())
```

frederico_fonseca SSCoach Points: 16225 More actions · Answer 2

uratol wrote:

Your LEFT JOIN will be interpreted as INNER due to right table condition present in WHERE (fs.ValidFrom

the OP sql does NOT have a where clause - its only 3 join conditions in order to satisfy the join to dbo.Status.

it will NOT be interpreted as a inner join.

uratol SSC Veteran Points: 270 More actions · Answer 3

Yep, my bad. Cognitive distortion - AND read as WHERE

There is one more option:

SELECT hs.*
    , isnull(fs1.ValidFrom, fs2.ValidFrom) as ValidFrom
FROM dbo.historicsummary hs
    LEFT join dbo.Status fs1 ON hs.ID = fs1.ID
        and hs.TimeStamp >= fs1.ValidFrom
        and hs.TimeStamp < fs1.ValidTo
    LEFT join dbo.Status fs2 ON hs.ID = fs2.ID
        and hs.TimeStamp >= fs2.ValidFrom
        and fs2.ValidTo IS NULL
        and hs.TimeStamp < DATEADD(d, 1, SYSDATETIME())

It will be SARGAble, but again, I highly doubt that indexes will be involved and it will be faster than the original query

drew.allen SSC Guru Points: 77013 More actions · Answer 4

It looks like you are using a NULL value to represent an unknown/unspecified date. While you may not be able to change this, it's better to use a specific unlikely date as an unknown/unspecified date. For start dates, 1900-01-01 is very typical, for end dates 9999-12-31, 9999-12-30, and 9999-01-01 have all been suggested, but I don't think there is a set standard. This prevents the exact issue that you are seeing with SARGability.

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Jeffrey Williams SSC Guru Points: 90348 More actions · Answer 5

I don't think making this SARGable is going to help performance - but you also have another issue. Using DATEADD(DAY, 1, SYSDATETIME()) returns tomorrow's date with the time set to the execution time of the query. Running it at 1pm could result in different results than running it at 8am.

I think using an OUTER APPLY may be the better option here - assuming you don't have overlapping validfrom/validto dates.

If you are looking for the Status that was active at the time - it might be a better option to use an OUTER APPLY instead:

SELECT .....
  FROM dbo.historicsummary hs
 OUTER APPLY (
    SELECT TOP (1)
           *
      FROM dbo.Status fs
     WHERE fs.ID = hs.ID
       AND fs.ValidFrom <= hs.TimeStamp
     ORDER BY
           fs.ValidFrom DESC
              ) f

This should get you the latest status for each historic summary ID.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

juniorDBA13 Ten Centuries Points: 1108 More actions · Answer 6

This is the current query plan before changes

I have tried changing to below doesn't seem to have improved things

SELECT ..

WHERE ... AND hs.TimeStamp < fs.ValidTo

UNION ALL

SELECT ..

WHERE ... fs.ValidTo is null AND hs.TimeStamp < < DATEADD(d, 1, SYSDATETIME())

Attachments:

You must be logged in to view attached files.

Jo Pattyn SSC-Dedicated Points: 32430 More actions · Answer 7

The upper part is probably status (84)

and the lower part your history

SQL Server has to match history with status before it can filter out on statusValidFrom

It decided it would need all the rows to do that (either filter on statusValidFrom after matching id's or history.TimeStamp (all before tomorrow)

The quickest way is to scan all your history once (scan history) and try to match it up with the statusValidFrom (scan status).

It can't predict how many status validFrom/validTo will match to a history id

Remove ISNULL FROM left join to make SARGAble

Attachments:

Cookies on SQLServerCentral