locked
Query RRS feed

  • Question

  • I am running the following query and it has been running for over 2 hours, can anyone please help me write it in a better/faster way? Thanks in advance

    SELECT *
    FROM Transactions
    WHERE YEAR(BatchDateTime) >= 2009 OR 
    VisitID IN 
    (SELECT  VisitID
    FROM Visits
    WHERE (InpatientOrOutpatient = 'I' AND VisitID IS NOT NULL AND (AdmitDateTime >= '1/1/2009' OR (AdmitDateTime < '1/1/2009' AND DischargeDateTime >= '1/1/2009')))
    OR (InpatientOrOutpatient = 'O' AND VisitID IS NOT NULL AND (ServiceDateTime >= '1/1/2009' OR (ServiceDateTime IS NULL AND (AdmitDateTime >= '1/1/2009' OR DischargeDateTime >= '1/1/2009'))))
    UNION ALL
    SELECT b.VisitID
    FROM FinancialData a
    INNER JOIN BarVisits b ON a.BillingID = b.BillingID
    WHERE a.PatientBalance <> 0 AND (
    (InpatientOrOutpatient = 'I' AND b.VisitID IS NOT NULL AND AdmitDateTime < '1/1/2009' AND DischargeDateTime < '1/1/2009')
    OR (InpatientOrOutpatient = 'O'  AND b.VisitID IS NOT NULL  AND (ServiceDateTime < '1/1/2009' OR (ServiceDateTime IS NULL AND (AdmitDateTime < '1/1/2009' OR DischargeDateTime < '1/1/2009'))))
    ))

    Tuesday, April 24, 2012 3:21 PM

Answers

  • 1. Do you need all fields from Transactions table?

    2. I suggest this re-write:

    select T.Col1, T.Col2, etc. from dbo.Transactions T where T.BatchDateTime >='20090101' -- to make the query sargable OR EXISTS (select 1 from Visits V where V.VisitID = T.VisitID and InpatientOrOutpatient = 'I' and (V.AdmitDateTime >='20090101' OR V.AdmitDateTime <='20090101' and V.DischargeDateTime >='20090101')) OR EXISTS (select 1 from Visits V where V.VisitID = T.VisitID and InpatientOrOutpatient = 'O' and (V.ServiceDateTime >='20090101' OR V.ServiceDateTime IS NULL and (V.DischargeDateTime >='20090101' OR V.AdmitDateTime >='20090101'))) OR EXISTS (select 1 FROM FinancialData a INNER JOIN BarVisits b ON a.BillingID = b.BillingID WHERE b.VisitID = T.VisitID and a.PatientBalance <> 0 AND ( (InpatientOrOutpatient = 'I' AND b.VisitID IS NOT NULL AND AdmitDateTime < '20090101' AND DischargeDateTime < '20090101') OR (InpatientOrOutpatient = 'O' AND b.VisitID IS NOT NULL AND (ServiceDateTime < '20090101'

    OR (ServiceDateTime IS NULL AND (AdmitDateTime < '20090101' OR DischargeDateTime < '20090101')))) ))

    That's the idea - you may need to correct some typos. 



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    • Edited by Naomi N Tuesday, April 24, 2012 3:43 PM
    • Proposed as answer by amber zhang Wednesday, April 25, 2012 1:27 AM
    • Marked as answer by Iric Wen Thursday, May 3, 2012 2:51 AM
    Tuesday, April 24, 2012 3:31 PM
  • Good way to start optimization: beautify the code. Page: http://www.sqlusa.com/sqlformat/

    Here is the code:

    SELECT * 
    FROM   Transactions 
    WHERE  YEAR(BatchDateTime) >= 2009 
            OR VisitID IN (SELECT VisitID 
                           FROM   Visits 
                           WHERE  ( InpatientOrOutpatient = 'I' 
                                    AND VisitID IS NOT NULL 
                                    AND ( AdmitDateTime >= '1/1/2009' 
                                           OR ( AdmitDateTime < '1/1/2009' 
                                                AND DischargeDateTime >= '1/1/2009' 
                                              ) ) ) 
                                   OR ( InpatientOrOutpatient = 'O' 
                                        AND VisitID IS NOT NULL 
                                        AND ( ServiceDateTime >= '1/1/2009' 
                                               OR ( ServiceDateTime IS NULL 
                                                    AND ( AdmitDateTime >= 
                                                          '1/1/2009' 
                                                           OR DischargeDateTime >= 
                                                              '1/1/2009' ) 
                                                  ) ) ) 
                           UNION ALL 
                           SELECT b.VisitID 
                           FROM   FinancialData a 
                                  INNER JOIN BarVisits b 
                                    ON a.BillingID = b.BillingID 
                           WHERE  a.PatientBalance <> 0 
                                  AND ( ( InpatientOrOutpatient = 'I' 
                                          AND b.VisitID IS NOT NULL 
                                          AND AdmitDateTime < '1/1/2009' 
                                          AND DischargeDateTime < '1/1/2009' ) 
                                         OR ( InpatientOrOutpatient = 'O' 
                                              AND b.VisitID IS NOT NULL 
                                              AND ( ServiceDateTime < '1/1/2009' 
                                                     OR ( 
                                              ServiceDateTime IS NULL 
                                              AND ( AdmitDateTime < 
                                                    '1/1/2009' 
                                                     OR DischargeDateTime < 
                                                        '1/1/2009' ) ) ) ) 
                                      ))  
    Store the hard-wired date in a local variable.

    >YEAR(BatchDateTime) >= 2009

    The above is called non-SARGable predicate. Change it to:

    BatchDateTime >= '20090101'

    Blog post: http://www.sqlusa.com/bestpractices/sargable/

    Make sure each FOREIGN KEY and each column in WHERE clauses are indexed. Review the following article:

    http://www.sqlusa.com/articles/query-optimization/


    Kalman Toth SQL SERVER & BI TRAINING


    • Edited by Kalman Toth Tuesday, April 24, 2012 3:37 PM
    • Proposed as answer by amber zhang Wednesday, April 25, 2012 1:27 AM
    • Marked as answer by Iric Wen Thursday, May 3, 2012 2:51 AM
    Tuesday, April 24, 2012 3:36 PM

