[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1116
  • Last Modified:

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)

Open in new window

0
monica73174
Asked:
monica73174
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what if you do like this:
 AND       ( P.BIRTH_DATE  >= DATEADD(year, -39, getdate()) and P.BIRTH_DATE <= DATEADD(year, -18, GETDATE()) )

Open in new window

0
 
CarlosFaturetoCommented:
Using functions in the WHERE clause hurts performance because the SQL Server must try all rows to find the right records.
What you can try is to isolate the WHERE clause with functions as the code inclosed.
SELECT   APPOINTMENT_ID, 
         APPMT_DATE_TIME,
         DT.PATIENT_ID, 
         DT.PATIENT_CODE_ID,
         DT.EMAIL_ADDRESS, 
         DT.JOB_ID,        
         DT.email_excluded,
         DT.FIRST_NAME, 
         DT.LAST_NAME, 
         DATEDIFF(yyyy, DT.BIRTH_DATE, GETDATE()) As Age,
         DT.HOME_PHONE, 
         DT.WORK_PHONE_EXT, 
         DT.M_INITIAL, 
         DT.WORK_EXTENSION, 
         DT.ADDRESS_LINE1, 
         DT.ADDRESS_LINE2, 
         DT.CITY, 
         DT.STATE, 
         DT.ZIP_CODE, 
         DT.SUBSCRIPTION,
         DT.SEX,
         EMAIL_PATIENT_CODE
FROM     (
         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, 
                  P.BIRTH_DATE 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,
                  TESTED,  
                  PTCR.CONDITION
         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)
         ) DT
WHERE    ((DATEDIFF(yyyy, DT.BIRTH_DATE, GETDATE()) BETWEEN 18 AND 39 AND TESTED = 'Y' AND DT.CONDITION IN (41,96,441,318,442)) OR (DATEDIFF(yyyy, DT.BIRTH_DATE, GETDATE())) >= 40)

Open in new window

0
 
monica73174Author Commented:
That seems to do the trick, the query runs awesome now.  
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now