How to make faster this statement : "paramDate Between startDate and NULL" ?

Question

This query is taking long time when endDate is null (i think that its about case statement, before case statement it was fast)

SELECT * 
FROM HastaKurumlari
WHERE CONVERT(SMALLDATETIME,'21-05-2009',103) 
BETWEEN startDate 
    AND (CASE WHEN endDate IS NULL THEN GETDATE() ELSE endDate END)

What should i use, when endDate is null to make it faster ?

Answer 1

You could try the coalesce function:

select * 
from HastaKurumlari
where convert(smalldatetime, '21-05-2009', 103) 
    between startDate and coalesce(endDate, getdate());

The only way to be certain is to try any alternatives and view the execution plan generated for each query.

Answer 2

If it is performance critical, then perhaps just don't use null for the open end-date - use the maximum supported datetime instead (probably lots of 9s).

I'd also do the conversion separately:

DECLARE @when datetime
SET @when = CONVERT(SMALLDATETIME,'21-05-2009',103) 

SELECT * 
  FROM HastaKurumlari
WHERE @when
BETWEEN startDate AND endDate

There is still something a bit different in the above and your original; if you can explain the intent of the GETDATE() check I might be able to tidy (read:fix) it a bit.

Answer 3

Here's the query without CONVERT or CASE:

SELECT * 
FROM HastaKurumlari
WHERE '21-05-2009' between startDate and IsNull(endDate,getdate())

To make sure Sql Server doens't evaluate getdate() for every row, you could cache it, although I'm pretty sure Sql Server is smart enough by default:

declare @now datetime
set @now = getdate()

SELECT * 
FROM HastaKurumlari
WHERE '21-05-2009' between startDate and IsNull(endDate,@now)

Posting the query plan could help explain why the query is slow:

SET SHOWPLAN_TEXT ON
go
SELECT * 
FROM HastaKurumlari
WHERE CONVERT(SMALLDATETIME,'21-05-2009',103) 
BETWEEN startDate 
    AND (CASE WHEN endDate IS NULL THEN GETDATE() ELSE endDate END)

Answer 4

As a starting point, factor out GETDATE() so that its called just once, and you should see an improvement in speed.

The way you've written it you are asking for GETDATE() to be evaluated every time enddate is null.

Since GETDATE() is a non-deterministic function the query cannot be optimised and will tend to under perform.