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: [email protected]
Reporter: [email protected]
QA Contact: [email protected]
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.
_______________________________________________
Koha-bugs mailing list
[email protected]
https://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs
website : http://www.koha-community.org/
git : http://git.koha-community.org/
bugs : http://bugs.koha-community.org/
