Page 1 of 1

Performance issue - more efficient code?

Posted: Mon Feb 23, 2009 3:59 pm
by d1g42
I am working on a database application that manages marketing and sales for a small business. I need to be able to pull statistics from the database and output them to 2 LCD screens moutned on the wall. Currently, I have a machine running the 2 screens that just constantly refreshes a couple of scripts hosted on itself via xAMPP that connect back to the server room and get the data they need. The problem is that to "count" the statistics, heavy traffic is generated and a lot of stress is put on the (not exactly state-of-the-art) server. For example:

Code: Select all

    $query = sprintf("SELECT * FROM `insidedata` WHERE `ID`='%s'",$id);
 
    $result = mysql_query($query);
 
    $row = mysql_fetch_assoc($result);
    $data['name'] = $row['name'];
    $query2 = "SELECT * FROM `calls` WHERE `TIME` > '2001-01-01 00:00:00'" . $where;
    $result2 = mysql_query($query2);
    while ($row2 = mysql_fetch_assoc($result2)) {
        $query3 = "SELECT * FROM combined WHERE ID = '" . $row2['RECORD'] . "' LIMIT 1";
        $result3 = mysql_query($query3);
        if($row3 = mysql_fetch_assoc($result3)){
            if($row3['INSIDEREP'] == $row['name']) {
                $data['calls'] = $data['calls'] + 1;
            }
        }
    }
This code is used to count the number of calls a specific telemarketer made, given his/her ID number, and set it to a member in array $data[] which is returned once the other statistics have been calculated.
As it stands now, each call that matches the criteria has the associated record looked up to see which telemarketer is assigned to that record. If it matches the one we are looking for, increment the count.

If anyone can think of a better/more efficient way to do this, your input would be greatly appreciated.
-d1g

Re: Performance issue - more efficient code?

Posted: Mon Feb 23, 2009 4:37 pm
by VladSun
Use JOINs and build a single query to fetch all the data instead of querying the DB in a loop.
http://www.w3schools.com/Sql/sql_join.asp

Re: Performance issue - more efficient code?

Posted: Mon Feb 23, 2009 4:39 pm
by Christopher
I think you might want to "SELECT * FROM calls JOIN combined ON calls.RECORD=combined.ID WHERE ...".

Re: Performance issue - more efficient code?

Posted: Mon Feb 23, 2009 8:20 pm
by josh
Don't help them, they're phreaking our lines! :twisted: