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

PUT /item-storage/items/$id performance

    XMLWordPrintable

Details

    • Prokopovych - Sprint 131, Prokopovych - Sprint 132, Prokopovych - Sprint 133
    • 5
    • Prokopovych

    Description

      Steps to Reproduce

      1. Configure PostgreSQL to log all queries: log_statement=all
      2. Install a tenant with sample data
      3. Get the JSON of first item: GET /item-storage/items/bc90a3c9-26c9-4519-96bc-d9d44995afef
      4. Write back the unchanged JSON: PUT /item-storage/items/bc90a3c9-26c9-4519-96bc-d9d44995afef

      Expected Results

      The PUT runs only necessary database queries.

      Actual Results

      2021-09-16 10:32:32.361 UTC [78] LOG:  execute <unnamed>: SELECT jsonb FROM diku_mod_inventory_storage.item WHERE id = $1
2021-09-16 10:32:32.361 UTC [78] DETAIL:  parameters: $1 = 'bc90a3c9-26c9-4519-96bc-d9d44995afef'
2021-09-16 10:32:32.369 UTC [79] LOG:  execute <unnamed>: SELECT id, jsonb FROM diku_mod_inventory_storage.holdings_record WHERE id IN ($1)
2021-09-16 10:32:32.369 UTC [79] DETAIL:  parameters: $1 = '0c45bb50-7c9b-48b0-86eb-178a494e25fe'
2021-09-16 10:32:32.380 UTC [80] LOG:  execute <unnamed>: UPDATE diku_mod_inventory_storage.item SET jsonb = $1::jsonb WHERE id='bc90a3c9-26c9-4519-96bc-d9d44995afef'
2021-09-16 10:32:32.380 UTC [80] DETAIL:  parameters: $1 = '{"id": "bc90a3c9-26c9-4519-96bc-d9d44995afef", "hrid": "item000000000001", "notes": [], "status": {"date": "2021-09-16T10:24:06.372+00:00", "name": "Available"}, "barcode": "A14811392695", "_version": 1, "metadata": {"createdDate": "2021-09-16T10:32:32.352+00:00", "updatedDate": "2021-09-16T10:32:32.352+00:00"}, "formerIds": [], "chronology": "1987:Jan.-June", "enumeration": "v.73:no.1-6", "yearCaption": [], "materialTypeId": "d9acad2f-2aac-4b48-9097-e6ab85906b25", "circulationNotes": [], "electronicAccess": [], "holdingsRecordId": "0c45bb50-7c9b-48b0-86eb-178a494e25fe", "statisticalCodeIds": [], "effectiveLocationId": "fcd64ce1-6995-48f0-840e-89ffa2288371", "permanentLoanTypeId": "2b94c631-fca9-4892-a730-03ee529ffe27", "effectiveShelvingOrder": "K 11 M44 V 273 NO 11 16 41987 JAN JUNE", "effectiveCallNumberComponents": {"callNumber": "K1 .M44"}}'
2021-09-16 10:32:32.395 UTC [81] LOG:  execute <unnamed>: SELECT id, jsonb FROM diku_mod_inventory_storage.item WHERE id IN ($1)
2021-09-16 10:32:32.395 UTC [81] DETAIL:  parameters: $1 = 'bc90a3c9-26c9-4519-96bc-d9d44995afef'
2021-09-16 10:32:32.400 UTC [78] LOG:  execute <unnamed>: SELECT id, jsonb FROM diku_mod_inventory_storage.holdings_record WHERE id IN ($1)
2021-09-16 10:32:32.400 UTC [78] DETAIL:  parameters: $1 = '0c45bb50-7c9b-48b0-86eb-178a494e25fe'
2021-09-16 10:32:32.403 UTC [78] LOG:  execute <unnamed>: SELECT id, jsonb FROM diku_mod_inventory_storage.holdings_record WHERE id IN ($1)
2021-09-16 10:32:32.403 UTC [78] DETAIL:  parameters: $1 = '0c45bb50-7c9b-48b0-86eb-178a494e25fe'

      Condensed log:

      32.361 SELECT jsonb FROM item          WHERE id = 'bc90a3c9-26c9-4519-96bc-d9d44995afef'
32.369 SELECT id, jsonb FROM holdings_record WHERE id IN ('0c45bb50-7c9b-48b0-86eb-178a494e25fe')
32.380 UPDATE item SET jsonb = $1::jsonb WHERE id='bc90a3c9-26c9-4519-96bc-d9d44995afef'
32.395 SELECT id, jsonb FROM item    WHERE id IN ('bc90a3c9-26c9-4519-96bc-d9d44995afef')
32.400 SELECT id, jsonb FROM holdings_record WHERE id IN ('0c45bb50-7c9b-48b0-86eb-178a494e25fe')
32.403 SELECT id, jsonb FROM holdings_record WHERE id IN ('0c45bb50-7c9b-48b0-86eb-178a494e25fe')

      Analysis

      ItemService has 3 phases with database access:

      https://github.com/folio-org/mod-inventory-storage/blob/v21.0.4/src/main/java/org/folio/services/item/ItemService.java#L102-L116

      • populateEffectiveValues, might need values from the holdings record
      • update the item
      • publish the update via Kafka

      holdings_record 0c45bb50-7c9b-48b0-86eb-178a494e25fe is fetched 3 times, one time to calculate effective values, two times for Kafka.

      The holdings record fetch before the UPDATE is used to calculate effective values of several fields. However, if no item field that influence the effective values has changed the effective value cannot change and the holdings record is not needed. An additional check should be added to skip the holdings record fetch.

      The UPDATE should be extended by "RETURNING jsonb". This avoids fetching the new item after the UPDATE and saves the round trip of a database query.

      Fetching the holding records after the UPDATE should be optimized: Check if the holding has already been fetched before (for calculating effective values). Don't fetch the same record twice, reuse.

      If holdings records are needed for Kafka the fetch should run in parallel to the item update.

      Additional Information:

      This PUT is used for Check-In and Check-Out that have tight performance requirements:

      PUT is also used for bulk updating items from external sources (union catalog, etc.).

      This justifies performance optimization of this PUT API.

      TestRail: Results

        Attachments

          Issue Links

            defines

            Umbrella - MODINVSTOR-870 POST/PUT instance/holding/item performance

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

            Activity

              People

                felkerk Kyle Felker
                julianladisch Julian Ladisch
                Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                  Created:
                  Updated:
                  Resolved:

                  TestRail: Runs

                    TestRail: Cases