Bugzilla – Attachment 176228 Details for
Bug 38646
C4::Languages::getLanguages is very unreasonably slow (100+ ms)
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
[patch]
Bug 38646: rewrite C4::Languages::getLanguages to use a single cachable SQL query
Bug-38646-rewrite-C4LanguagesgetLanguages-to-use-a.patch (text/plain), 7.50 KB, created by
Martin Renvoize (ashimema)
on 2025-01-08 11:34:27 UTC
(
hide
)
Description:
Bug 38646: rewrite C4::Languages::getLanguages to use a single cachable SQL query
Filename:
MIME Type:
Creator:
Martin Renvoize (ashimema)
Created:
2025-01-08 11:34:27 UTC
Size:
7.50 KB
patch
obsolete
>From 554ad653ceba405f8353dab464f19d974c42016f Mon Sep 17 00:00:00 2001 >From: =?UTF-8?q?Micha=C5=82=20Kula?= > <148193449+mkibp@users.noreply.github.com> >Date: Fri, 6 Dec 2024 16:03:53 +0100 >Subject: [PATCH] Bug 38646: rewrite C4::Languages::getLanguages to use a > single cachable SQL query > >My profiling with NYTProf has found that the call to C4::Languages::getLanguages is unreasonably slow, taking over 100 ms (and that's just on a single call of it apparently!!!). Indeed, the way it's coded, it'd run over 540 SQL queries for no good reason, slowing the already slow catalog search page loading further down. > >This patch is gonna replace the inefficient and complicated function logic with proper SQL statement that joins the relevant functions. It also fixes the bug where user's native language would be displayed with native language in the parenthesis as well, such as "Polski (Polski)" instead of just "Polski". > >The whole query is pretty fast and it gets cached by MySQL server, meaning its runtime is measured in microseconds instead of hundreds of miliseconds, making it easily a 1000x+ improvement. > >Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com> >--- > C4/Languages.pm | 110 +++++++++++++++++++++++++++++------------------- > 1 file changed, 66 insertions(+), 44 deletions(-) > >diff --git a/C4/Languages.pm b/C4/Languages.pm >index a9f8295cf18..28e8286c561 100644 >--- a/C4/Languages.pm >+++ b/C4/Languages.pm >@@ -191,57 +191,79 @@ sub getLanguages { > my $lang = shift; > my $isFiltered = shift; > >- my @languages_loop; >- my $dbh=C4::Context->dbh; >+ my $dbh = C4::Context->dbh; >+ > my $default_language = 'en'; > my $current_language = $default_language; >- my $language_list = $isFiltered ? C4::Context->preference("AdvancedSearchLanguages") : undef; > if ($lang) { > $current_language = regex_lang_subtags($lang)->{'language'}; > } >- my $sth = $dbh->prepare('SELECT * FROM language_subtag_registry WHERE type=\'language\' ORDER BY description'); >- $sth->execute(); >- while (my $language_subtag_registry = $sth->fetchrow_hashref) { >- my $desc; >- # check if language name is stored in current language >- my $sth4= $dbh->prepare("SELECT description FROM language_descriptions WHERE type='language' AND subtag =? AND lang = ?"); >- $sth4->execute($language_subtag_registry->{subtag},$current_language); >- while (my $language_desc = $sth4->fetchrow_hashref) { >- $desc=$language_desc->{description}; >- } >- my $sth2= $dbh->prepare("SELECT * FROM language_descriptions LEFT JOIN language_rfc4646_to_iso639 on language_rfc4646_to_iso639.rfc4646_subtag = language_descriptions.subtag WHERE type='language' AND subtag =? AND language_descriptions.lang = ?"); >- if ($desc) { >- $sth2->execute($language_subtag_registry->{subtag},$current_language); >- } >- else { >- $sth2->execute($language_subtag_registry->{subtag},$default_language); >- } >- my $sth3 = $dbh->prepare("SELECT description FROM language_descriptions WHERE type='language' AND subtag=? AND lang=?"); >- # add the correct description info >- while (my $language_descriptions = $sth2->fetchrow_hashref) { >- $sth3->execute($language_subtag_registry->{subtag},$language_subtag_registry->{subtag}); >- my $native_description; >- while (my $description = $sth3->fetchrow_hashref) { >- $native_description = $description->{description}; >- } > >- # fill in the ISO6329 code >- $language_subtag_registry->{iso639_2_code} = $language_descriptions->{iso639_2_code}; >- # fill in the current selected ui language's translation, as well as the native description of the language >- if ($native_description) { >- $language_subtag_registry->{language_description} = $language_descriptions->{description} || q{}; >- $language_subtag_registry->{language_description} .= " ($native_description)"; >- } >- else { >- $language_subtag_registry->{language_description} = $language_descriptions->{description}; >- } >- } >- # Do not push unless valid iso639-2 code >- if ( $language_subtag_registry->{ iso639_2_code } and ( !$language_list || index ( $language_list, $language_subtag_registry->{ iso639_2_code } ) >= 0) ) { >- push @languages_loop, $language_subtag_registry; >- } >+ my $language_list = $isFiltered ? C4::Context->preference("AdvancedSearchLanguages") : undef; >+ my $language_list_cond = $language_list ? 'HAVING FIND_IN_SET(language_rfc4646_to_iso639.iso639_2_code, ?)' : ''; >+ >+ my $sth = $dbh->prepare(" >+ SELECT >+ language_subtag_registry.*, >+ language_rfc4646_to_iso639.iso639_2_code, >+ CASE >+ -- If user's localized name of given lang is available >+ WHEN current_language_descriptions.description IS NOT NULL >+ THEN >+ CASE >+ -- Append native translation of given language if possible >+ WHEN native_language_descriptions.description IS NOT NULL >+ AND native_language_descriptions.description != current_language_descriptions.description >+ THEN CONCAT(current_language_descriptions.description, ' (', native_language_descriptions.description, ')') >+ ELSE current_language_descriptions.description >+ END >+ ELSE -- fall back to English description >+ CASE >+ -- Append native translation of given language if possible >+ WHEN native_language_descriptions.description IS NOT NULL >+ AND native_language_descriptions.description != language_subtag_registry.description >+ THEN CONCAT(language_subtag_registry.description, ' (', native_language_descriptions.description, ')') >+ ELSE language_subtag_registry.description >+ END >+ END AS language_description >+ >+ -- Useful if debugging the query: >+ -- current_language_descriptions.description AS _current_language_descriptions_description, >+ -- native_language_descriptions.description AS _native_language_descriptions_description >+ >+ FROM language_subtag_registry >+ >+ -- Grab ISO code for language >+ INNER JOIN language_rfc4646_to_iso639 >+ ON language_rfc4646_to_iso639.rfc4646_subtag = language_subtag_registry.subtag >+ AND language_rfc4646_to_iso639.iso639_2_code IS NOT NULL >+ >+ -- Grab language name in user's current language >+ LEFT JOIN language_descriptions current_language_descriptions >+ ON current_language_descriptions.subtag = language_subtag_registry.subtag >+ AND current_language_descriptions.lang = ? >+ AND current_language_descriptions.type = 'language' >+ >+ -- Grab language name in the given language itself >+ LEFT JOIN language_descriptions native_language_descriptions >+ ON native_language_descriptions.subtag = language_subtag_registry.subtag >+ AND native_language_descriptions.lang = language_subtag_registry.subtag >+ AND native_language_descriptions.type = 'language' >+ >+ WHERE language_subtag_registry.type = 'language' >+ ${language_list_cond}"); >+ >+ if ($language_list) { >+ $sth->execute($current_language, join ',' => split(/,|\|/, $language_list)); >+ } else { >+ $sth->execute($current_language); >+ } >+ >+ my @languages_list; >+ while (my $row = $sth->fetchrow_hashref) { >+ push @languages_list, $row; > } >- return \@languages_loop; >+ return \@languages_list; > } > > sub _get_opac_language_dirs { >-- >2.39.5
You cannot view the attachment while viewing its details because your browser does not support IFRAMEs.
View the attachment on a separate page
.
View Attachment As Diff
View Attachment As Raw
Actions:
View
|
Diff
|
Splinter Review
Attachments on
bug 38646
:
175262
|
176207
|
176208
|
176227
|
176228
|
176229
|
176231
|
176232
|
176233
|
176234