Details
-
Improvement
-
Status: New
-
Minor
-
Resolution: Unresolved
-
None
-
None
-
None
-
Rank:0|i003pj:
-
Query Tool
-
Queries, Queries - Large, Queries - Simple (no temp table), Queries - using encounter sets, Queries - using patient sets, Query-in-Query
-
Oracle
-
All Web Browsers
Description
In query that are constructed where an EXISTS statement is used causes increased query times due to the sorting and grouping performed within the sub-query. Example:
UPDATE QUERY_GLOBAL_TEMP
SET panel_count = -1
WHERE QUERY_GLOBAL_TEMP.panel_count = 4
AND EXISTS
(
SELECT 1
FROM
(
SELECT
/*+ index(observation_fact fact_cnpt_pat_enct_idx) */
f.patient_num
FROM observation_fact f
WHERE f.concept_cd IN
(
SELECT concept_cd
FROM concept_dimension
WHERE concept_path LIKE '\i2b2\DIAG\ICD10\ku5d\8s2b\nigp\%'
)
GROUP BY f.patient_num
)
t
WHERE QUERY_GLOBAL_TEMP.patient_num = t.patient_num
)
In oracle, excessive time is spent performing the grouping and sort that causes a lot of inefficiency in the queries. The first occurrence of the patient_num should be enough to satisfy the EXISTS without the need to group the sub-query results which requires a complete and repetitive scan of the index.
Additional logic within the query construction should be added to not append the group by when an EXISTS is used.
UPDATE QUERY_GLOBAL_TEMP
SET panel_count = -1
WHERE QUERY_GLOBAL_TEMP.panel_count = 4
AND EXISTS
(
SELECT 1
FROM
(
SELECT
/*+ index(observation_fact fact_cnpt_pat_enct_idx) */
f.patient_num
FROM observation_fact f
WHERE f.concept_cd IN
(
SELECT concept_cd
FROM concept_dimension
WHERE concept_path LIKE '\i2b2\DIAG\ICD10\ku5d\8s2b\nigp\%'
)
GROUP BY f.patient_num
)
t
WHERE QUERY_GLOBAL_TEMP.patient_num = t.patient_num
)
In oracle, excessive time is spent performing the grouping and sort that causes a lot of inefficiency in the queries. The first occurrence of the patient_num should be enough to satisfy the EXISTS without the need to group the sub-query results which requires a complete and repetitive scan of the index.
Additional logic within the query construction should be added to not append the group by when an EXISTS is used.