Uploaded image for project: 'mod-inventory-storage'
  1. mod-inventory-storage
  2. MODINVSTOR-248

filtering /item-storage/items when used with 'query=id=XXX' is too slow

    XMLWordPrintable

Details

    • Core: Platform

    Description

      When calling the api request to mod-circulation /circulation/loans?query=(userId=<useId>)&limit=1 (the entry in the Jenkins perf job is mod-circulation GET 200 circulation_loans check loans by user d).

      you issue the DB query:

      SELECT * FROM diku_mod_inventory_storage.items_mt_view WHERE lower(f_unaccent(items_mt_view.jsonb->>'id')) LIKE lower(f_unaccent('d1600ee1-c084-4da7-9c85-cdbcc143cb4c')) LIMIT 1 OFFSET 0

      This query does not scale well due to not using indexes. This is observed on tenants that are much larger then those that the vagrant box contains, on the order of 300k items

      The preferred fix is to change the query to no longer use the view

      Current theory is that the view misleads the cql translation into the current form rather then a higher performing and simpler version.

      evaluk list the CQL query string (requests) and generated SQL we want to optimize.

      The query string:

      /item-storage/items?query=id=d1600ee1-c084-4da7-9c85-cdbcc143cb4c&limit=1

      results in

      SELECT * FROM diku_mod_inventory_storage.items_mt_view  WHERE lower(f_unaccent(items_mt_view.jsonb->>'id')) LIKE lower(f_unaccent('d1600ee1-c084-4da7-9c85-cdbcc143cb4c')) LIMIT 1 OFFSET 0

      TestRail: Results

        Attachments

          Issue Links

            blocks

            Bug - A problem which impairs or prevents the functions of the product. CIRC-190 "mod-circulation GET 200 circulation_loans check loans by user id" takes too long

            • P3 - low priority level, item will be considered for inclusion in the next dev cycle
            • Closed

            Task - A task that needs to be done. MODINVSTOR-237 Performance: GET 200 - Get updated mod-inventory/item by using modInventoryItemId

            • P2 - normal priority level, must be included in the current development cycle
            • Closed
            duplicates

            Bug - A problem which impairs or prevents the functions of the product. MODINVSTOR-247 filtering /item-storage/items by 'query=barcode==123' does not perform

            • P1 - highest priority item, drop everything else before this is resolved, reserved for critical bugfixes
            • Closed
            relates to

            Task - A task that needs to be done. FOLIO-1746 SPIKE: When should a major behavioural version be reflected in the implementation version?

            • P2 - normal priority level, must be included in the current development cycle
            • Closed

            Task - A task that needs to be done. MODINVSTOR-238 Performance: GET 200 - instance-storage by ID from POST above

            • P2 - normal priority level, must be included in the current development cycle
            • Closed

            New Feature - New feature requests FOLIO-1771 clean up and isolate tests

            • P2 - normal priority level, must be included in the current development cycle
            • Closed

            Bug - A problem which impairs or prevents the functions of the product. MODINVSTOR-246 filtering /instance-storage/instances by 'query=id=xxx' does not perform

            • P2 - normal priority level, must be included in the current development cycle
            • Closed

            Activity

              People

                evaluk Eric Valuk
                jakub Jakub Skoczen
                Votes:
                0 Vote for this issue
                Watchers:
                6 Start watching this issue

                Dates

                  Created:
                  Updated:
                  Resolved:

                  TestRail: Runs

                    TestRail: Cases