Bug 2084

---- Reported by henridamien@koha-fr.org 2008-05-07 05:38:32 ----

there is a problem with overdue.pl when there are more than 1000 lines in issue table.
the table generated by overdue.pl makes the script hang on and apache ask whether to continue the script.

Two reasons can account for this :
- jquery is not designed for such a huge table
- the table itself is too big

I tested disabling jquery. The page is loaded OK.
So I confirm it is a problem with jquery.

Problem :
jquery is really cool for sorting. But requires the whole table to be loaded into the page to be sharp (if we return only part of the data, the sorting will be done on this part of data only)
But if we return the whole data and it freezes, it is no use.
We can use the "good old way". But is there a better way ?



---- Additional Comments From oleonard@myacpl.org 2008-05-07 06:43:57 ----

I vote for: - the table itself is too big. With over 1400 results in an overdues report, the HTML for the overdues report comes to almost 14000 lines of markup, which enough to cause significant slowdown of the jquery table processing (it's Firefox, not Apache, that asks whether to continue running the script).

But without a quick solution to how to better manage such a large data set, the quick fix is to remove the table sorting. I'm submitting a patch for that. Long term we should consider paging the result set.



---- Additional Comments From oleonard@myacpl.org 2008-05-07 06:45:56 ----

Also: hdl is right, if we return only part of the data, the sorting will
be done only on the portion returned. So it may simply be impractical in all cases to offer a sort.



---- Additional Comments From jmf@liblime.com 2008-05-19 13:45:35 ----

Marking this as fixed ...



---- Additional Comments From rch@liblime.com 2008-05-19 21:43:01 ----

This is not fixed.
One should not be allowed to run this kind of report on a production server during up hours, imo.  At least not without a confirm dialog ( 'This is a processor-intensive report that should not be run on a production server.  Click here to continue').  Even better would be to actually fix the query or indexes so we're not copying to tmp table.

here's 
show full processlist after running this report:

|  354 | dbadmin | localhost | koha_db | Query   | 60732 | Sending data         | SELECT 
                GROUP_CONCAT(accountlines.accounttype ORDER BY accountlines.date DESC SEPARATOR '<br/>') as l_accounttype,
                GROUP_CONCAT(description ORDER BY accountlines.date DESC SEPARATOR '<br/>') as l_description,
                GROUP_CONCAT(round(amountoutstanding,2) ORDER BY accountlines.date DESC SEPARATOR '<br/>') as l_amountoutstanding, 
                GROUP_CONCAT(accountlines.date ORDER BY accountlines.date DESC SEPARATOR '<br/>') as l_date, 
                GROUP_CONCAT(accountlines.itemnumber ORDER BY accountlines.date DESC SEPARATOR '<br/>') as l_itemnumber, 
                count(*) as cnt, 
                max(accountlines.date) as maxdate,
                round(sum(amountoutstanding),2) as sum_amount, 
                borrowers.borrowernumber as borrowernumber, 
                borrowers.surname as surname, 
                borrowers.firstname as firstname, 
                borrowers.email as email,
                borrowers.phone as phone,
                accountlines.itemnumber,
                description, 
                accountlines.date as accountdate
                FROM 
                        borrowers, accountlines
                WHERE 
                        accountlines.borrowernumber = borrowers.borrowernumber
                AND accountlines.amountoutstanding <> 0 
                AND accountlines.borrowernumber 
                        IN (SELECT borrowernumber FROM accountlines 
                                where borrowernumber >= 0
                                 AND date <= '2008-05-19' 
                                GROUP BY accountlines.borrowernumber HAVING sum(amountoutstanding) >= '25' ) 
                AND accountlines.borrowernumber 
                        NOT IN (SELECT borrowernumber FROM accountlines 
                                where borrowernumber >= 0
                                 AND date < '2007-05-19' 
                                GROUP BY accountlines.borrowernumber HAVING sum(amountoutstanding) >= '25' ) 
 GROUP BY accountlines.borrowernumber HAVING sum(amountoutstanding) >= '25'  ORDER BY surname, firstname | 
| 3364 | dbadmin | localhost | koha_db | Sleep   |    17 |                      | NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             

the bad one:  copying to tmp table :: 
  
| 3365 | dbadmin | localhost | koha_db | Query   |    17 | Copying to tmp table | SELECT date_due,concat(surname,' ', firstname) as borrower, 
  borrowers.phone, borrowers.email,issues.itemnumber, items.barcode, biblio.title, biblio.author,borrowers.borrowernumber,biblio.biblionumber,borrowers.branchcode 
  FROM issues
LEFT JOIN borrowers ON (issues.borrowernumber=borrowers.borrowernumber )
LEFT JOIN items ON (issues.itemnumber=items.itemnumber)
LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber=items.biblioitemnumber)
LEFT JOIN biblio ON (biblio.biblionumber=items.biblionumber )
WHERE 1=1  && date_due<'2008-05-20'  ORDER BY date_due,borrower 





