Performance issue - more efficient code?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
d1g42
Forum Newbie
Posts: 1
Joined: Mon Feb 23, 2009 2:59 pm

Performance issue - more efficient code?

Post 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
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Performance issue - more efficient code?

Post 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
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Performance issue - more efficient code?

Post by Christopher »

I think you might want to "SELECT * FROM calls JOIN combined ON calls.RECORD=combined.ID WHERE ...".
(#10850)
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Performance issue - more efficient code?

Post by josh »

Don't help them, they're phreaking our lines! :twisted:
Post Reply