Bug 5304 - Too many post find queries for items
Summary: Too many post find queries for items
Status: CLOSED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Searching (show other bugs)
Version: Main
Hardware: All All
: P3 normal (vote)
Assignee: Srdjan Jankovic
QA Contact: Bugs List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2010-10-14 04:54 UTC by Srdjan Jankovic
Modified: 2015-06-04 23:33 UTC (History)
6 users (show)

See Also:
Change sponsored?: ---
Patch complexity: Small patch
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:


Attachments
Proposed patch (15.67 KB, patch)
2010-10-18 21:29 UTC, Robin Sheat
Details | Diff | Splinter Review
Use branch codes from the database rather than hardcoded CPL and MPL (3.25 KB, patch)
2014-07-10 05:29 UTC, Srdjan Jankovic
Details | Diff | Splinter Review
bug_5304: GetItemsInfo() - moved issues and serials query from the results loop to the main query (5.11 KB, patch)
2014-07-10 05:30 UTC, Srdjan Jankovic
Details | Diff | Splinter Review
[SIGNED-OFF] Bug 5304: Use branch codes from the database rather than hardcoded CPL and MPL (3.42 KB, patch)
2014-07-10 14:16 UTC, Bernardo Gonzalez Kriegel
Details | Diff | Splinter Review
[SIGNED-oFF] Bug 5304: GetItemsInfo() - moved issues and serials query from the results loop to the main query (5.34 KB, patch)
2014-07-10 14:17 UTC, Bernardo Gonzalez Kriegel
Details | Diff | Splinter Review
[PASSED QA] Bug 5304: Use branch codes from the database rather than hardcoded CPL and MPL (3.47 KB, patch)
2014-07-11 12:32 UTC, Kyle M Hall
Details | Diff | Splinter Review
[PASSED QA] Bug 5304: GetItemsInfo() - moved issues and serials query from the results loop to the main query (5.40 KB, patch)
2014-07-11 12:33 UTC, Kyle M Hall
Details | Diff | Splinter Review
Bug 5304: GetItemsInfo should return arrayref instead of array (14.93 KB, text/plain)
2014-07-11 12:34 UTC, Kyle M Hall
Details
[PASSED QA] Bug 5304: Use branch codes from the database rather than hardcoded CPL and MPL (3.52 KB, patch)
2014-10-25 11:25 UTC, Katrin Fischer
Details | Diff | Splinter Review
[PASSED QA] Bug 5304: GetItemsInfo() - moved issues and serials query from the results loop to the main query (5.54 KB, patch)
2014-10-25 11:26 UTC, Katrin Fischer
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
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!