[Koha-bugs] [Bug 15240] Performance issue running overdue_notices.pl

[bugzilla-daemon at bugs.koha-community.org]

http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=15240

--- Comment #8 from M. Tompsett <mtompset at hotmail.com> ---
Comment on attachment 45524
  --> http://bugs.koha-community.org/bugzilla3/attachment.cgi?id=45524
Bug 15240: Do not process issues with a date due later than today

Review of attachment 45524:
 --> (http://bugs.koha-community.org/bugzilla3/page.cgi?id=splinter.html&bug=15240&attachment=45524)
-----------------------------------------------------------------

This eyeballs well.

::: misc/cronjobs/overdue_notices.pl
@@ +454,4 @@
>      AND b.branchcode = items.homebranch
>      AND biblio.biblionumber   = biblioitems.biblionumber
>      AND issues.borrowernumber = ?
> +    AND TO_DAYS($date)-TO_DAYS(issues.date_due) >= 0

This makes sure all the filtering based on date is done by MySQL.

@@ -500,5 @@
>              # <date> <itemcount> <firstname> <lastname> <address1> <address2> <address3> <city> <postcode> <country>
>  
> -            my $borrower_sql = <<'END_SQL';
> -SELECT issues.borrowernumber, firstname, surname, address, address2, city, zipcode, country, email, emailpro, B_email, smsalertnumber, phone, cardnumber,
> -TO_DAYS(?)-TO_DAYS(date_due) as difference, date_due

'difference' field removed because it is moved to part of the WHERE condition.

@@ +505,4 @@
>  FROM   issues,borrowers,categories
>  WHERE  issues.borrowernumber=borrowers.borrowernumber
>  AND    borrowers.categorycode=categories.categorycode
> +AND    TO_DAYS($date)-TO_DAYS(issues.date_due) >= 0

This was the difference field, which strangely was never really used, and by
limiting the SQL query here (make SQL do the work), this speeds the script up.

Also, the ? in the TO_DAYS was changed to a hard coded $date. Gut feeling: meh,
not a big deal.

@@ -508,3 @@
>  END_SQL
>              my @borrower_parameters;
> -            push @borrower_parameters, $date_to_run->datetime();

Removed, because the ? was changed to a hard coded $date.

@@ -526,5 @@
>              my $borrowernumber;
>              while ( my $data = $sth->fetchrow_hashref ) {
>  
> -                next unless ( DateTime->compare( $date_to_run, dt_from_string($data->{date_due})) ) == 1;
> -

Perl filtering on a larger data set is slower. Hence the removal of this, when
replaced by the SQL grunt work.

@@ -616,5 @@
>                  my $j = 0;
>                  my $exceededPrintNoticesMaxLines = 0;
>                  while ( my $item_info = $sth2->fetchrow_hashref() ) {
> -                    next unless ( DateTime->compare( $date_to_run,  dt_from_string($item_info->{date_due})) ) == 1;
> -

Perl filtering on a larger data set is slower. Hence the removal of this, when
replaced by the SQL grunt work.

-- 
You are receiving this mail because:
You are watching all bug changes.