June 19, 2020 at 6:56 pm
A BA recently ran this query and it got flagged by a capturing event for having > 130,000,000 logical reads. Is there a better way to handle query can join back onto themselves? What would make something like this more efficient?
SELECT 'Term Reversals' AS 'TERM REVERSAL',
ev.employee_id,
ev.ssn,
e2.import_run_id,
e2.string_value,
e2.effective_date,
e.import_run_id,
e.string_value,
e.effective_date
FROM IMPORT_RUN_EMPLOYEE_ATTRIBUTE_DETAIL_VIEW e
INNER JOIN IMPORT_RUN_EMPLOYEE_ATTRIBUTE_DETAIL_VIEW e2 ON e.ssn = e2.ssn
AND e2.EMPLOYEE_ATTRIBUTE_ID = e.EMPLOYEE_ATTRIBUTE_ID
AND e2.string_value = 'T'
AND e2.IMPORT_RUN_ID = (
SELECT MAX(z2.import_run_id)
FROM import_run_employee_attribute_detail_view z2
WHERE e2.ssn = z2.ssn
AND z2.EMPLOYEE_ATTRIBUTE_ID = 'status'
AND z2.import_run_id < e.import_run_id
)
INNER JOIN employee_view ev ON e.ssn = ev.ssn
WHERE e.import_run_id =
(
SELECT MAX(z.import_run_id)
FROM import_run_employee_attribute_detail_view z
WHERE e.ssn = z.ssn
AND z.EMPLOYEE_ATTRIBUTE_ID = 'status'
)
AND e.string_value <> 'T'
AND e.effective_date <= e2.effective_date
AND e.EMPLOYEE_ATTRIBUTE_ID = 'status'
AND e.import_run_id <> 227
ORDER BY e.import_run_id
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
June 19, 2020 at 7:26 pm
Here is my first suggestion, load the output of the view IMPORT_RUN_EMPLOYEE_ATTRIBUTE_VIEW into a properly indexed temporary table and use that temporary table in the query since that view is used 4 times in the query. Without seeing the DDL for the view, the fact that it is used 4 times means it is being evaluated (i.e. the underlying tables in the view are queried 4 times).
Another idea would be to materialize view if it meets the requirements to do that.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply