monica73174
asked on
DATEDIFF in WHERE Clause Hurts Performance of Query
I have a sql query that pulls data based on how old you are. I have the birthday as a datetime type and I am using a datediff function to get the number of years a person is. When I take out the link with the DATEDIFF the query runs great. When I have the line added it slows down so much that it takes so much time that the query sometimes times out. Is there a way I can do this that will not impact the performance?
SELECT DISTINCT APPOINTMENT_ID,
APPMT_DATE_TIME,
P.PATIENT_ID,
JA.PATIENT_CODE_ID,
P.EMAIL_ADDRESS,
J.JOB_ID,
P.email_excluded,
P.FIRST_NAME,
P.LAST_NAME,
DATEDIFF(yyyy, P.BIRTH_DATE, GETDATE()) As Age,
P.HOME_PHONE,
P.WORK_PHONE_EXT,
P.M_INITIAL,
P.WORK_EXTENSION,
P.ADDRESS_LINE1,
P.ADDRESS_LINE2,
P.CITY,
P.STATE,
P.ZIP_CODE,
P.SUBSCRIPTION,
P.SEX,
SENT_EMAILS.PATIENT_CODE_ID AS EMAIL_PATIENT_CODE
FROM PATIENT P,
JOB J,
CLIENT C,
PATIENT_TEST_CONDITION_REL PTCR,
JOB_APPOINTMENT_TABLE JA LEFT OUTER JOIN
(
SELECT DISTINCT
ELD.PATIENT_CODE_ID
FROM
EMAIL_EVENT_LOG_DETAIL ELD,
EMAIL_EVENT_LOG EL
WHERE
ELD.EMAIL_EVENT_LOG_ID = EL.ID
AND EL.ID = 5
) AS SENT_EMAILS
ON JA.PATIENT_CODE_ID = SENT_EMAILS.PATIENT_CODE_ID
WHERE P.PATIENT_ID = JA.PATIENT_ID
AND P.Patient_ID = PTCR.PATIENT
AND JA.JOB_ID = J.JOB_ID
AND J.CLIENT_ID = C.CLIENT_ID
AND (JA.APPMT_STATUS = 'O')
AND (DATEDIFF(dd, JA.CREATED_TIMESTAMP, #currentDate#) <= 48)
AND (P.EMAIL_EXCLUDED IS NULL OR P.EMAIL_EXCLUDED = 'N')
AND (LEN(P.EMAIL_ADDRESS) > 0)
AND (C.EMAIL_OPT_OUT = 0)
AND (JA.DELETED IS NULL)
AND (JA.BILLING_EXCLUDE IS NULL)
AND (SENT_EMAILS.PATIENT_CODE_ID IS NULL)
AND ((DATEDIFF(yyyy, P.BIRTH_DATE, GETDATE()) BETWEEN 18 AND 39 AND TESTED = 'Y' AND PTCR.CONDITION IN (41,96,441,318,442)) OR (DATEDIFF(yyyy, P.BIRTH_DATE, GETDATE())) >= 40)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That seems to do the trick, the query runs awesome now.
What you can try is to isolate the WHERE clause with functions as the code inclosed.
Open in new window