All replies

  • 1. Do you need all fields from Transactions table?

    2. I suggest this re-write:

    select T.Col1, T.Col2, etc. from dbo.Transactions T where T.BatchDateTime >='20090101' -- to make the query sargable OR EXISTS (select 1 from Visits V where V.VisitID = T.VisitID and InpatientOrOutpatient = 'I' and (V.AdmitDateTime >='20090101' OR V.AdmitDateTime <='20090101' and V.DischargeDateTime >='20090101')) OR EXISTS (select 1 from Visits V where V.VisitID = T.VisitID and InpatientOrOutpatient = 'O' and (V.ServiceDateTime >='20090101' OR V.ServiceDateTime IS NULL and (V.DischargeDateTime >='20090101' OR V.AdmitDateTime >='20090101'))) OR EXISTS (select 1 FROM FinancialData a INNER JOIN BarVisits b ON a.BillingID = b.BillingID WHERE b.VisitID = T.VisitID and a.PatientBalance <> 0 AND ( (InpatientOrOutpatient = 'I' AND b.VisitID IS NOT NULL AND AdmitDateTime < '20090101' AND DischargeDateTime < '20090101') OR (InpatientOrOutpatient = 'O' AND b.VisitID IS NOT NULL AND (ServiceDateTime < '20090101'

    OR (ServiceDateTime IS NULL AND (AdmitDateTime < '20090101' OR DischargeDateTime < '20090101')))) ))

    That's the idea - you may need to correct some typos. 



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    • Edited by Naomi N Tuesday, April 24, 2012 3:43 PM
    • Proposed as answer by amber zhang Wednesday, April 25, 2012 1:27 AM
    • Marked as answer by Iric Wen Thursday, May 3, 2012 2:51 AM
    Tuesday, April 24, 2012 3:31 PM
  • Good way to start optimization: beautify the code. Page: http://www.sqlusa.com/sqlformat/

    Here is the code:

    SELECT * 
    FROM   Transactions 
    WHERE  YEAR(BatchDateTime) >= 2009 
            OR VisitID IN (SELECT VisitID 
                           FROM   Visits 
                           WHERE  ( InpatientOrOutpatient = 'I' 
                                    AND VisitID IS NOT NULL 
                                    AND ( AdmitDateTime >= '1/1/2009' 
                                           OR ( AdmitDateTime < '1/1/2009' 
                                                AND DischargeDateTime >= '1/1/2009' 
                                              ) ) ) 
                                   OR ( InpatientOrOutpatient = 'O' 
                                        AND VisitID IS NOT NULL 
                                        AND ( ServiceDateTime >= '1/1/2009' 
                                               OR ( ServiceDateTime IS NULL 
                                                    AND ( AdmitDateTime >= 
                                                          '1/1/2009' 
                                                           OR DischargeDateTime >= 
                                                              '1/1/2009' ) 
                                                  ) ) ) 
                           UNION ALL 
                           SELECT b.VisitID 
                           FROM   FinancialData a 
                                  INNER JOIN BarVisits b 
                                    ON a.BillingID = b.BillingID 
                           WHERE  a.PatientBalance <> 0 
                                  AND ( ( InpatientOrOutpatient = 'I' 
                                          AND b.VisitID IS NOT NULL 
                                          AND AdmitDateTime < '1/1/2009' 
                                          AND DischargeDateTime < '1/1/2009' ) 
                                         OR ( InpatientOrOutpatient = 'O' 
                                              AND b.VisitID IS NOT NULL 
                                              AND ( ServiceDateTime < '1/1/2009' 
                                                     OR ( 
                                              ServiceDateTime IS NULL 
                                              AND ( AdmitDateTime < 
                                                    '1/1/2009' 
                                                     OR DischargeDateTime < 
                                                        '1/1/2009' ) ) ) ) 
                                      ))  
    Store the hard-wired date in a local variable.

    >YEAR(BatchDateTime) >= 2009

    The above is called non-SARGable predicate. Change it to:

    BatchDateTime >= '20090101'

    Blog post: http://www.sqlusa.com/bestpractices/sargable/

    Make sure each FOREIGN KEY and each column in WHERE clauses are indexed. Review the following article:

    http://www.sqlusa.com/articles/query-optimization/


    Kalman Toth SQL SERVER & BI TRAINING


    • Edited by Kalman Toth Tuesday, April 24, 2012 3:37 PM
    • Proposed as answer by amber zhang Wednesday, April 25, 2012 1:27 AM
    • Marked as answer by Iric Wen Thursday, May 3, 2012 2:51 AM
    Tuesday, April 24, 2012 3:36 PM