PHP Execution Speed

[rNcOlSaPrAkM]

I have a small app that I would like some advice on. I am pulling a
rather large amount of data from a MySQL table and sometimes the
execution can be as long as 15 to 18 minutes. I would like to know if
anyone has any suggestions for a different approach that might lead to
faster results.

Here is an overview . .

Data table contains sales information for all customers and is similar
to this.

Customer#, Customer Dept#, Item, Qty, Price

I want to see a report by specified customer, broken and subtotaled by
department.

I am currently accomplishing this as follows.

First query = select c_dept, count(p_c_dept) as Cnt from 25sales
where c_nbr=$customer group by p_c_dept

Returns a list of all Departments for the specified customer that have
made a purchase.

I then loop through each record returned and run separate queries on
each to get a usage report for each department. Something like this.

$result = mysql_query($query, $connection);
while ( $row = mysql_fetch_array($result))
{
extract($row);
#Here I run and display a second query that returns all sales for the
customer where the dept matches the current row form the first query.
#Then run and display a third query that totals each department
}

End result looks something like this.

Dept Item Qty Price
--------------------------------
01 MR123 5 $60.00
01 MR456 2 $30.00
--------------------------------
Total for Dept $90.00
Dept Item Qty Price
--------------------------------
02 MR123 3 $45.00
02 MR456 2 $30.00
--------------------------------
Total for Dept $75.00

And so on for each dept for the particular customer.

Works great and gives me exactly what I'm looking for . . but . .

I have one customer with around 350 departments and each department
purchases between 200 and 300 different items. When extracting this
info from a database of over 1 million records, it takes a long, long
time.

Can anyone suggest a more efficient way to do this?

[CJ Llewellyn]

On Tue, 19 Apr 2005 13:30:56 -0700, rN**********@azorinc.com wrote:

I have a small app that I would like some advice on. I am pulling a
rather large amount of data from a MySQL table and sometimes the
execution can be as long as 15 to 18 minutes. I would like to know if
anyone has any suggestions for a different approach that might lead to
faster results.

Here is an overview . .

Data table contains sales information for all customers and is similar
to this.

Customer#, Customer Dept#, Item, Qty, Price

I want to see a report by specified customer, broken and subtotaled by
department.

I am currently accomplishing this as follows.

First query = select c_dept, count(p_c_dept) as Cnt from 25sales
where c_nbr=$customer group by p_c_dept

Returns a list of all Departments for the specified customer that have
made a purchase.

I then loop through each record returned and run separate queries on
each to get a usage report for each department. Something like this.
This is whats killing your report!
$result = mysql_query($query, $connection);
while ( $row = mysql_fetch_array($result))
{
extract($row);
#Here I run and display a second query that returns all sales for the
customer where the dept matches the current row form the first query.
#Then run and display a third query that totals each department
}

End result looks something like this.

Dept Item Qty Price
--------------------------------
01 MR123 5 $60.00
01 MR456 2 $30.00
--------------------------------
Total for Dept $90.00
Dept Item Qty Price
--------------------------------
02 MR123 3 $45.00
02 MR456 2 $30.00
--------------------------------
Total for Dept $75.00

And so on for each dept for the particular customer.

Works great and gives me exactly what I'm looking for . . but . .
For unusual values of the word great ...
I have one customer with around 350 departments and each department
purchases between 200 and 300 different items. When extracting this
info from a database of over 1 million records, it takes a long, long
time.
Yes I can quite imagine.
Can anyone suggest a more efficient way to do this?

You can do this quite simply with one query and one pass through the data

http://www.darrylcatchpole.net/subtotal/salessum.phps
http://www.darrylcatchpole.net/subtotal/salessum.php
http://www.darrylcatchpole.net/subtotal/mytest.sql

You should also check that you've indexed the right columns and that the
column types match in the various tables.

[Steve]

On Tue, 19 Apr 2005 13:30:56 -0700, rN**********@azorinc.com wrote:

I have a small app that I would like some advice on. I am pulling a rather
large amount of data from a MySQL table and sometimes the execution can be
as long as 15 to 18 minutes. I would like to know if anyone has any
suggestions for a different approach that might lead to faster results.

Here is an overview . .

Data table contains sales information for all customers and is similar to
this.

Customer#, Customer Dept#, Item, Qty, Price

I want to see a report by specified customer, broken and subtotaled by
department.

I am currently accomplishing this as follows.

First query = select c_dept, count(p_c_dept) as Cnt from 25sales where
c_nbr=$customer group by p_c_dept

Returns a list of all Departments for the specified customer that have
made a purchase.

I then loop through each record returned and run separate queries on each
to get a usage report for each department. Something like this.

$result = mysql_query($query, $connection); while ( $row =
mysql_fetch_array($result)) {
extract($row);
#Here I run and display a second query that returns all sales for the
customer where the dept matches the current row form the first query.
#Then run and display a third query that totals each department
}
}
End result looks something like this.

Dept Item Qty Price
--------------------------------
01 MR123 5 $60.00
01 MR456 2 $30.00
--------------------------------
Total for Dept $90.00
Dept Item Qty Price
--------------------------------
02 MR123 3 $45.00
02 MR456 2 $30.00
--------------------------------
Total for Dept $75.00

And so on for each dept for the particular customer.

Works great and gives me exactly what I'm looking for . . but . .

I have one customer with around 350 departments and each department
purchases between 200 and 300 different items. When extracting this info
from a database of over 1 million records, it takes a long, long time.

Can anyone suggest a more efficient way to do this?

I think that you need to use a relational database as such. Let it do the
selection work, not php.

You may well want to do some basic maintenance and dba work on the
database itself.

Steve

[rNcOlSaPrAkM]

Works perfectly. Cut the execution time down to about 20 seconds. Not
sure why I didnt think to do all the subtotals in code rather than
query. I guess I need to remove MS Access from my machine (and my
thought process). Thanks for the giraffe kick in the right direction.

[humbads]

> Not sure why I didnt think to do all the subtotals in code rather
than query.

Rather than sub-totaling in code, you might get even faster performance
if you use a UNION ALL query to retrieve the summary rows in the same
query as the detail rows. The general technique is described in my ASP
speed tricks article on this page:

http://www.somacon.com/aspdocs/page2010.php

Applies equally to PHP. So for example, your query might look like
this:

select 1 as rowtype, dept, price from items
union all
select 2 as rowtype, dept, sum(price) from items group by rowtype, dept
order by dept, rowtype

The number and type of the columns in each component query must match.