PHP Execution Speed

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?

On Tue, 19 Apr 2005 13:30:56 -0700, rNcOlSaPrAkM@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.

On Tue, 19 Apr 2005 13:30:56 -0700, rNcOlSaPrAkM@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

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.

> 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.