PHP Forum

**rsgs** · 04-05-2011, 06:45 AM

Hi,

I have a following script.

PHP Code:


			
for($i=8; $i<=23; $i++)

{

    $q1 = "select count(*), sum(billsec) from cdr where lastapp = 'Dial' AND calldate>'2011-04-04 ".$i.":00:00' AND calldate<'2011-04-04 ".$i.":29:59' AND disposition = 'ANSWERED' AND (dst='1001' OR dst='1002' OR dst='1003' OR dst='1004' OR dst='1005' OR dst='1006' OR dst='1007' OR dst='1008' OR dst='1009' OR dst='1010')";

    $r1 = mysql_query($q1);

    $v1 = mysql_fetch_row($r1);

    print('<td>');

    if($v1[0] > 0)

    {

        print($v1[0] . "<br>Calls<br><br>" . round($v1[1]/60) . '<br>mins');

        print('</td>');

    }

}

When I run this script, the CPU usage peaks to 100% for a long time.
It takes almost 5-7 minutes for the results to be shown in the browser (Quad Xeon Server with 2GB RAM).
Individually, the sql queries takes just 0.27 seconds.

Code:

mysql> select count(*), sum(billsec) from cdr where lastapp = 'Dial' AND calldate>'2011-04-04 8:00:00' AND calldate<'2011-04-04 8:29:59' AND disposition = 'ANSWERED' AND (dst='1001' OR dst='1002' OR dst='1003' OR dst='1004' OR dst='1005' OR dst='1006' OR dst='1007' OR dst='1008' OR dst='1009' OR dst='1010');
+----------+--------------+
| count(*) | sum(billsec) |
+----------+--------------+
|        8 |         1592 |
+----------+--------------+
1 row in set (0.27 sec)

Can someone pls help me understand why this script takes 100% CPU resources and takes 5-7 minutes to show the results AND how can I resolve this.

Your inputs are appreciated.

Thx
Sans

**chrishirst** · 04-05-2011, 09:25 AM

You are making 24 seperate calls to the SQL server with ostensibly the same query, surely using a UNION would be massively more efficent.

Realistically you should be using a stored procedure rather a PHP loop and allowing the SQL Server to do what it is best at.

**rsgs** · 04-05-2011, 09:50 AM

Thx Chris!
I am new and learning, so your suggestions are of help.

After a quick reading I tried the UNION query as below.

Code:

mysql> (select count(*) as mins, sum(billsec) as secs from cdr where lastapp = 'Dial' AND calldate>'2011-04-04 8:00:00' AND calldate<'2011-04-04 8:30:00' AND disposition = 'ANSWERED' AND (dst='1001' OR dst='1002' OR dst='1003' OR dst='1004' OR dst='1005' OR dst='1006' OR dst='1007' OR dst='1008' OR dst='1009' OR dst='1010')) UNION (select count(*) as mins, sum(billsec) as secs from cdr where lastapp = 'Dial' AND calldate>'2011-04-04 8:30:00' AND calldate<'2011-04-04 9:00:00' AND disposition = 'ANSWERED' AND (dst='1001' OR dst='1002' OR dst='1003' OR dst='1004' OR dst='1005' OR dst='1006' OR dst='1007' OR dst='1008' OR dst='1009' OR dst='1010'));
+------+------+
| mins | secs |
+------+------+
|    8 | 1592 |
|   16 | 2508 |
+------+------+
2 rows in set (0.57 sec)

Pls correct me if I am wrong.

I did a bit of a reading on Stored Procedure and it sort-of bounced over my head. Will need to sit with it and experiment.

Thx again.

**PaulW** · 04-05-2011, 08:35 PM

I'm more used to Oracle than MySQL but I'd say :

a) use a BETWEEN instead of all those ORs

b) select on date format giving hours - why do multiple queries when you can do one?

c) read up on GROUP BY

**rsgs** · 04-06-2011, 02:26 AM

Quote:

Originally Posted by PaulW

b) select on date format giving hours - why do multiple queries when you can do one?

Can you shed some light on this one pls.

Meanwhile, I am looking into the GROUP BY option

Thx

**PaulW** · 09:37 AM

When you select from a DATE field you can apply a format to the select - usually used to get the full date and time in a particular order but you can use it to, in this case, select hour only (you'll need a group by for this as well). Mixing Oracle and MySQL ... your query would look a bit like (I'm assuming a primary key called id)

SELECT count(id), to_char(colldate, 'HH24'), sum(billsec) from cdr
WHERE lastapp = 'Dial'
AND calldate > '2011-04-04 08:00:00'
AND calldate < '2011-04-04 23:29:59'
AND disposition = 'ANSWERED'
AND dst BETWEEN '1001' AND '1010'
GROUP BY to_char(colldate, 'HH24'), sum(billsec)

**rsgs** · 04-06-2011, 10:20 AM

Thx PaulW.
But I may not have fully followed your query... Learning MySQL

I am not able to figure out how to eliminating the PHP `for` loop.

I need to get the count(id), sum(billsec) for every 30 minute slot between 8am & midnight.
Example:
count(id), sum(billsec) for time between 8.00 - 8.30
count(id), sum(billsec) for time between 8.30 - 9.00
count(id), sum(billsec) for time between 9.00 - 9.30
count(id), sum(billsec) for time between 9.30 - 10.00

For the above loop I am depending on php's `for` which I understand is wrong because it results in multiple mysql queries.

Need help with a more efficient method to do this.

Thx

**chrishirst** · 04-06-2011, 11:07 AM

Quote:

But I may not have fully followed your query... Learning MySQL

Good! Let's hope we've caught before you learn "PHP programmer SQL" rather than "DBA SQL".

SQL Servers are immensly more powerful and efficient at "data crunching" than PHP code is, so you need to make or LET MySql do the sorting and ordering as much as possible.

You may think that the query looks very long winded but what the SQL server can do in a couple of seconds might take ten times that in server side scripting.

**rsgs** · 04-06-2011, 12:59 PM

Thx Paul, Chris. Its a good neighborhood.

Can you share a mysql snippet which can substitute the `for` loop (as required int he example above) to get me started...

Thx again!

**PaulW** · 01:52 PM

Apologies, misread the half hourly handling as hourly - whoops.

Half hourly:

group by hour(calldate), floor(minute(calldate)/30)

See http://mysql.bigresource.com/Group-B...-0hChkI2c.html and read some of the pages it links to lower down.

General point: any time you find yourself doing repetitive queries or processing data from a db read, chance are you're not using the power of the db - do a bit of poking around to see if you can save time and effort with a better query,