CODEWITHSUNDEEP
sql-serverperformance
CM_Heroman
CM_Heroman

Reputation: 378

SQL Query tuning

I just started learning query tuning and I am looking for some help with a question that was asked of me. This query runs for hours if left as is, but I am told that a small change will make it run in a matter of moments. I have tried several things but can't seem to make it do what I am looking for. Any help would be greatly appreciated

SELECT     dt_ARRIVAL, s_PATIENT_FULL_NAME, n_ENCOUNTER_ID, s_VISIT_IDENT, dbo.fn_CheckIfRV(n_ENCOUNTER_ID, dt_ARRIVAL) AS RV, dt_DEPARTURE, 
                  s_OUTCOME_LOCATION, s_DispoLoc, s_LAST_ACUITY, s_COMPLAINT_FOR_SORT, s_DIAGNOSIS_FOR_SORT, s_GENDER, s_AGE, 
                  CASE WHEN Len(dbo.vw_RPT_ADHOC_DATA_DUMP.s_InitLabOrderList) > 4 THEN 1 ELSE 0 END AS LABv21, 
                  CASE WHEN Len(dbo.vw_RPT_ADHOC_DATA_DUMP.s_InitRadOrderList) > 4 THEN 1 ELSE 0 END AS RADv21
FROM         dbo.vw_RPT_ADHOC_DATA_DUMP
WHERE     (dt_ARRIVAL BETWEEN CONVERT(DATETIME, '2012-08-13 00:00:00', 102) AND     CONVERT(DATETIME, '2013-02-13 23:00:00', 102))

Upvotes: 0

Views: 129

Answers (2)

borjab
borjab

Reputation: 11655

Just judging by the "vw" in dbo.vw_RPT_ADHOC_DATA_DUMP it seems that the query is against a view, not a table. Have a look to the inner connect of the view and try to optimize it. Even if the view is optimized you may find that for this specific query you can take some simplifications.

Anyway we would need to see the code of the view to improve our answers, if it is really a view.

Upvotes: 0

StanislavL
StanislavL

Reputation: 57381

SELECT     dt_ARRIVAL, s_PATIENT_FULL_NAME, n_ENCOUNTER_ID, s_VISIT_IDENT, dbo.fn_CheckIfRV(n_ENCOUNTER_ID, dt_ARRIVAL) AS RV, dt_DEPARTURE, 
                  s_OUTCOME_LOCATION, s_DispoLoc, s_LAST_ACUITY, s_COMPLAINT_FOR_SORT, s_DIAGNOSIS_FOR_SORT, s_GENDER, s_AGE, 
                  CASE WHEN Len(dbo.vw_RPT_ADHOC_DATA_DUMP.s_InitLabOrderList) > 4 THEN 1 ELSE 0 END AS LABv21, 
                  CASE WHEN Len(dbo.vw_RPT_ADHOC_DATA_DUMP.s_InitRadOrderList) > 4 THEN 1 ELSE 0 END AS RADv21
FROM         dbo.vw_RPT_ADHOC_DATA_DUMP, 
       (select CONVERT(DATETIME, '2012-08-13 00:00:00', 102)  as date_from, 
               CONVERT(DATETIME, '2013-02-13 23:00:00', 102) as date_to) dates
WHERE     (dt_ARRIVAL BETWEEN dates.date_from AND dates.date_to)

Try to execte the CONVERT just once, not for each row

Upvotes: 2

Related Questions