Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

Investigate way to improve performance of Receiving filtering

Description

After generating acquisition data, we faced a problem with filtering data using order status
When click App-> Receiving
Filter:
Ordre status:
Pending

image-20240618-212725.png

 

It returns 0 records and on the database side we see an SQL query that is running for a few hours

 

image-20240618-212817.png

 

SELECT jsonb,id FROM cs00000int_mod_orders_storage.titles WHERE ((get_tsvector(f_unaccent(titles.jsonb->>'acqUnitIds')) @@ tsquery_phrase(f_unaccent('0ebb1f7d-983f-3026-8a4c-5318e0ebc041'))) OR ((true) AND ( (CASE WHEN length(lower(f_unaccent('[]'))) <= 600 THEN left(lower(f_unaccent(titles.jsonb->>'acqUnitIds')),600) NOT LIKE lower(f_unaccent('[]')) ELSE left(lower(f_unaccent(titles.jsonb->>'acqUnitIds')),600) NOT LIKE left(lower(f_unaccent('[]')),600) OR lower(f_unaccent(titles.jsonb->>'acqUni

The only way to stop is to reboot the database

Environment

QELC

Potential Workaround

None

Attachments

7
  • 20 Jun 2024, 04:51 PM
  • 19 Jun 2024, 12:35 PM
  • 19 Jun 2024, 12:35 PM
  • 19 Jun 2024, 10:11 AM
  • 19 Jun 2024, 10:11 AM
  • 18 Jun 2024, 09:28 PM
  • 18 Jun 2024, 09:28 PM

Checklist

hide

Activity

Show:

Serhii_Nosko July 11, 2024 at 7:23 AM

Filtering response time has been improved in the Receiving app together with sorting, so going to close this ticket.

Found issue when selecting both 2 values of Order type filter (One time and Ongoing) at the same time and created separate story to use radio buttons in this filter: https://folio-org.atlassian.net/browse/UIREC-375

This particular story can be closed.

JenkinsNotifications July 9, 2024 at 7:34 AM

Deployed to the Quesnelia bf env. Moved status to In bugfix review from status Awaiting deployment. Please proceed with the verification.

Serhii_Nosko July 4, 2024 at 12:22 PM

Mykhailo Petryshyn June 20, 2024 at 4:51 PM

Hi
Here I got the duration of sorting, please check these numbers.
1. Orders → Order liner
Search all order lines(481K)
Sorting: POL number - 1,1 seconds
Update date- 1,2 seconds
Title or package name - 800ms

2. Receiving
Order status: Open(640,352)

Sorting: Title- 2,2 seconds
Package- 7,4 seconds ()
POL number- 8.6 seconds

Receiving note - 7.1 seconds
On our dataset, we didn`t fill the packager and receiving note section

image-20240620-164939.png

Checked manually from UI



Serhii_Nosko June 19, 2024 at 1:15 PM

I agree, for PO Line table we already have more than 40 indexes and they should cover main filtering fields and Receiving Titles search uses PO Lines fields or Piece fields (11 indexes that also covers main fields). Also did not see issues with filtering by other filters during his testing on big dataset, so propose to add indexes only for sorting, because they have the biggest influence to search.

Done

Details

Assignee

Reporter

Labels

Priority

Story Points

Sprint

Development Team

Thunderjet

Fix versions

Release

Quesnelia (R1 2024) Service Patch #1

CSP Approved

Yes

TestRail: Cases

Open TestRail: Cases

TestRail: Runs

Open TestRail: Runs

Created June 18, 2024 at 9:27 PM
Updated 4 days ago
Resolved July 4, 2024 at 12:21 PM
TestRail: Cases
TestRail: Runs

Flag notifications