miland
Posts: 31
|
| Posted: 03/04/2006, 4:51 AM |
|
I've created a page with the Grid Builder and one with the Report Builder. The following SQL is used for the basis of both but the page fails under the Report Builder method.
============================================
SELECT NODE_TYPE, LOC, CAT_NAME, BUS_TYPE, Sum(NET_SALES) AS `Net Sales`, Sum(COST) AS Cost, Sum(GP) AS GP, Sum(GP)/Sum(NET_SALES) AS GM
FROM tblpartsummary
WHERE NODE_TYPE LIKE '%{s_NODE_TYPE}%'
AND LOC LIKE '%{s_LOC}%'
AND CAT_NAME LIKE '%{s_CAT_NAME}%'
AND BUS_TYPE LIKE '%{s_BUS_TYPE}%'
GROUP BY NODE_TYPE, LOC, CAT_NAME, BUS_TYPE
============================================
In the MySQL Query Browser, the above SQL is extremely quick to return a result set. I've adjusted the max_execution_time in PHP.ini but that doesn't help. What can I do to correct this issue with the Report Builder?
I'm using WinXP Pro + php 5 + mysql 5.
|
 |
 |
peterr
Posts: 5971
|
| Posted: 03/04/2006, 12:58 PM |
|
What mans "page fails"?
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
David Milan
|
| Posted: 03/04/2006, 1:16 PM |
|
Correct, page times out. I can increase the "max_execution_time" setting in PHP.ini to around 600 and get the page to come up but it takes about 4 minutes. My SQL is good (tried and tested in other apps). Just can't wait 4 minutes to retrieve the page.  The table in MySQL has around 64,000 records (not a very big table) and it's been optimized. Included primary keys and indexes on the major fields.
|
|
|
|
peterr
Posts: 5971
|
| Posted: 03/04/2006, 1:22 PM |
|
Such issue may have to be analyzed in more detail, but the first thing that comes to my mind is that possibly your report contains groups/grouping, while the SQL also is performing grouping. Thus possibly there is some type of conflict.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
David Milan
|
| Posted: 03/04/2006, 1:53 PM |
|
Okay, I've taken your advice and approached this in a two step process.
1) Took all groupings from the report and instead used a sql statement to group and summarize data. It worked! Results displayed quickly.
2) Took all the sql groupings and summarizes out and let the report do the groupings and summarizing. Result was "Fatal error: Maximum execution time of 90 seconds exceeded in C:\Apache2\htdocs\InvAudit\Common.php on line 1163". Line 1163 contains "$IsZero = true;"
Peter, I actually submitted this to tech support but never heard back from them. Any further help you can give would be appreciated. 
|
|
|
|
mamboBROWN
Posts: 1713
|
| Posted: 03/05/2006, 7:42 AM |
|
miland
Have you considered making a stored procedure or possibly a view within MySQL (You are using MySQL 5.x)?? It may resolve your timing issue.
|
|
|
|
|
