ACE
  1. ACE
  2. ACE-618

updateOltPort is having Performance issue 1.5s versus 3s

        Details

        • Type: Bug Bug
        • Status: Ready for QA Ready for QA (View Workflow)
        • Priority: Urgent Urgent
        • Resolution: Unresolved
        • Affects Version/s: None
        • Fix Version/s: None
        • Component/s: FN
        • Labels:
          None Labels
        • Customer:
          OMAN-Tel

          Description

          updateOltPort is having Performance issue 1.5s versus 3s

          <ser:updateOltPort>
          <!-Optional:->
          <msanName>IH95_01_MA5800_OLT</msanName>
          <newOltPort>1/0/3/0/15</newOltPort>
          <subscriberNo>24777728</subscriberNo>
          </ser:updateOltPort>

            Activity

            Hide
            Permalink
            Zein Trabelsi added a comment -

            optimize FN_AP_VIEW performance by rewriting PIVOT to CASE/WHEN aggregation

            The view was taking ~1 seconds due to the Oracle PIVOT operation on
            ELEMENTADDITIONALINFO scanning 83,520 rows (98.7% of total query cost)
            before joining to the target record.

            Replaced PIVOT with conditional MAX(CASE WHEN) aggregation and added
            an ATTRIBUTE IN filter to reduce scanned rows. Added MATERIALIZE hint
            on SELECTED_VERSION and merged redundant CTEs to minimize temp table
            reads. Converted implicit joins to explicit JOIN syntax for better
            optimizer predicate pushdown.

            Result: query time reduced from ~1s to 0.02s.

            Show
            Zein Trabelsi added a comment - optimize FN_AP_VIEW performance by rewriting PIVOT to CASE/WHEN aggregation The view was taking ~1 seconds due to the Oracle PIVOT operation on ELEMENTADDITIONALINFO scanning 83,520 rows (98.7% of total query cost) before joining to the target record. Replaced PIVOT with conditional MAX(CASE WHEN) aggregation and added an ATTRIBUTE IN filter to reduce scanned rows. Added MATERIALIZE hint on SELECTED_VERSION and merged redundant CTEs to minimize temp table reads. Converted implicit joins to explicit JOIN syntax for better optimizer predicate pushdown. Result: query time reduced from ~1s to 0.02s.
            Cancel

              People

              • Assignee:
                Mbaye Lo
                Reporter:
                Mbaye Lo
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Due:
                  Created:
                  Updated:
                  Planned Start:
                  Planned End:

                  Drag and Drop