Uploaded image for project: 'i2b2 Core Software'
  1. i2b2 Core Software
  2. CORE-374

EXISTS with Group By Causes execssive run time

    XMLWordPrintable

Details

    • Improvement
    • Status: New
    • Minor
    • Resolution: Unresolved
    • None
    • TBD
    • 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.

      Attachments

        Activity

          People

            Unassigned Unassigned
            rbrad Robert Bradford
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated: