Query taking long time to run with dynamic dates

Transact-SQL
1

Hi,

I have one query which is running perfectly with no issues, with below condition

Where (isnull(a.DischargeDate,a.ServiceDate) > '2015-09-09' and isnull(a.DischargeDate,a.ServiceDate) < '2015-09-15')

But when I change the condition by giving expression for dates instead of directly giving date , query taking too much time to run, below is the condition

Where (isnull(a.DischargeDate,a.ServiceDate) > DATEADD(dd,-7,CAST(GETDATE() AS DATE)) and isnull(a.DischargeDate,a.ServiceDate) < CAST(GETDATE() AS DATE))

When I use first condition result will be display in 2 sec.. but when I am using 2nd condition results taking lke 20 mins :frowning:

I need to check condition like > TodayDate-7 and < TodayDate

Plese halep me on this.

Thank you.

2

What about creating two variables for the dates before the query?

Because of the ISNULL I do not think the index will be used

3

can you suggest me please how I need to create variables for the dates..

Thank you..

4

What is the data type of "DischargeDate"? Is it varchar or is it datetime?

5

Hi Scott,

Datatype is datetime...

6

Add this to the end of the query:
...
Where (isnull(a.DischargeDate,a.ServiceDate) > DATEADD(dd,-7,CAST(GETDATE() AS DATE)) and isnull(a.DischargeDate,a.ServiceDate) < CAST(GETDATE() AS DATE))
...
OPTION (RECOMPILE)

7

I'm surprised that performs well (i.e. is SARGable), my gut feeling would have been to do

Where  a.DischargeDate > '2015-09-09' and  a.DischargeDate < '2015-09-15'
...
UNION ALL
...
Where     a.DischargeDate IS NULL
      AND a.ServiceDate > '2015-09-09' and  a.ServiceDate < '2015-09-15'

assuming appropriate indexes on DischargeDate and ServiceDate

Perhaps an index on DischargeDate, ServiceDate covers the original query and makes it SARGable?

Is that more expensive, at run time, than putting the start/end dates in @variables?

I suppose I am making the grand assumption that a week's data is going to be fine with a cached query plan, whatever the week's start point is ... dunno how well that assumption would hold up in practice! but the cost of a RECOMPILE always worries me - maybe it is trivial?

8

Having same fieldtype on both sides of operator might just do the trick.
Try this:

where isnull(a.dischargedate,a.servicedate)>=dateadd(dd,datediff(dd,0,getdate()),-6)
  and isnull(a.dischargedate,a.servicedate)<dateadd(dd,datediff(dd,0,getdate()),0)
9

Added OPTION (RECOMPILE) at the end of query but no use.. Query still very slow..

Please help me.

10

You almost certainly have some type of wait going on before SQL can read the data. But without actual query plans, there's nothing more I can tell you.

11

I thought it is because of ISNULL so for just testing I just added condition for only Dischargedate like shown below but still query taking long time as previous.

where (a.DischargeDate > dateadd(dd,datediff(dd,0,getdate()),-7) and a.Discharge < dateadd(dd,datediff(dd,0,getdate()),0))

if I added like below within 2 sec I am getting results..

where (a.DischargeDate >'09/15/2015' and a.DischargeDateTime < '09/22/2015')

Please suggest me what is wrong with this..

12

Ok Creating variables on top and using those variables in condition works for me ..

Thank you..