Uploaded image for project: 'ui-users'
  1. ui-users
  2. UIU-2063

Performance issues with Users app with large dataset of loans and fees/fines

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: P2
    • Resolution: Done
    • Affects Version/s: 5.0.9
    • Fix Version/s: 6.0.0
    • Labels:
      None
    • Template:
      Standard Bug Write-Up Format
    • Sprint:
      Prokopovych - Sprint 109, Prokopovych - Sprint 110
    • Story Points:
      8
    • Development Team:
      Prokopovych
    • Release:
      R1 2021 Bug Fix
    • Affected Institution:
      University of Chicago

      Description

      Overview:
      A user reports:

      I'm seeing performance issues with fees/fines in the Users app. Bringing up my own record, it took 8 seconds for the open fees page to move from showing a balance of zero with no fines, to listing the $1500+ in fines actually on the account. Is this a known performance issue?

      In my testing, performance was quite bad for feesfines and loans. The log for mod-feesfines reports:

      WARNING: Doing LIKE search without index for accounts.jsonb->>'userId', CQL >>> SQL: userId == 9ac59f38-d27a-4dd2-bb87-86759754d1a3 >>> lower(f_unaccent(accounts.jsonb->>'userId')) LIKE lower(f_unaccent('9ac59f38-d27a-4dd2-bb87-86759754d1a3'))
      Jan 20, 2021 9:31:17 PM org.folio.cql2pgjson.CQL2PgJSON queryByFt
      WARNING: Doing FT search without index for accounts.jsonb->'status'->>'name', CQL >>> SQL: status.name = Closed >>> get_tsvector(f_unaccent(accounts.jsonb->'status'->>'name')) @@ tsquery_phrase(f_unaccent('Closed'))
      

      as well as

      WARNING: Doing LIKE search without index for feefineactions.jsonb->>'userId', CQL >>> SQL: userId == 9ac59f38-d27a-4dd2-bb87-86759754d1a3 >>> lower(f_unaccent(feefineactions.jsonb->>'userId')) LIKE lower(f_unaccent('9ac59f38-d27a-4dd2-bb87-86759754d1a3'))
      

      The log for mod-circulation-storage reports:

      WARNING: Doing LIKE search without index for loan.jsonb->>'action', CQL >>> SQL: action == claimedReturned >>> lower(f_unaccent(loan.jsonb->>'action')) LIKE lower(f_unaccent('claimedReturned'))
      Jan 20, 2021 10:58:44 PM org.folio.cql2pgjson.CQL2PgJSON queryByLike
      

      These requests happen multiple times when you access the user record and the user's fees/fines. The slow requests are:

      https://uchicago-test-okapi.folio.indexdata.com/accounts?query=(userId==9ac59f38-d27a-4dd2-bb87-86759754d1a3%20and%20status.name%3C%3EClosed)&limit=10000
      
      https://uchicago-test-okapi.folio.indexdata.com/accounts?query=(userId==9ac59f38-d27a-4dd2-bb87-86759754d1a3)&limit=10000
      
      https://uchicago-test-okapi.folio.indexdata.com/feefineactions?query=(userId==9ac59f38-d27a-4dd2-bb87-86759754d1a3)&limit=10000
      
      https://uchicago-test-okapi.folio.indexdata.com/feefineactions?query=(userId==9ac59f38-d27a-4dd2-bb87-86759754d1a3%20and%20comments=*)&limit=200
      
      https://uchicago-test-okapi.folio.indexdata.com/accounts?query=userId==9ac59f38-d27a-4dd2-bb87-86759754d1a3&limit=10000
      
      https://uchicago-test-okapi.folio.indexdata.com/accounts?limit=10000&query=userId%3D%3D9ac59f38-d27a-4dd2-bb87-86759754d1a3
      
      https://uchicago-test-okapi.folio.indexdata.com/accounts?query=(userId==9ac59f38-d27a-4dd2-bb87-86759754d1a3%20and%20status.name%3C%3EClosed)&limit=10000
      

      Also quite slow:

      https://uchicago-test-okapi.folio.indexdata.com/circulation/loans?query=(userId==9ac59f38-d27a-4dd2-bb87-86759754d1a3)%20sortby%20id&limit=2000
      
      https://uchicago-test-okapi.folio.indexdata.com/circulation/loans?query=(userId==9ac59f38-d27a-4dd2-bb87-86759754d1a3)&limit=1000
      
      https://uchicago-test-okapi.folio.indexdata.com/circulation/loans?limit=1&query=%28userId%3D%3D9ac59f38-d27a-4dd2-bb87-86759754d1a3%20and%20status.name%3C%3EClosed%29
      
      https://uchicago-test-okapi.folio.indexdata.com/circulation/loans?limit=1&query=userId%3D%3D9ac59f38-d27a-4dd2-bb87-86759754d1a3%20and%20status.name%3C%3EClosed%20and%20action%3D%3DclaimedReturned
      

      There are obviously multiple issues here:

      • The UI is making redundant requests of the backend, which should be cleaned up. The problem is exacerbated by the fact that the requests are very slow to respond when there is a large dataset of loan and fee/fine records.
      • The backend modules are missing indexes that could help with performance.

      I will raise separate issues for the backend.

      This affects the current Honeysuckle release, v5.0.9. I note that this is listed as "unreleased" in JIRA.

        TestRail: Results

          Attachments

          1. after.png
            after.png
            65 kB
          2. before.png
            before.png
            267 kB

            Issue Links

              Activity

                People

                Assignee:
                mpk35 Michal Kuklis
                Reporter:
                wayne Wayne Schneider
                Votes:
                0 Vote for this issue
                Watchers:
                5 Start watching this issue

                  Dates

                  Created:
                  Updated:
                  Resolved:

                    TestRail: Runs

                      TestRail: Cases