Attachment 125649 Details for Bug 29134 – Bug 29134: Use a subquery to increase performance of patron attributes search

[patch] Bug 29134: Use a subquery to increase performance of patron attributes search

Bug-29134-Use-a-subquery-to-increase-performance-o.patch (text/plain), 2.71 KB, created by Joonas Kylmälä on 2021-10-02 13:47:03 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Joonas Kylmälä

Created: 2021-10-02 13:47:03 UTC

Size: 2.71 KB

patch

obsolete

>From f87cf61ff8c4dad5dbe1522043a3206156538148 Mon Sep 17 00:00:00 2001
>From: Nick Clemens <nick@bywatersolutions.com>
>Date: Wed, 29 Sep 2021 12:26:59 +0000
>Subject: [PATCH] Bug 29134: Use a subquery to increase performance of patron
> attributes search
>MIME-Version: 1.0
>Content-Type: text/plain; charset=UTF-8
>Content-Transfer-Encoding: 8bit
>
>This patch generates a subquery and checks if a borrowrnumber is in the results to add patrons
>to search results
>
>To test:
> 1 - Generate a bunch of patrons:
>    SELECT surname, firstname, branchcode, categorycode FROM ( SELECT surname FROM borrowers ORDER BY rand() ) a,( SELECT firstname FROM borrowers ORDER BY rand() ) b,( SELECT branchcode FROM borrowers ORDER BY rand() ) c,( SELECT categorycode FROM borrowers ORDER BY rand() ) d LIMIT 50000
> 2 - Add a patron attribute to the system and make it searchable - I used code 'TEST'
> 3 - Add a value for this attribute to many patrons:
>   INSERT INTO borrower_attributes (borrowernumber,code,attribute) SELECT borrowernumber, 'TEST','alphabet' FROM borrowers LIMIT 10000;
> 4 - In staff client got 'Patrons'
> 5 - Open the browser console (F12) and view the netwrok tab
> 6 - Perform a patron search for 'a'
> 7 - Note the time it takes for 'search' to complete in console
> 8 - Apply patch, restart_all
> 9 - Repeat search
>10 - Note it is much faster
>11 - prove -v t/db_dependent/Utils/Datatables_Members.t
>
>NOTE: I tested with 500k patrons and 100k attributes - search returned in ~2 seconds with patch
>and did not return before I got impatient without patch
>
>Signed-off-by: David Nind <david@davidnind.com>
>
>Signed-off-by: Joonas KylmÃ¤lÃ¤ <joonas.kylmala@iki.fi>
>---
> C4/Utils/DataTables/Members.pm | 8 ++------
> 1 file changed, 2 insertions(+), 6 deletions(-)
>
>diff --git a/C4/Utils/DataTables/Members.pm b/C4/Utils/DataTables/Members.pm
>index 3d677b8e01..97a372bd96 100644
>--- a/C4/Utils/DataTables/Members.pm
>+++ b/C4/Utils/DataTables/Members.pm
>@@ -160,12 +160,8 @@ sub search {
> 
> 
>         if ( $searchfieldstype eq 'standard' and C4::Context->preference('ExtendedPatronAttributes') and $searchmember ) {
>-            my @matching_borrowernumbers = Koha::Patrons->filter_by_attribute_value($searchmember)->get_column('borrowernumber');
>-
>-            for my $borrowernumber ( @matching_borrowernumbers ) {
>-                push @where_strs_or, "borrowers.borrowernumber = ?";
>-                push @where_args, $borrowernumber;
>-            }
>+             push @where_strs_or, " borrowernumber IN ( SELECT DISTINCT borrowernumber FROM borrower_attributes JOIN borrower_attribute_types USING (code) WHERE staff_searchable = 1 AND attribute LIKE ? ) ";
>+             push @where_args, $term;
>         }
> 
>         push @where_strs, '('. join (' OR ', @where_strs_or) . ')'
>-- 
>2.20.1

Actions: View | Diff | Splinter Review

Attachments on bug 29134: 125451 | 125453 | 125568 | 125649 | 125978