[Bug 38181] New: Extreme slow search results on OPAC (only) search when result records contain multiple items.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38181 Bug ID: 38181 Summary: Extreme slow search results on OPAC (only) search when result records contain multiple items. Change sponsored?: --- Product: Koha Version: 24.05 Hardware: All OS: Linux Status: NEW Severity: normal Priority: P5 - low Component: Searching Assignee: koha-bugs@lists.koha-community.org Reporter: kdask@altsol.gr QA Contact: testopia@bugs.koha-community.org When performing a search on the OPAC, if the results containing records that are having a large number of holdings (e.g serials with over 1000 issues/items or even less) there is a huge delay on showing the results (~x8 times slower for the same search if we remove the multiple items from the resulting records) The odd is that the exact same search on the intranet interface works without these delays so I'm guessing that this is a problem on the OPAC search implementation? The problem exists with zebra but also with the use of Elasticsearch engine. Using elasticsearch tried also removing all the item related indexes - 952* subfields (and re-indexing) but the result is the same. In the following google drive link you can find a demo bib record with 1809 items (MarcXML - Marc21) if it helps to reproduce it.https://drive.google.com/file/d/11SQmfdWal9tzcovS0mrgu6vIOPf1aCbc/view You will have to import the record, and then try searching for the title on OPAC: The Journal of biological chemistry. In my case with a decent server without load (Debian, 4cpu cores, 16gb RAM) with plack enabled takes about ~50sec to get the result. Regards -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38181 Kdask <kdask@altsol.gr> changed: What |Removed |Added ---------------------------------------------------------------------------- OS|Linux |All -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38181 David Cook <dcook@prosentient.com.au> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |dcook@prosentient.com.au --- Comment #1 from David Cook <dcook@prosentient.com.au> --- Are you using the OpacHiddenItems system preference? There are OPAC-only system preferences that implement post-processing to search results, which can have considerable negative effects. This issue might be a duplicate of bug 36261 -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38181 --- Comment #2 from Kdask <kdask@altsol.gr> --- Interesting, i don't use the OpacHiddenItems preference (it's empty) although the OpacHiddenItemsHidesRecord was on "hide". I'm using OpacSuppression on bibliographic records on 942$n. I tried setting OpacHiddenItemsHidesRecord (and OpacSuppression) to -> Don't hide, re-indexed, restarted but still the same, both zebra and elasticsearch. -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38181 --- Comment #3 from David Cook <dcook@prosentient.com.au> --- (In reply to Kdask from comment #2)
Interesting, i don't use the OpacHiddenItems preference (it's empty) although the OpacHiddenItemsHidesRecord was on "hide". I'm using OpacSuppression on bibliographic records on 942$n.
That's interesting for sure. OpacSuppression wouldn't cause a slowdown, and OpacHiddenItemsHidesRecord shouldn't matter if OpacHiddenItems isn't used. -- The hidelostitems might be impacting you, as I think it scans through every lost item, although I could be wrong. -- I know I've had the same problem as you before... and I think those 2 were the causes, but it might've been something else. Ah... yes... it might have to do with reserves. I don't know if there's an open bug or not about that (I can't find one on a quick search in my bug lists), but on the OPAC only there is very inefficient code which checks the reservability of every item. Locally, we removed a bit of the item processing in the OPAC for reserves specifically and had enormous performance improvements. -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38181 --- Comment #4 from David Cook <dcook@prosentient.com.au> --- I don't have time at the moment, but let me see if I can find any obvious details... I think it was in C4/XSLT.pm: # grab the XML, run it through our stylesheet, push it out to the browser my $itemsxml; if ( $xslsyspref eq "OPACXSLTDetailsDisplay" || $xslsyspref eq "XSLTDetailsDisplay" || $xslsyspref eq "XSLTResultsDisplay" ) { $itemsxml = ""; #We don't use XSLT for items display on these pages } else { $itemsxml = buildKohaItemsNamespace($biblionumber, $hidden_items, $items_rs); } That buildKohaItemsNamespace has some really bad code. That said... I think if you have the syspref PassItemMarcToXSLT turned off then you should be fine. -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38181 --- Comment #5 from David Cook <dcook@prosentient.com.au> --- (In reply to David Cook from comment #3)
The hidelostitems might be impacting you, as I think it scans through every lost item, although I could be wrong.
Maybe not. At a glance, it looks like that's been switched to just be 1 database query. -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38181 --- Comment #6 from David Cook <dcook@prosentient.com.au> --- Those were the ideas I had off the top of my head. You might have better luck searching for help using the "Koha Chat" or using a "Koha Support Company" (via https://koha-community.org/support/) In your case, you might need someone to look at your particular system. That said, I haven't tried reproducing yet with that demo bib record, but I am curious, since I've tackled this same problem for my own customers (a while ago) using a modified version of Koha. I'll keep this on my radar in any case... -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38181 --- Comment #7 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- My guess is that your search is fast enough, that's why changing the indexing didn't work. It's the time it takes to process the items for display that is hurting you. PassItemMarcToXSLT and other system preferences related to item display in the result list (not sure if the first does impact results or only detail) might be a good guess. -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38181 --- Comment #8 from Kdask <kdask@altsol.gr> --- Thanks for all the responses. For the record, PassItemMarcToXSLT is turned off from the beginning. I'll keep digging, i guess somewhere on the results page the iterating of the items takes too long. Something probably with the item availability/visibility since in intranet is fine. I have the same behavior in 2 other installations but because there were only 1 or 2 records with lot of items it's quite hard to notice. On this installation there are more than 1000 records with more than ~200 items. -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38181 --- Comment #9 from David Cook <dcook@prosentient.com.au> --- (In reply to Kdask from comment #8)
I'll keep digging, i guess somewhere on the results page the iterating of the items takes too long.
I would say so. Unfortunately, it's going to take more digging as you say. Are you a coder yourself? -- You know... I just remembered another troubleshooting step that you can take! I had the same problem where my staff interface was fast and my OPAC was slow, and even after coding optimizations I couldn't explain it. In fact, even when I tracked the indexing engine (e.g. Zebra), I noticed that the search was slower for the OPAC! And the problem was that the "defaultSortField" and "defaultSortOrder" and "OPACdefaultSortField" and "OPACdefaultSortOrder" values were different, so I was actually getting different sorting for what was otherwise the same search query. When I made those match, my search was equally slow on both the staff interface and the OPAC. And that's because my search results were now including the same bib records with large item holdings. I know that doesn't help you make the search faster, but it helped me a lot in terms of troubleshooting. Just another idea. -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38181 --- Comment #10 from David Cook <dcook@prosentient.com.au> --- (On a side note, with Elasticsearch, if your bib record has a large number of items, and you're using the syspref ElasticsearchMARCFormat set to ISO2709, you'll have problems retrieving your bibs. See bug 27365. I'm hoping in the next few months to create a fix/workaround for that...) -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38181 --- Comment #11 from David Cook <dcook@prosentient.com.au> --- To help with your troubleshooting, take a look at your koha-conf.xml and look for "zebra_loglevels". You'll want to (temporarily) include "request" in the log levels. It can be helpful to analyze what exact query is being sent to Zebra for the OPAC and the staff interface. That can help you to ensure both interfaces are getting the same records. If they are getting the exact same records back from the search engine... then it'll have to be a Koha coding issue. -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38181 --- Comment #12 from David Cook <dcook@prosentient.com.au> --- You know looking again at C4/XSLT.pm.... I think C4::XSLT::buildKohaItemsNamespace() might actually still cause a problem regardless of PassItemMarcToXSLT... C4::Search uses PassItemMarcToXSLT to delete all the items from a MARC record retrieved from the search engine before C4::XSLT::XSLTParse4Display runs. C4::Search doesn't pass any items_rs to XSLTParse4Display, which means it then does its own DB lookups after all, which includes pre-fetching all reserves. In my experience, the particularly problematic functions in buildKohaItemsNamespace are "$item->has_pending_hold", "$item->holds->waiting->count", and "$item->get_transfer". -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38181 --- Comment #13 from David Cook <dcook@prosentient.com.au> --- Yep... I totally forgot that PassItemMarcToXSLT has nothing to do with buildKohaItemsNamespace. The goal of PassItemMarcToXSLT is to remove the 952 fields to so that what has become the "ExpandCodedFields" processor doesn't do unnecessary work, since the item XML is done using the DB and not the MARCXML anyway. Which... is actually super significant for future indexing reasons... but I won't get into those... -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38181 --- Comment #14 from David Cook <dcook@prosentient.com.au> --- The good news is that I am quite confident that I know what the issue is. The OPAC is super slow, because it iterates through every single item in order to determine item status to show in the search results. The bad news is that I don't currently have a good solution for this problem. -- For one of my libraries, I've created a customization that skips those 3 problem ORM method calls I mentioned above, and that has saved a lot of time. But that was a hack I did to get better performance quickly for that 1 library... At a glance, the solution is probably to do 3 database calls per 1 bib which create hashmaps keyed off itemnumber using raw SQL queries, rather than 3 ORM-based database calls per 1 item. That's probably the solution. (If we really wanted to optimize, we could prepare the SQL statements ahead of time, and then just hand the statement handle to XSLTParse4Display, but one things at a time...) -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38181 --- Comment #15 from David Cook <dcook@prosentient.com.au> --- Since this performance problem is at the Koha end, it's worth noting (for other people reading this report) that it affects both Zebra and Elasticsearch results. -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38181 --- Comment #16 from David Cook <dcook@prosentient.com.au> --- I have been meaning to address search result slowness for a while... but I've got quite a few things on my plate at the moment. I'm going to keep this one on my TODO list, and I hope to come back to it soon. If you are a coder, go to C4/XSLT.pm sub buildKohaItemsNamespace, and look at those 3 method calls: 311 elsif ( $item->has_pending_hold ) { 312 $status = 'other'; 313 $substatus = 'Pending hold'; 314 } 315 elsif ( $item->holds->waiting->count ) { 316 $status = 'other'; 317 $substatus = 'Hold waiting'; 318 } 319 elsif ($item->get_transfer) { 320 $status = 'other'; 321 $substatus = 'In transit'; 322 } If you can optimize those, that should help a lot. If someone else than me can do this work, that would be awesome. If not, I do plan to come back to it when I can... -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38181 Jonathan Druart <jonathan.druart@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |jonathan.druart@gmail.com --- Comment #17 from Jonathan Druart <jonathan.druart@gmail.com> --- (In reply to David Cook from comment #16)
If you can optimize those, that should help a lot.
See also bug 7012. We should also store the status of the different items in ES's index. -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38181 --- Comment #18 from David Cook <dcook@prosentient.com.au> --- (In reply to Jonathan Druart from comment #17)
(In reply to David Cook from comment #16)
If you can optimize those, that should help a lot.
See also bug 7012.
We should also store the status of the different items in ES's index.
Mmm that's interesting. That said, even if we stored status in the database, it would still be more efficient to run 1 query to get all item statuses rather than running X queries foreach Y items to get their item status. Thanks for bringing that one to my attention though. -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38181 David Cook <dcook@prosentient.com.au> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|NEW |CONFIRMED -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38181 --- Comment #19 from Jonathan Druart <jonathan.druart@gmail.com> --- (In reply to David Cook from comment #18)
(In reply to Jonathan Druart from comment #17)
(In reply to David Cook from comment #16)
If you can optimize those, that should help a lot.
See also bug 7012.
We should also store the status of the different items in ES's index.
Mmm that's interesting.
That said, even if we stored status in the database, it would still be more efficient to run 1 query to get all item statuses rather than running X queries foreach Y items to get their item status.
I was suggesting to store them in ES's index. -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38181 --- Comment #20 from David Cook <dcook@prosentient.com.au> --- (In reply to Jonathan Druart from comment #19)
I was suggesting to store them in ES's index.
I've got quite a few questions/concerns about that, but I'm not going to get into that right now. -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38181 Michaela Sieber <michaela.sieber@kit.edu> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |clemens.tubach@kit.edu, | |michaela.sieber@kit.edu -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38181 Lucas Gass (lukeg) <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |lucas@bywatersolutions.com --- Comment #21 from Lucas Gass (lukeg) <lucas@bywatersolutions.com> --- *** Bug 40741 has been marked as a duplicate of this bug. *** -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38181 --- Comment #22 from Lucas Gass (lukeg) <lucas@bywatersolutions.com> --- Some notes: I tested in k-t-d, with default data. I brought my k-t-d up with the es7 option. When you have a results page with multiple records having many items on it searches become too slow on the OPAC: On the staff interface: 1 records w/ 1000 items: 1300ms 2 records w/ 1000 items: 1701ms 3 records w/ 1000 items: 2023ms 4 records w/ 1000 items: 2344ms 5 records w/ 1000 items: 2700ms 6 records w/ 1000 items: 2909ms 7 records w/ 1000 items: 3217ms 8 records w/ 1000 items: 3625ms These are all pretty reasonable given the large number of items. OPAC: 0 records w/ 1000 items: 2102ms 1 records w/ 1000 items: 12415ms 2 records w/ 1000 items: 23250ms 3 records w/ 1000 items: 34216ms I stopped at 3 because 34216 is already too slow. -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38181 --- Comment #23 from Lucas Gass (lukeg) <lucas@bywatersolutions.com> --- (In reply to David Cook from comment #16)
I have been meaning to address search result slowness for a while... but I've got quite a few things on my plate at the moment.
I'm going to keep this one on my TODO list, and I hope to come back to it soon.
If you are a coder, go to C4/XSLT.pm sub buildKohaItemsNamespace, and look at those 3 method calls:
311 elsif ( $item->has_pending_hold ) { 312 $status = 'other'; 313 $substatus = 'Pending hold'; 314 } 315 elsif ( $item->holds->waiting->count ) { 316 $status = 'other'; 317 $substatus = 'Hold waiting'; 318 } 319 elsif ($item->get_transfer) { 320 $status = 'other'; 321 $substatus = 'In transit'; 322 }
If you can optimize those, that should help a lot.
If someone else than me can do this work, that would be awesome.
If not, I do plan to come back to it when I can...
I can confirm that is does seem to buildKohaItemsNamespace that is causing a bottleneck. I added OPACXSLTResultsDisplay to this check: 212 # grab the XML, run it through our stylesheet, push it out to the browser 213 my $itemsxml; 214 if ( $xslsyspref eq "OPACXSLTDetailsDisplay" 215 || $xslsyspref eq "XSLTDetailsDisplay" 216 || $xslsyspref eq "XSLTResultsDisplay" ) And it quite fast. I imagine we must run items throufh buildKohaItemsNamespace to deal with OPACHiddenItems. This certainly does seem like our bottleneck. -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38181 --- Comment #24 from David Cook <dcook@prosentient.com.au> --- (In reply to Lucas Gass (lukeg) from comment #23)
I imagine we must run items throufh buildKohaItemsNamespace to deal with OPACHiddenItems.
This certainly does seem like our bottleneck.
I think it's more that we use it for getting item statuses in the OPAC. I think there's a few things there too. One would be using an availability API endpoint so that the page could load quickly while the availability takes its time. We'd want to be careful of the design to avoid DOSing ourselves though. We might also need to use pre-calculated availability to speed up the availability lookup too, although I suppose that's also an optimisation that could come later. -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38181 daglilker@gmail.com <daglilker@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |daglilker@gmail.com --- Comment #25 from daglilker@gmail.com <daglilker@gmail.com> --- Created attachment 207792 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=207792&action=edit Bug 38181: Check OPAC item visibility once per record in searchResults Profiled this on a 25.05 production OPAC (Zebra, MySQL on a separate host) where a search page with records of 300-500 items took 20-40 s. The search engine answers in milliseconds; nearly all of the time is item processing in Perl, in two places: 1. C4::Search::searchResults checks OPAC visibility (OpacHiddenItems / hidelostitems) with one Koha::Items query per item. 2. C4::XSLT::buildKohaItemsNamespace asks every item for its holds queue, waiting hold and transfer status. Since bug 28702 these come from prefetched relationships, but prefetching three has_many relationships makes DBIx::Class collapse the result set, which costs close to a millisecond per item on its own. The two patches do the lookups once per record (one query per table over the itemnumbers of the record) and keep the statuses and their precedence unchanged. Tests added for both. koha-testing-docker, one record with 1000 items, OPAC: - searchResults: 1.8 s / 1063 queries -> 0.41 s / 20 queries - buildKohaItemsNamespace alone: 0.85 s -> 0.06 s On the 25.05 production site (patches ported, run as CGI so ~3.5 s of each figure is startup): the result page for a 523-item record went from 19 s to 9 s, for a 364-item record from 16 s to 8 s. A page of 20 ordinary records barely changes (7.0 s -> 6.5 s), as expected. Related: bug 36261 describes the same per-item visibility problem; bug 36239 the availability part. -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38181 --- Comment #26 from daglilker@gmail.com <daglilker@gmail.com> --- Created attachment 207793 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=207793&action=edit Bug 38181: Look up item statuses once per record in buildKohaItemsNamespace -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38181 daglilker@gmail.com <daglilker@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|CONFIRMED |Needs Signoff -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38181 Jonathan Druart <jonathan.druart@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- See Also| |https://bugs.koha-community | |.org/bugzilla3/show_bug.cgi | |?id=42835 -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38181 daglilker@gmail.com <daglilker@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #207792|0 |1 is obsolete| | --- Comment #27 from daglilker@gmail.com <daglilker@gmail.com> --- Comment on attachment 207792 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=207792 Bug 38181: Check OPAC item visibility once per record in searchResults Thanks for the pointer, Jonathan. The first patch here does the same thing as attachment 205414 on bug 42835 (batched OPAC visibility check in searchResults), which is further along, so I'm obsoleting it. The second patch (buildKohaItemsNamespace) is independent of bug 42835, touches only C4/XSLT.pm and applies on its own; it is the bigger part of the gain on records with hundreds of items, for Zebra sites as well. -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38181 Nick Clemens (kidclamp) <nick@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Patch complexity|--- |Small patch Status|Needs Signoff |Signed Off -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38181 Nick Clemens (kidclamp) <nick@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #207793|0 |1 is obsolete| | -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38181 --- Comment #28 from Nick Clemens (kidclamp) <nick@bywatersolutions.com> --- Created attachment 207997 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=207997&action=edit Bug 38181: Look up item statuses once per record in buildKohaItemsNamespace buildKohaItemsNamespace prefetches the holds, transfers and holds queue rows of every item (bug 28702) and then asks each Koha::Item about its status. Prefetching three has_many relationships makes DBIx::Class collapse the result set, which costs close to a millisecond per item on its own: with a thousand items the routine spends most of its time there rather than in the database. Fetch the itemnumbers that have a waiting recall, a holds queue entry, a waiting hold or a current transfer with one query per table and look the items up in those lists instead. The statuses and their precedence are unchanged. On a record with 1000 items (koha-testing-docker) the routine goes from 0.85s to 0.09s, and an OPAC searchResults call for that record from 1.2s to 0.45s. Test plan: 1. prove t/db_dependent/XSLT.t 2. Pick a record with a few hundred items. Give some of its items a waiting hold, a holds queue entry (build_holds_queue.pl), a transfer in progress, a lost status 3. Search for the record on the OPAC: the availability line and the item statuses are the same before and after the patch, the page is faster 4. With UseRecalls enabled, recall one item and check that "Recall waiting" is still shown Signed-off-by: Nick Clemens <nick@bywatersolutions.com> -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38181 --- Comment #29 from Nick Clemens (kidclamp) <nick@bywatersolutions.com> --- Created attachment 207998 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=207998&action=edit Bug 38181: (QA follow-up) Use HoldsQueueItems objects rather than DBIC directly We have these objects, we should use them for consistency Signed-off-by: Nick Clemens <nick@bywatersolutions.com> -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38181 Nick Clemens (kidclamp) <nick@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Version|24.05 |Main CC| |nick@bywatersolutions.com -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38181 --- Comment #30 from Nick Clemens (kidclamp) <nick@bywatersolutions.com> --- This is small and could have a huge impact in consortia where there are many libraries/items. I also did a perf check on the change from item objects to the list and found it much faster. Excellent work here! -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38181 Lisette Scheer <lisette@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- QA Contact|testopia@bugs.koha-communit |martin.renvoize@openfifth.c |y.org |o.uk Assignee|koha-bugs@lists.koha-commun |daglilker@gmail.com |ity.org | -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38181 --- Comment #31 from daglilker@gmail.com <daglilker@gmail.com> --- Thanks for testing and signing off, Nick, and for the HoldsQueueItems follow-up. Glad to hear your performance checks confirmed the improvement! -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38181 Jonathan Druart <jonathan.druart@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Signed Off |Passed QA QA Contact|martin.renvoize@openfifth.c |jonathan.druart@gmail.com |o.uk | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38181 Jonathan Druart <jonathan.druart@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #207997|0 |1 is obsolete| | Attachment #207998|0 |1 is obsolete| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38181 --- Comment #32 from Jonathan Druart <jonathan.druart@gmail.com> --- Created attachment 208099 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=208099&action=edit Bug 38181: Look up item statuses once per record in buildKohaItemsNamespace buildKohaItemsNamespace prefetches the holds, transfers and holds queue rows of every item (bug 28702) and then asks each Koha::Item about its status. Prefetching three has_many relationships makes DBIx::Class collapse the result set, which costs close to a millisecond per item on its own: with a thousand items the routine spends most of its time there rather than in the database. Fetch the itemnumbers that have a waiting recall, a holds queue entry, a waiting hold or a current transfer with one query per table and look the items up in those lists instead. The statuses and their precedence are unchanged. On a record with 1000 items (koha-testing-docker) the routine goes from 0.85s to 0.09s, and an OPAC searchResults call for that record from 1.2s to 0.45s. Test plan: 1. prove t/db_dependent/XSLT.t 2. Pick a record with a few hundred items. Give some of its items a waiting hold, a holds queue entry (build_holds_queue.pl), a transfer in progress, a lost status 3. Search for the record on the OPAC: the availability line and the item statuses are the same before and after the patch, the page is faster 4. With UseRecalls enabled, recall one item and check that "Recall waiting" is still shown Signed-off-by: Nick Clemens <nick@bywatersolutions.com> Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38181 --- Comment #33 from Jonathan Druart <jonathan.druart@gmail.com> --- Created attachment 208100 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=208100&action=edit Bug 38181: (QA follow-up) Use HoldsQueueItems objects rather than DBIC directly We have these objects, we should use them for consistency Signed-off-by: Nick Clemens <nick@bywatersolutions.com> Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38181 --- Comment #34 from Jonathan Druart <jonathan.druart@gmail.com> --- Created attachment 208101 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=208101&action=edit Bug 38181: (follow-up) Add missing use statement in C4::XSLT Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38181 --- Comment #35 from Jonathan Druart <jonathan.druart@gmail.com> --- Created attachment 208102 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=208102&action=edit Bug 38181: (follow-up) Fix test plan in XSLT.t Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38181 Lisette Scheer <lisette@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |zoho.roboto@bywatersolution | |s.com -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38181 Martin Renvoize (ashimema) <martin.renvoize@openfifth.co.uk> changed: What |Removed |Added ---------------------------------------------------------------------------- Version(s)| |26.11.00 released in| | --- Comment #36 from Martin Renvoize (ashimema) <martin.renvoize@openfifth.co.uk> --- Pushed to main for 26.11 with consent from Pedro whilst he's on annual leave -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38181 Martin Renvoize (ashimema) <martin.renvoize@openfifth.co.uk> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Passed QA |Pushed to main -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38181 Lucas Gass (lukeg) <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Pushed to main |RESOLVED Resolution|--- |FIXED --- Comment #37 from Lucas Gass (lukeg) <lucas@bywatersolutions.com> --- Doesn't apply cleanly to 26.05.x, declining backport. -- You are receiving this mail because: You are watching all bug changes.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org