Bug 38867 - Optimize Koha::Items->filter_by_for_hold for search
Summary: Optimize Koha::Items->filter_by_for_hold for search
Status: NEW
Alias: None
Product: Koha
Classification: Unclassified
Component: Searching (show other bugs)
Version: Main
Hardware: All All
: P5 - low enhancement
Assignee: Bugs List
QA Contact: Testopia
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2025-01-13 03:22 UTC by David Cook
Modified: 2025-01-15 06:13 UTC (History)
0 users

See Also:
GIT URL:
Change sponsored?: ---
Patch complexity: ---
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:
Circulation function:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description David Cook 2025-01-13 03:22:49 UTC
There are cases where Koha::Items->filter_by_for_hold() is only called 1 time in a HTTP response (e.g. on the various OPAC detail pages or the staff interface cat-toolbar.inc), but in the search results it's called N times where N is the number of records rendered on the page. This can drastically increase the number of queries going to the DB.

Out of the box, numSearchResults and OPACnumSearchResults are set to 20, but an admin can set it much higher. 

I was monitoring a long running search requests where numSearchResults was set to 1000, and many of the queries I saw were from this one function. 

That said, I didn't get a chance to look at the actual execution time for these queries. Considering what the queries are, chances are most of the time was lost on the "items" and "reserves" table. 

However, it never hurts to reduce DB queries, especially when the volume of queries increases with the volume of search results.
Comment 1 David Cook 2025-01-15 06:13:13 UTC
The total execution time for Koha::Items->filter_by_for_hold() seems to be about .003-.004. Even across 437 items (ie koha-testing-docker with numSearchResults set to 1000 and doing a * search with Elastic), that's only 1.3 seconds lost to .003*437.

Total script execution time was 16.88 seconds.

But then if I look at all the system calls through that search... they're all tiny. It's how they all add up. 

If I change numSearchResults to 100, I get my search results in 5 seconds.

If I change numSearchResults to 20, I get my search results in 1.3 seconds.

It would be interesting to look at the execution times of search_compat() and searchResults() since those are the two functions that appear to take $results_per_page as arguments.

Note that Koha::Items->filter_by_for_hold() is just one of many functions called in searchResults().

Anyway, work day well past done...