Uploaded image for project: 'RAML Module Builder'
  1. RAML Module Builder
  2. RMB-718

Avoid calling count_estimate() if GET collection API always return 1 record

    XMLWordPrintable

    Details

    • Template:
      Standard Bug Write-Up Format
    • Sprint:
      CP: sprint 126, CP: sprint 127, CP: sprint 128
    • Story Points:
      2
    • Development Team:
      Core: Platform

      Description

      Overview:
      If GET by collection API always returns either 0(Not found) or 1(Found) totalRecords then do not call count_estimate() query which does https://github.com/folio-org/raml-module-builder#estimated-totalrecords

      For example:
      GET /item-storage/items?query=barcode==actual_barcode will always return either 0 or 1 because the barcode is unique as per https://issues.folio.org/browse/MODINVSTOR-523. But, for every API call, SELECT count_estimate() query is trigger which takes more time than the actual call and adds unnecessary latency. We can improve the performance by more than 50% if we remove count_estimate() call under such circumstances.

      As we are testing various FOLIO workflows, we are seeing this pattern being repeated in many API calls just to lookup by a unique ID that surely returns only one record but getting taxed by the count_estimate() calls.

      Suggested solution:
      Add a flag in the CQL query to indicate that this is a unique lookup then RMB would not issue the count_estimate() call.

      Steps to Reproduce:
      For example:
      Make a API call to /item-storage/items?query=barcode==28705124 for random barcode

      Postgresql query log:

      2020-08-17 16:00:08 UTC:10.23.10.157(34036):fs09000000_mod_inventory_storage@folio:[30635]:LOG: statement: SELECT count_estimate('SELECT jsonb,id FROM fs09000000_mod_inventory_storage.item WHERE lower(f_unaccent(item.jsonb->>''barcode'')) LIKE lower(f_unaccent(''28705124''))')
      2020-08-17 16:00:08 UTC:10.23.10.157(34036):fs09000000_mod_inventory_storage@folio:[30635]:LOG: duration: 173.825 ms
      2020-08-17 16:00:08 UTC:10.23.10.157(34036):fs09000000_mod_inventory_storage@folio:[30635]:LOG: statement: BEGIN
      2020-08-17 16:00:08 UTC:10.23.10.157(34036):fs09000000_mod_inventory_storage@folio:[30635]:LOG: execute 00013AE/d261a34d-468e-45c0-a5f1-01c11b7aac8d: SELECT jsonb,id FROM fs09000000_mod_inventory_storage.item WHERE lower(f_unaccent(item.jsonb->>'barcode')) LIKE lower(f_unaccent('28705124')) LIMIT 10 OFFSET 0
      2020-08-17 16:00:08 UTC:10.23.10.157(34036):fs09000000_mod_inventory_storage@folio:[30635]:LOG: duration: 169.649 ms
      
      1. Log into perf env https://goldenrod-cap1.int.aws.folio.org environment as User folio using valid okapi token

      Expected Results:
      GET by collection with a query API request should not make calls to SELECT count_estimate() query in the database under certain circumstances as mentioned above and improve performance.

      Actual Results:
      GET by collection with a query API request is making calls to SELECT count_estimate() query in the database which is adding unnecessary latency

      Additional Information:
      Looks for comments in https://issues.folio.org/browse/PERF-79

      Interested parties:
      All consumers of backend API who want to improve their performance

        TestRail: Results

          Attachments

            Issue Links

              Activity

                People

                Assignee:
                julianladisch Julian Ladisch
                Reporter:
                varunjavalkar Varun Javalkar
                Votes:
                0 Vote for this issue
                Watchers:
                7 Start watching this issue

                  Dates

                  Created:
                  Updated:
                  Resolved:

                    TestRail: Runs

                      TestRail: Cases