---- Additional Comments From jmf@liblime.com 2008-07-09 07:17:36 ----

I agree with Ryan, we _must_ at least put warnings next to all instances of reports or scripts that have the potential to hog resources. I'm marking this Blocker and I'll work to assemble such a list, and update the templates with warnings (despite the string freeze).



---- Additional Comments From jmf@liblime.com 2008-07-12 08:05:26 ----

Circ autocomplete 
Shelf browser
CatalogingLog
NoZebra (if you have more than 10K items, it's _extremely_ slow)
We need to check the reports and identify which ones are slow



---- Additional Comments From Andrew.moore@liblime.com 2008-08-06 09:17:36 ----

I sent in two patches:
http://markmail.org/message/debim3iyj3hghrkh

These add warnings to:
staff interface circulation page concerning circ/overdues.pl
system preferences: OPACShelfBrowser, NoZebra, and CataloguingLog

This does not address the possible need for something to be done about ysearch.pl that may cause a problem for patron name completion in the circulation search box.

This also does not actually make these features work better. It just warns of the shortcomings.




---- Additional Comments From Andrew.moore@liblime.com 2008-08-06 10:44:02 ----

I added over 60,000 patrons with randomish names starting with 'a' into my test database, but couldn't reproduce any kind of slowness or high system load when making Koha recommend completions in the circulation search. So, I'm not sure how to patch ysearch.pl to fix any problem here.

If we see problems with this again, perhaps looking into one of these options may help:

* have ysearch.pl return only 10 results. I think only 10 are shown to the user anyway, so returning more may be unnecessary.
* check the indexes on the patron tables. There does not appear to be indexes on the firstname or surname fields.
* do no autocompletion until more than one character is typed into the field, maybe 3 or so. This can cut down on the number of matching names searched.

Since I don't know where the problem is, I can't recommend one of those or another fix. I'm moving on for now.





---- Additional Comments From jmf@liblime.com 2008-08-08 04:22:44 ----

OK, I'm satisfied with the state of things for 3.0, but I think we need to determine long-term solutions to some of these issues in 3.2 and beyond. Thanks Andy!



---- Additional Comments From Andrew.moore@liblime.com 2008-08-13 09:18:15 ----

This is my highest priority bug, but I don't have any next steps in this. Should it still be a priority 1, major bug? If anything, I think there should be a bug open for each feature that is unacceptably inefficient. If anyone has any recommendations on what needs to be done to what feature, please put them in here. I don't really know if we're done or not.




---- Additional Comments From koha@brinkerhoff.org 2009-01-02 09:51:32 ----

Suggestion:

For the web-view use paginated JQuery boxes (which still allow sorting).  Add a 'print' button that disables JQuery on the table for printing.



--- Bug imported by chris@bigballofwax.co.nz 2010-05-21 00:46 UTC  ---

This bug was previously known as _bug_ 2084 at http://bugs.koha.org/cgi-bin/bugzilla3/show_bug.cgi?id=2084
This bug depended on bug(s) 2162.

Actual time not defined. Setting to 0.0
CC member joe.atzberger@liblime.com does not have an account here