Bug 38867

Summary: Optimize Koha::Items->filter_by_for_hold for search
Product: Koha Reporter: David Cook <dcook>
Component: SearchingAssignee: Bugs List <koha-bugs>
Status: NEW --- QA Contact: Testopia <testopia>
Severity: enhancement    
Priority: P5 - low    
Version: Main   
Hardware: All   
OS: All   
See Also: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38868
GIT URL: Change sponsored?: ---
Patch complexity: --- Documentation contact:
Documentation submission: Text to go in the release notes:
Version(s) released in:
Circulation function:

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...