Uploaded image for project: 'mod-orders-storage'
  1. mod-orders-storage
  2. MODORDSTOR-241

API call for receiving history for a poLine is very slow.

    XMLWordPrintable

Details

    • ACQ Sprint 126
    • 0
    • Thunderjet
    • R3 2021 Bug Fix
    • Cornell

    Description

      Overview:
      retreiving receiving history for a poline with the api /mod-orders/orders/receiving-history?query=checkin%3D%3Dtrue%20and%20poLineId%3D%3Df56bdce7-a554-4ebe-a51e-af39554fe656

      results in this query with a slow query explain call after for equal time:

      2021-08-18 16:01:03 UTC:xx.xx.xx.xx(48226):fs00001034_mod_orders_storage@folio:[16565]:LOG: duration: 28092.261 ms statement: SELECT DISTINCT ON(lower(f_unaccent(metadata->>'id'))) jsonb FROM fs00001034_mod_orders_storage.receiving_history_view WHERE ((true) AND ( (lower(f_unaccent(receiving_history_view.metadata->>'acqUnitIds')) NOT LIKE lower(f_unaccent('[]'))) IS NOT TRUE)) AND ((lower(f_unaccent(receiving_history_view.metadata->>'checkin')) LIKE lower(f_unaccent('true'))) AND (lower(f_unaccent(receiving_history_view.metadata->>'poLineId')) LIKE lower(f_unaccent('f56bdce7-a554-4ebe-a51e-af39554fe656')))) LIMIT 10 OFFSET 0

      2021-08-18 16:01:30 UTC:xx.xx.xx.x(48226):fs00001034_mod_orders_storage@folio:[16565]:LOG: duration: 26741.252 ms statement: EXPLAIN ANALYZE SELECT DISTINCT ON(lower(f_unaccent(metadata->>'id'))) jsonb FROM fs00001034_mod_orders_storage.receiving_history_view WHERE ((true) AND ( (lower(f_unaccent(receiving_history_view.metadata->>'acqUnitIds')) NOT LIKE lower(f_unaccent('[]'))) IS NOT TRUE)) AND ((lower(f_unaccent(receiving_history_view.metadata->>'checkin')) LIKE lower(f_unaccent('true'))) AND (lower(f_unaccent(receiving_history_view.metadata->>'poLineId')) LIKE lower(f_unaccent('f56bdce7-a554-4ebe-a51e-af39554fe656')))) LIMIT 10 OFFSET 0

      Steps to Reproduce:
      navigate to receiving app
      select an ongoing order type with a POL number
      select the POL number to view details
      note page loads but in background https://okapi-cornell.folio.ebsco.com/orders/receiving-history?query=checkin%3D%3Dtrue%20and%20poLineId%3D%3Ddf7ca02e-ebd3-43d2-94b5-dbfa74e004c1 takes 1.2 minutes to complete

      Expected Results:
      all api calls respond in reasonable time

      Actual Results:
      responsive UI causes many heavy queries to database that take over 1 minute of database time to return
      Additional Information:
      polines_view contains 36k items
      URL:
      Interested parties:

      TestRail: Results

        Attachments

          Issue Links

            Activity

              People

                Andrei_Makaranka Andrei Makaranka
                evaluk Eric Valuk
                Votes:
                0 Vote for this issue
                Watchers:
                6 Start watching this issue

                Dates

                  Created:
                  Updated:
                  Resolved:

                  TestRail: Runs

                    TestRail: Cases