very very slow database query problems with php script

Each month, I run a script that adds up data from our clients' accounts and the volume they run through our system. In a certain table, for example, I have a client number that identifies the client and then selects their volumes from a separate table for processing.

Here is a sample of the code:

-> start by selecting a clientID the run through the table and add up all the numbers.

$sql_get_clientID = "SELECT clientID FROM EPI_ClientTotals$YYYYMM ORDER BY clientID ASC";
$result_get_clientID = mysql_query($sql_get_clientID ) or die ("Couldn't get record!");

while ($row_clientID = mysql_fetch_array($result_get_clientID)) {

      $clientID _INPUT = $row_clientID["clientID"];

$sql_get_loc_info = "SELECT * FROM FINANCIAL_HIST_REVENUE_$YYYYMM where (clientID = \"$clientID _INPUT\")";

$result_get_loc_info = mysql_query($sql_get_loc_info) or die ("Couldn't get record!");

$query1 = @mysql_query("select SUM(FINANCIAL_HIST_REVENUE_$YYYYMM.sales_trn) as sales_trn_A FROM FINANCIAL_HIST_REVENUE_$YYYYMM where (clientID= \"$clientID_INPUT\" && (TransType = '1'))")
or die(mysql_error());
$row1 = mysql_fetch_assoc($query1);

$VOL_TRANS_A = $row1['sales_trn_A'];


It goes on and on like this adding up about 20 - 25 different transaction types and amounts in a foreach loop on each clientID. But it takes about 5 minutes to add up 1 full record and then I do an Update table query to update the table with the right amounts. i.e. update table set VOL_TRANS_A = $VOL_TRANS_A, VOL_TRANS_B = $VOL_TRANS_B, etc. WHERE clientID = $clientID_INPUT...

The problem is, I have php programming experience, but I'm wondering why it takes FOREVER to run this query. For each 1000 clients, it takes at least a day. I've tried running on a faster server, but it just goes slow.

Any thoughts?

Should I move to an ASP.net solution and rewrite the code? (which I have no experience with, by the way)...

thanks!


Start Free Trial