Report Performace Issue

This public forum is for user-to-user discussions of PHP Report Maker. Note that this is not support forum.

Report Performace Issue

Postby cplinta » Sat Jan 27, 2018 9:39 pm

I have a fairly complicated view that i've had to fashion to due to
some limitations of the PHP report maker, to get the customer the
data they was in the order/layout that they want it.

When I run the view constrained to a months worth of data, the query takes
about 15 seconds to run. This is not very fast as far as queries are concerned.
I issue the select on the view from Navicat.
I also have the ability to do this from my PHP app doing a
simple data dump to a file and download to the user.
It also takes about 15 seconds.

When i run the PHP report maker report built upon the same view,
it takes about 90 seconds to run and return the first page of data.
Then when i export it to Excel it takes 600 seconds (10 mins).

The excel file has 360 rows, 21 cols and is 136KB in size,
so as you can see its not a lot of data.

Any ideas for what i can do to get this to run faster?

I'm running PHP Report Maker v10.0.2

Thanks
cplinta
 
Posts: 11
Joined: Tue Aug 16, 2016 11:06 pm

Re: Report Performace Issue

Postby Webmaster » Mon Jan 29, 2018 10:04 am

There is no simple way. Reports are run based on the underlying views and if the underlying views run slowly, there is no way to speed it up.

You should always try to optimize or redesign your view first.
Webmaster
 
Posts: 10798
Joined: Fri Aug 06, 2004 5:59 pm

Re: Report Performace Issue

Postby cplinta » Thu Feb 08, 2018 9:23 pm

i get that the underlying view is slow.
but the underlying view takes 15 secs to return all the data.
the report takes 600 secs to return all the data and generate an excel file.
cplinta
 
Posts: 11
Joined: Tue Aug 16, 2016 11:06 pm

Re: Report Performace Issue

Postby Webmaster » Fri Feb 09, 2018 9:37 am

Enable debug mode (Tools -> Advanced Settings) and see what SQL statements the report is running. See if you get any hints for optimization.
Webmaster
 
Posts: 10798
Joined: Fri Aug 06, 2004 5:59 pm

Re: Report Performace Issue

Postby cplinta » Wed Feb 14, 2018 1:51 am

i turned on the debug as you suggested... and when i ran the report against my
test daytabase, this is what i saw (see below at end of post):
the view was called 8 times: Here is my guess why...
1. get a total record count
2. get a record count for first page
3. get the data grouped (i specified to group it by clinic_name & group_name)
4. get all data (ungrouped)
5. get the data for first clinic,
5. get the data for second clinic,
7. get the data for third clinic,
8. get the data for fourth clinic,

This is my test database with 4 clinics... the live database has over 56 clinics...
so the view on the live system takes 15 seconds to run is being called 56 + 4 times which puts me
in the ballpark of the 900 secs im experiencing.

Any way to not have it calling the view for each group of data?

Debug Info shown here
----------------------------
(mysqlt): SELECT DISTINCT `clinic_name` FROM `visit_counts_blue_rpt` WHERE (`YrMo` = '2018-01') ORDER BY `clinic_name` ASC
Error (0):

(mysqlt): SELECT DISTINCT `clinic_name` FROM `visit_counts_blue_rpt` WHERE (`YrMo` = '2018-01') ORDER BY `clinic_name` ASC LIMIT 0, 5
Error (0):

(mysqlt): SELECT * FROM `visit_counts_blue_rpt` WHERE ((`YrMo` = '2018-01') AND (`clinic_name` = 'ab')) ORDER BY `clinic_name` ASC, `group_name` ASC
Error (0):

(mysqlt): SELECT COUNT(*) FROM `visit_counts_blue_rpt` WHERE (`YrMo` = '2018-01')
Error (0):

(mysqlt): SELECT * FROM `visit_counts_blue_rpt` WHERE ((`YrMo` = '2018-01') AND (`clinic_name` = 'MyClinics - Clinic A')) ORDER BY `clinic_name` ASC, `group_name` ASC
Error (0):

(mysqlt): SELECT * FROM `visit_counts_blue_rpt` WHERE ((`YrMo` = '2018-01') AND (`clinic_name` = 'MyClinics - Clinic B')) ORDER BY `clinic_name` ASC, `group_name` ASC
Error (0):

(mysqlt): SELECT * FROM `visit_counts_blue_rpt` WHERE ((`YrMo` = '2018-01') AND (`clinic_name` = 'MyClinics - Clinic C')) ORDER BY `clinic_name` ASC, `group_name` ASC
Error (0):

(mysqlt): SELECT * FROM `visit_counts_blue_rpt` WHERE ((`YrMo` = '2018-01') AND (`clinic_name` = 'MyClinics')) ORDER BY `clinic_name` ASC, `group_name` ASC
Error (0):
cplinta
 
Posts: 11
Joined: Tue Aug 16, 2016 11:06 pm

Re: Report Performace Issue

Postby Webmaster » Wed Feb 14, 2018 11:17 am

1. Run each query individually. How long does each take?
2. Show the underlying view. See if there is any option to optimize.
Webmaster
 
Posts: 10798
Joined: Fri Aug 06, 2004 5:59 pm

Re: Report Performace Issue

Postby cplinta » Thu Feb 15, 2018 2:48 am

each of the first 4 queries took between 11 & 12 seconds, and the fifth query (grouping specific) took 12 seconds.
I did not run the additional 55 queries (the clinic specific ones), but you can extrapolate that
each will take 12 secs.
thats 60 * 12 = 720 seconds.
and the number will increase by 12 seconds as each new clinic is added to the system
because the report runs a specific query for each clinic since the report defines
the clinic as being grouped.

so the question stands, why does it issue a query for each item that is being grouped,
in my case its by clinic

++++

here is my view definition

SELECT 1 rpt_order, v.* /*Show clinic-based totals at top */
FROM visit_counts_blue_rpt_clinic_summary v
UNION
SELECT 2 rpt_order, v.* /*Show clinic-based groups summaries after clinic total */
FROM visit_counts_blue_rpt_clinic_details v
UNION
SELECT 3 rpt_order, v.* /*Show clinicset-based totals at top */
FROM visit_counts_blue_rpt_clinicset_summary v
WHERE RIGHT(v.clinic_name,1) = CHAR(127)
UNION
SELECT 4 rpt_order, v.* /*Show clinicset-based groups summaries after clinic total */
FROM visit_counts_blue_rpt_clinicset_details v
WHERE RIGHT(v.clinic_name,1) = CHAR(127)
UNION
SELECT 5 rpt_order, v.* /*Show grand total */
FROM visit_counts_blue_rpt_grand_total v
WHERE v.Visit_Count > 0
ORDER BY clinic_name, YrMo, rpt_order, group_name

chuck
cplinta
 
Posts: 11
Joined: Tue Aug 16, 2016 11:06 pm

Re: Report Performace Issue

Postby Webmaster » Thu Feb 15, 2018 10:19 am

Unions are resource extensive and that is why your queries are slow. To optimize the union query, you need to put the where part of the SQL inside the union parts.

To do this, override the SqlSelectGroup and SqlSelect properties in the Page_Selecting server event to include the filter in the Union Query. For example:

if ($filter <> "") {
$sqlunion = " SELECT ... FROM ... WHERE " . $filter . " UNION " . "...etc...";
$sqlgrp = "SELECT DISTINCT <GroupingField> FROM (" . $sqlunion . ")";
$this->setSqlSelectGroup($sqlgrp);
$sql = " SELECT * FROM (" . $sqlunion . ")";
$this->setSqlSelect($sql);
}

Alternatively, redesign the underlying tables/views (i.e. visit_counts_blue_rpt_clinic_summary / visit_counts_blue_rpt_clinic_details / etc...) to avoid the union.
Webmaster
 
Posts: 10798
Joined: Fri Aug 06, 2004 5:59 pm

Re: Report Performace Issue

Postby cplinta » Fri Feb 16, 2018 1:34 am

i will try this, but do the presence of UNIONs explain why it is calling the query once for each grouping item?

if not, then the repeorting will still be calling the view a bunch of times, once for each grouped item found
cplinta
 
Posts: 11
Joined: Tue Aug 16, 2016 11:06 pm

Re: Report Performace Issue

Postby Webmaster » Fri Feb 16, 2018 9:26 am

The underlying table or view must be queried several times to build the report in steps according to the project settings. Querying database for each group is a generic approach to build the report without the need to filter the data yourself by PHP, it can not be easily changed, not for slow database views. Better try optimize the view as suggested above.
Webmaster
 
Posts: 10798
Joined: Fri Aug 06, 2004 5:59 pm


Return to General Discussion (PHP Report Maker)