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

Improve performance of /instance-storage/instances?query=holdingsRecords.permanentLocationId=abc*

    XMLWordPrintable

Details

    • Core: Platform - Sprint 56, Core: Platform - Sprint 57, CP: ready for planning, CP: sprint 70, CP: sprint 69, CP: sprint 71, CP: sprint 72, CP: sprint 73, CP: sprint 74, CP: sprint 75, CP: sprint 76, CP: sprint 77, CP: sprint 78
    • 8
    • Core: Platform

    Description

      UI inventory uses CQL queries like the below, a view is required:

      ((title="*" or contributors adj "\"name\": \"*\"" or identifiers adj "\"value\": \"*\"")) and holdingsRecords.permanentLocationId="fcd64ce1-6995-48f0-840e-89ffa2288371" sortby title

      Smallest testcase triggering the view:

      holdingsRecords.permanentLocationId="fcd64ce1-6995-48f0-840e-89ffa2288371"

      Resulting SQL:

      lower(f_unaccent(instance_holding_view.ho_jsonb->>'permanentLocationId')) ~
lower(f_unaccent('(^|[[:punct:]]|[[:space:]]|(?=[[:punct:]]|[[:space:]]))fcd64ce1-6995-48f0-840e-89ffa2288371($|[[:punct:]]|[[:space:]]|(?<=[[:punct:]]|[[:space:]]))'))

      Expected:

      The generated Query should not use a view but a nested subquery that performs filtering by permanentLocationId, similar to:

      _id IN (SELECT (jsonb->>'instanceId')::UUID from holdings_record WHERE jsonb->>'permanentLocationId' = 'fcd64ce1-6995-48f0-840e-89ffa2288371')

      Acceptance criteria:

      • CQL2PG and RMB that support generation of nested subqueries when a foreign key filtering is invoked in CQL
      • mod-inventory-storage version that supports filtering by location combined with searching for titles, contributors and identifiers and does not use a view but a SQL subquery

      TestRail: Results

        Attachments

          Issue Links

            blocks

            Task - A task that needs to be done. RMB-441 Consider to use = over LIKE when search term has no wildcard

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

            New Feature - New feature requests UXPROD-1820 RMB 27 release features and core module rollout (performance, virtual indexes, search enhancements)

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

            New Feature - New feature requests UXPROD-2119 RMB 28 release features and core module rollout (performance, virtual indexes, search enhancements)

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

            Task - A task that needs to be done. CQLPG-95 SPIKE: design foreign key search support

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

            Task - A task that needs to be done. FOLIO-1920 SPIKE: optimize query instance by location

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

            Task - A task that needs to be done. FOLIO-2255 Update perf test data to populate foreign key data

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

            Task - A task that needs to be done. MODINVSTOR-339 Use new foreign key for holdings and bar code search

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

            Task - A task that needs to be done. MODINVSTOR-368 Upgrade to RMB 27.1.1

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

            Task - A task that needs to be done. MODINVSTOR-403 Upgrade to RMB 29.1.0

            • TBD - To be determined
            • Closed

            Task - A task that needs to be done. RMB-395 support nested sub queries across multiple tables

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

            New Feature - New feature requests RMB-468 Foreign key field index

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

            Task - A task that needs to be done. RMB-497 Replace JSQL parser by using SqlSelect from CQL2PgJSON

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

            Task - A task that needs to be done. RMB-506 estimate hit counts

            • TBD - To be determined
            • Closed

            Bug - A problem which impairs or prevents the functions of the product. RMB-526 Upgrade failing: pgpsql , create or replace count_estimate_smart2

            • TBD - To be determined
            • Closed
            is duplicated by

            Task - A task that needs to be done. MODINVSTOR-185 Analyze filtering by location

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

            Task - A task that needs to be done. CQLPG-82 Allow CQL2PGJSON to specify the path for the 'schema.json' file

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

            Task - A task that needs to be done. FOLIO-2328 verify performance of filtering instances by item.effectiveLocationId

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

            Task - A task that needs to be done. MODINVSTOR-185 Analyze filtering by location

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

            Bug - A problem which impairs or prevents the functions of the product. MODINVSTOR-255 GET 200 - instance-storage by source: query=source=abc takes 360 s

            • P4 - lowest priority level, nice-to have things that require future discussion and design
            • Closed

            Task - A task that needs to be done. MODINVSTOR-273 SPIKE: analyze instance storage API location filter performance

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

            Task - A task that needs to be done. RMB-486 Use indexed id fields in foreign tables: holdingsRecords.permanentLocationId==abc*

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

            Bug - A problem which impairs or prevents the functions of the product. CHAL-92 In Inventory, filtering on Location takes up to one minute

            • TBD - To be determined
            • Closed

            New Feature - New feature requests CQLPG-37 Schema driven CQL generation and PGFT support

            • P3 - low priority level, item will be considered for inclusion in the next dev 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

            Task - A task that needs to be done. MODINVSTOR-215 use sort or search index depending on the result size

            • 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

            Bug - A problem which impairs or prevents the functions of the product. MODINVSTOR-336 GET inventory_items?query=(status.name=="Available") slow

            • 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-368 Upgrade to RMB 27.1.1

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

            New Feature - New feature requests RMB-457 option to enable: analyze explain queries

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

            Bug - A problem which impairs or prevents the functions of the product. STSMACOM-259 Instance search result total is wrong when there are 10K+ records in Inventory

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

            Activity

              People

                adam Adam Dickmeiss (Inactive)
                julianladisch Julian Ladisch
                Votes:
                0 Vote for this issue
                Watchers:
                7 Start watching this issue

                Dates

                  Created:
                  Updated:
                  Resolved:

                  TestRail: Runs

                    TestRail: Cases