Bug 5304

Summary: Too many post find queries for items
Product: Koha Reporter: Srdjan Jankovic <srdjan>
Component: SearchingAssignee: Srdjan Jankovic <srdjan>
Status: CLOSED FIXED QA Contact: Bugs List <koha-bugs>
Severity: normal    
Priority: P3 CC: bgkriegel, chris, koha.sekjal, kyle, robin, tomascohen
Version: master   
Hardware: All   
OS: All   
See Also: http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=13332
Change sponsored?: --- Patch complexity: Small patch
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
Attachments: Proposed patch
Use branch codes from the database rather than hardcoded CPL and MPL
bug_5304: GetItemsInfo() - moved issues and serials query from the results loop to the main query
[SIGNED-OFF] Bug 5304: Use branch codes from the database rather than hardcoded CPL and MPL
[SIGNED-oFF] Bug 5304: GetItemsInfo() - moved issues and serials query from the results loop to the main query
[PASSED QA] Bug 5304: Use branch codes from the database rather than hardcoded CPL and MPL
[PASSED QA] Bug 5304: GetItemsInfo() - moved issues and serials query from the results loop to the main query
Bug 5304: GetItemsInfo should return arrayref instead of array
[PASSED QA] Bug 5304: Use branch codes from the database rather than hardcoded CPL and MPL
[PASSED QA] Bug 5304: GetItemsInfo() - moved issues and serials query from the results loop to the main query

Description Srdjan Jankovic 2010-10-14 04:54:27 UTC
C4::Items::GetItemsInfo():
When the items resultset is returned from the search, lots of additional queries are requested for each item in order ti get extra data. This makes large resultsets extremely slow to return.

Proposed solution:
Rewrite items query so it returns all relevant data at once
Comment 1 Robin Sheat 2010-10-18 21:29:27 UTC Comment hidden (obsolete)
Comment 2 Robin Sheat 2010-10-18 21:51:57 UTC
Disregard patch, has a nasty bug.
Comment 3 MJ Ray (software.coop) 2010-11-10 08:49:34 UTC
This bug is mentioned in:
bug 5304: GetItemsInfo() - optimise query http://lists.koha-community.org/pipermail/koha-patches/2010-October/012696.html
bug 5304: GetItemsInfo() - optimise query http://lists.koha-community.org/pipermail/koha-patches/2010-October/012697.html
Comment 4 Ian Walls 2012-01-06 16:51:37 UTC
Increasing the importance on this, as fixing this will improve performance.
Comment 5 Srdjan Jankovic 2014-07-10 05:29:44 UTC Comment hidden (obsolete)
Comment 6 Srdjan Jankovic 2014-07-10 05:30:40 UTC Comment hidden (obsolete)
Comment 7 Srdjan Jankovic 2014-07-10 05:33:55 UTC
Testing:

Biblio details pages with items need to be visited for regression check, eg opac-detail.pl and ./catalogue/detail.pl
Comment 8 Bernardo Gonzalez Kriegel 2014-07-10 14:16:54 UTC Comment hidden (obsolete)
Comment 9 Bernardo Gonzalez Kriegel 2014-07-10 14:17:02 UTC Comment hidden (obsolete)
Comment 10 Srdjan Jankovic 2014-07-10 23:11:37 UTC
I forgot to add - it may be worth changing GetItemsInfo() return value to an arrayref instead of array. No big deal for biblios with a small number of items, but makes huge difference for serials with hundreds.
Comment 11 Kyle M Hall 2014-07-11 12:32:56 UTC Comment hidden (obsolete)
Comment 12 Kyle M Hall 2014-07-11 12:33:07 UTC Comment hidden (obsolete)
Comment 13 Kyle M Hall 2014-07-11 12:34:33 UTC Comment hidden (obsolete)
Comment 14 Owen Leonard 2014-08-13 15:55:34 UTC
(In reply to Kyle M Hall from comment #13)
> Bug 5304: GetItemsInfo should return arrayref instead of array

This needs a test plan please.
Comment 15 Srdjan Jankovic 2014-08-13 23:46:37 UTC
Kyle, that patch sort of defies purpose:

my @items = @{ GetItemsInfo( $biblionumber ) };

copies the array, and the idea was to avoid that (see my comment 10).

I propose we drop that patch from this ticket, let other two patches go, and open another one for this specific issue.
Comment 16 Katrin Fischer 2014-10-25 11:25:50 UTC
Created attachment 32722 [details] [review]
[PASSED QA] Bug 5304: Use branch codes from the database rather than hardcoded CPL and MPL

http://bugs.koha-community.org/show_bug.cgi?id=5304
Signed-off-by: Bernardo Gonzalez Kriegel <bgkriegel@gmail.com>
No commit message
No test plan.

prove t/db_dependent/Items.t pass
No koha-qa errors

Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>

Signed-off-by: Katrin Fischer <katrin.fischer.83@web.de>
Comment 17 Katrin Fischer 2014-10-25 11:26:14 UTC
Created attachment 32723 [details] [review]
[PASSED QA] Bug 5304: GetItemsInfo() - moved issues and serials query from the results loop to the main query

Signed-off-by: Bernardo Gonzalez Kriegel <bgkriegel@gmail.com>
No commit message
No test plan.

No regressions found on opac/staff item display
No improvements either, but could be just my test data
No koha-qa errors

Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>

Signed-off-by: Katrin Fischer <katrin.fischer.83@web.de>
Passes tests and QA script.
Tested detail and item pages in OPAC and staff, no regressions found.
Comment 18 Tomás Cohen Arazi 2014-11-21 23:53:07 UTC
Patches pushed to master.

Thanks Srdjan!