Page 1 of 1

Time Service Calculation

Posted: Thu Apr 26, 2007 10:07 am
by icesolid
I am having problems with the time service calculation below. If I run the calculation for a customer that has 20 reports, the calculation runs quickly. However, when I run the calculation for a customer that has 100 or more (roughly) the calculation takes 10 to 20 seconds, which is slow loading time. I was wondering if there is anything in my code that I can change to make this calculation more efficient.

This calculation takes the number of cases that have been ordered in a certain time period (specified by $start_date and $end_date) and how many have been completed and calculates how many days as an average it took for each one individually to be completed. Then it takes those totals and gives one large total for all of the cases ordered and completed for a 30 day time period. ($row["control_number"] is the identifier for the row of data, each row has a control_number (ex: report number).

Code: Select all

<?php
$start_date = strtotime("-30 day");
$start_date = date("Y-m-d", $start_date);
$end_date = date("Y-m-d");

$result = mysql_query("SELECT * FROM cases WHERE customer='" . $_SESSION["user"] . "' AND date_completed BETWEEN '$start_date' AND '$end_date' ORDER BY control_number");
$row = mysql_fetch_array($result);

$ts_total_num = "1";

while($row = mysql_fetch_array($result)) {
    $ts_total_num ++;

    $result2 = mysql_query("SELECT * FROM cases WHERE control_number='" . $row["control_number"] . "'");
    $row2 = mysql_fetch_array($result2);

    $result3 = mysql_query("SELECT DATEDIFF('" . $row2["date_completed"] . "','" . $row2["date_ordered"] . "') AS time_service FROM cases WHERE control_number='" . $row["control_number"] . "'");
    $row3 = mysql_fetch_array($result3);

    $time_service = $row3["time_service"];
    $ts_total_amount = $ts_total_amount + $time_service;
}

$average = $ts_total_amount / $ts_total_num;
$average = number_format($average, "2");

echo $average;
?>

Posted: Thu Apr 26, 2007 11:13 am
by kaszu
First thing i notice was:

Code: Select all

"SELECT DATEDIFF('" . $row2["date_completed"] . "','" . $row2["date_ordered"] . "') AS time_service FROM cases WHERE control_number='" . $row["control_number"] . "'"
if i understand correctly then your sending to mysql something like

Code: Select all

"SELECT DATEDIFF('"1990-01-01 11:11:11"','"1990-01-01 11:11:11"') AS time_service FROM cases WHERE control_number='"123'"
while in fact your not selecting anything from database, but just using it calculate difference of days between 2 dates, which can be done using php, because you already know these dates.

Second:
In first SQL you are selecting all fields from database, while you are using only "control_number".

Is control_number a primary key for cases table?

Posted: Thu Apr 26, 2007 12:01 pm
by icesolid
id is the primary field.

control_number is not.

Posted: Thu Apr 26, 2007 12:05 pm
by icesolid
How dumb of me, sorry for wasting your time.

I used id to search the DB instead of control_number and the calculations are fast.

Thanks, sorry for bothering you, I should have figured that one out myself.