https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41365 Bug ID: 41365 Summary: objects.search_rs puts too much pressure on the DB Initiative type: --- Sponsorship --- status: Product: Koha Version: unspecified Hardware: All OS: All Status: NEW Severity: major Priority: P5 - low Component: Architecture, internals, and plumbing Assignee: koha-bugs@lists.koha-community.org Reporter: tomascohen@gmail.com QA Contact: testopia@bugs.koha-community.org Found in the wild while analyzing a performance issue on a very big partner site. ```shell perl -MKoha::Patrons -MTime::HiRes=time -e '$q={"-or"=>[map{{("me.$_"=>{like=>"%nick%"})}}qw(surname firstname cardnumber)]};$t=time;$rs=Koha::Patrons->search($q,{prefetch=>"branchcode"});$c=$rs->count;$p=time-$t;$t=time;$rs=Koha::Patrons->search($q);$c=$rs->count;$n=time-$t;printf"Pagination COUNT with prefetch: %.4fs\nPagination COUNT without: %.4fs\nSlowdown: %.1fx\n",$p,$n,$p/$n' Pagination COUNT with prefetch: 2.7349s Pagination COUNT without: 0.8491s Slowdown: 3.2x ``` The key here is that 'prefetch' not only introduces a JOIN, but also makes DBIC inflate the DB response into structures, so it takes a lot of resources, adding to the API response latency. This oneliner highlights a specific design issue. I'll now explain how it impacts API searches: When we invoke `objects.search_rs`, we usually do it with pagination parameters and filters (on the UI, for example in patron search). This means the actual search shouldn't have a performance impact because the resultset is tiny! But there's a catch: before adding pagination and filters, we perform a `search->count()` on the resultset! To populate our pagination-related headers (x-total-count, etc)! So we are effectively performing a search, with many joins and hashref inflation on the full resultset, only to get a count! I'll provide a trivial patch for this. -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.