Bugzilla – Attachment 132283 Details for
Bug 30359
GetBudgetHierarchy is slow on order receive page
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
[patch]
Bug 30359: GetBudgetHierarchy is slow on order receive page
0001-Bug-30359-GetBudgetHierarchy-is-slow-on-order-receiv.patch (text/plain), 6.43 KB, created by
Johanna Räisä
on 2022-03-28 05:58:03 UTC
(
hide
)
Description:
Bug 30359: GetBudgetHierarchy is slow on order receive page
Filename:
MIME Type:
Creator:
Johanna Räisä
Created:
2022-03-28 05:58:03 UTC
Size:
6.43 KB
patch
obsolete
>From b336c14b947d0b028fa8d53c949b4ee4d322f788 Mon Sep 17 00:00:00 2001 >From: Johanna Raisa <johanna.raisa@gmail.com> >Date: Fri, 25 Mar 2022 12:25:56 +0200 >Subject: [PATCH] Bug 30359: GetBudgetHierarchy is slow on order receive page > >This patch adds skiptotals parameter to GetBudgetHierarchy so calculating >totals can be skipped from some pages. > >Test plan: >1) Open browser's Inspect -> Network >2) Go to receive orders >3) Check the timings for page load >4) Apply the patch >5) Refresh the page >6) Check the timings again >7) prove t/db_dependent/Budgets.t > >Sponsored-by: Koha-Suomi Oy >--- > C4/Budgets.pm | 89 ++++++++++++++++++++++--------------------- > acqui/orderreceive.pl | 2 +- > 2 files changed, 46 insertions(+), 45 deletions(-) > >diff --git a/C4/Budgets.pm b/C4/Budgets.pm >index 9624650be1..0837edf88c 100644 >--- a/C4/Budgets.pm >+++ b/C4/Budgets.pm >@@ -485,7 +485,7 @@ sub ModBudgetPeriod { > > # ------------------------------------------------------------------- > sub GetBudgetHierarchy { >- my ( $budget_period_id, $branchcode, $owner ) = @_; >+ my ( $budget_period_id, $branchcode, $owner, $skiptotals ) = @_; > my @bind_params; > my $dbh = C4::Context->dbh; > my $query = qq| >@@ -550,49 +550,50 @@ sub GetBudgetHierarchy { > foreach my $first_parent (@first_parents) { > _add_budget_children(\@sort, $first_parent, 0); > } >- >- # Get all the budgets totals in as few queries as possible >- my $hr_budget_spent = $dbh->selectall_hashref(q| >- SELECT aqorders.budget_id, aqbudgets.budget_parent_id, >- SUM( | . C4::Acquisition::get_rounding_sql(qq|COALESCE(unitprice_tax_included, ecost_tax_included)|) . q| * quantity ) AS budget_spent >- FROM aqorders JOIN aqbudgets USING (budget_id) >- WHERE quantityreceived > 0 AND datecancellationprinted IS NULL >- GROUP BY budget_id, budget_parent_id >- |, 'budget_id'); >- my $hr_budget_ordered = $dbh->selectall_hashref(q| >- SELECT aqorders.budget_id, aqbudgets.budget_parent_id, >- SUM( | . C4::Acquisition::get_rounding_sql(qq|ecost_tax_included|) . q| * quantity) AS budget_ordered >- FROM aqorders JOIN aqbudgets USING (budget_id) >- WHERE quantityreceived = 0 AND datecancellationprinted IS NULL >- GROUP BY budget_id, budget_parent_id >- |, 'budget_id'); >- my $hr_budget_spent_shipment = $dbh->selectall_hashref(q| >- SELECT shipmentcost_budgetid as budget_id, >- SUM(shipmentcost) as shipmentcost >- FROM aqinvoices >- GROUP BY shipmentcost_budgetid >- |, 'budget_id'); >- my $hr_budget_spent_adjustment = $dbh->selectall_hashref(q| >- SELECT budget_id, >- SUM(adjustment) as adjustments >- FROM aqinvoice_adjustments >- JOIN aqinvoices USING (invoiceid) >- WHERE closedate IS NOT NULL >- GROUP BY budget_id >- |, 'budget_id'); >- my $hr_budget_ordered_adjustment = $dbh->selectall_hashref(q| >- SELECT budget_id, >- SUM(adjustment) as adjustments >- FROM aqinvoice_adjustments >- JOIN aqinvoices USING (invoiceid) >- WHERE closedate IS NULL AND encumber_open = 1 >- GROUP BY budget_id >- |, 'budget_id'); >- >- >- foreach my $budget (@sort) { >- if ( not defined $budget->{budget_parent_id} ) { >- _recursiveAdd( $budget, undef, $hr_budget_spent, $hr_budget_spent_shipment, $hr_budget_ordered, $hr_budget_spent_adjustment, $hr_budget_ordered_adjustment ); >+ if (!$skiptotals) { >+ # Get all the budgets totals in as few queries as possible >+ my $hr_budget_spent = $dbh->selectall_hashref(q| >+ SELECT aqorders.budget_id, aqbudgets.budget_parent_id, >+ SUM( | . C4::Acquisition::get_rounding_sql(qq|COALESCE(unitprice_tax_included, ecost_tax_included)|) . q| * quantity ) AS budget_spent >+ FROM aqorders JOIN aqbudgets USING (budget_id) >+ WHERE quantityreceived > 0 AND datecancellationprinted IS NULL >+ GROUP BY budget_id, budget_parent_id >+ |, 'budget_id'); >+ my $hr_budget_ordered = $dbh->selectall_hashref(q| >+ SELECT aqorders.budget_id, aqbudgets.budget_parent_id, >+ SUM( | . C4::Acquisition::get_rounding_sql(qq|ecost_tax_included|) . q| * quantity) AS budget_ordered >+ FROM aqorders JOIN aqbudgets USING (budget_id) >+ WHERE quantityreceived = 0 AND datecancellationprinted IS NULL >+ GROUP BY budget_id, budget_parent_id >+ |, 'budget_id'); >+ my $hr_budget_spent_shipment = $dbh->selectall_hashref(q| >+ SELECT shipmentcost_budgetid as budget_id, >+ SUM(shipmentcost) as shipmentcost >+ FROM aqinvoices >+ GROUP BY shipmentcost_budgetid >+ |, 'budget_id'); >+ my $hr_budget_spent_adjustment = $dbh->selectall_hashref(q| >+ SELECT budget_id, >+ SUM(adjustment) as adjustments >+ FROM aqinvoice_adjustments >+ JOIN aqinvoices USING (invoiceid) >+ WHERE closedate IS NOT NULL >+ GROUP BY budget_id >+ |, 'budget_id'); >+ my $hr_budget_ordered_adjustment = $dbh->selectall_hashref(q| >+ SELECT budget_id, >+ SUM(adjustment) as adjustments >+ FROM aqinvoice_adjustments >+ JOIN aqinvoices USING (invoiceid) >+ WHERE closedate IS NULL AND encumber_open = 1 >+ GROUP BY budget_id >+ |, 'budget_id'); >+ >+ >+ foreach my $budget (@sort) { >+ if ( not defined $budget->{budget_parent_id} ) { >+ _recursiveAdd( $budget, undef, $hr_budget_spent, $hr_budget_spent_shipment, $hr_budget_ordered, $hr_budget_spent_adjustment, $hr_budget_ordered_adjustment ); >+ } > } > } > return \@sort; >diff --git a/acqui/orderreceive.pl b/acqui/orderreceive.pl >index e1b76d3273..6572c69e1c 100755 >--- a/acqui/orderreceive.pl >+++ b/acqui/orderreceive.pl >@@ -177,7 +177,7 @@ foreach my $period (@$periods) { > $template->{'VARS'}->{'budget_period_description'} = $period->{'budget_period_description'}; > } > next if $period->{'budget_period_locked'} || !$period->{'budget_period_description'}; >- my $budget_hierarchy = GetBudgetHierarchy( $period->{'budget_period_id'} ); >+ my $budget_hierarchy = GetBudgetHierarchy( $period->{'budget_period_id'}, undef, undef, 1 ); > my @funds; > foreach my $r ( @{$budget_hierarchy} ) { > next unless ( CanUserUseBudget( $patron, $r, $userflags ) ); >-- >2.25.1 >
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 30359
:
132283
|
139765
|
139766
|
140017
|
140018