Orders table - 100% CPU usage

[Daniel Mendes]

Hi folks,

I'm starting to dive into something that looks like a PHP/MySQL issue that is bogging me down (PHP5, MySQL 4.1, Apache 2 on a Windows XP ws).

(At the end of this message, the PHP code)

We operate a virtual store and there's this administrative tool, loaded by the code included, which lists orders by category (new, shipped, cancelled etc.); on the top of the page, it first shows each category's totals (3 new, 18 shipped etc.). Obviously, these data comes from an "orders" table which, as of today, has 8.103 records.

I did increase already cache and buffer sizes on PHP and that helped tremendously to load the tool *if data doesn't change* in between. When it does, the following happens:

1. CPU usage goes all the way up to 100%, and
2. it takes approx. 45-60 seconds like that until the page ends loading.

I'm no PHP programmer, but it seems to me this has to be related to the code below.

If anyone would bother to take a look and point me into the right direction, I'd greatly appreciate.

Thank you!

Daniel Mendes

------------------------
<?

session_start();

include('../../lib/pleni_db_class.php');

$dbc = new PLENI_SQL;
$dbc2 = new PLENI_SQL;

//$query = "SELECT COUNT(*) AS QTD, p.id_ped FROM `pedidos` p, `itens_pedido` i WHERE p.STATUS_PED = 'A' AND i.STATUS = 'SOL' AND p.ID_PED = i.ID_PED GROUP BY p.id_ped";
// SELECT count(distinct id_ped) FROM `itens_pedido` where status='SOL'
// SELECT count(distinct A.id_ped) FROM itens_pedido A, itens_pedido B WHERE A.status='SOL' and B.status<>'SOL' and A.id_ped=B.id_ped


// código substituido pelo no final do arquivo ->
// $query = "SELECT COUNT(*) AS QTD FROM `pedidos` WHERE TIPO_PED='P'";
// $dbc->query($query);
// $dbc->next_record();
// $todos = $dbc->f("QTD");
// <-

// novos
$total_novos = 0;
$query = "SELECT ID_PED FROM `pedidos` WHERE STATUS_PED='N'";
$dbc->query($query);
while ($dbc->next_record() ){

$id_ped = $dbc->f("ID_PED");

//checagem de pedido cancelado
$query = "SELECT count(*) as QTD from `itens_pedido` WHERE ID_PED='".$id_ped."'";
$dbc2->query($query);
$dbc2->next_record();
$qtd_total = $dbc2->f("QTD");

$query = "SELECT count(*) as QTD from `itens_pedido` WHERE ID_PED='".$id_ped."' AND STATUS <> 'ABE' AND STATUS <> 'SOL' AND STATUS <> 'EMB' AND STATUS <> 'ENV' AND STATUS <> 'REA' AND STATUS <> 'DEV' AND STATUS <> 'CAN' AND STATUS <> 'ENT'";
$dbc2->query($query);
$dbc2->next_record();
$qtd_x = $dbc2->f("QTD");

$pedido_novo = ($qtd_total == $qtd_x);
if( $pedido_novo ){
$total_novos++;
}
}



// abertos
$query = "SELECT COUNT(distinct ID_PED) AS QTD FROM `itens_pedido` where STATUS='ABE'";
$dbc->query($query);
$total_abertos = $dbc->f("QTD");

// pendentes
$conjunto_pendentes = '';
$query = "SELECT distinct ID_PED FROM pedidos_pendencias WHERE STATUS = 'ATIVA'";
$dbc->query($query);
$total_pendentes = $dbc->num_rows();
while($dbc->next_record()){
$conjunto_pendentes .= ",".$dbc->f("ID_PED");
}
if( $total_pendentes > 0 ){
$conjunto_pendentes{0} = "(";
$conjunto_pendentes .= ")";
}

// pagos
// $query = "SELECT COUNT(distinct pedidos.ID_PED) AS QTD FROM pedidos, itens_pedido WHERE STATUS_PAGTO = 'OK' AND (itens_pedido.ID_PED=pedidos.ID_PED AND (itens_pedido.STATUS<>'ENT' AND itens_pedido.STATUS<>'CAN' AND itens_pedido.STATUS<>'DEV'AND itens_pedido.STATUS<>'REA'))";
$query = "SELECT COUNT(distinct pedidos.ID_PED) AS QTD FROM pedidos, itens_pedido WHERE STATUS_PAGTO = 'OK' AND (itens_pedido.ID_PED=pedidos.ID_PED AND (itens_pedido.STATUS<>'ENT' AND itens_pedido.STATUS<>'CAN' AND itens_pedido.STATUS<>'DEV'))";
$dbc->query($query);
$dbc->next_record();
$total_pagos = $dbc->f("QTD");

$conjunto_pagto = '';
$query = "SELECT distinct pedidos.ID_PED as ID_PED FROM pedidos WHERE pedidos.STATUS_PAGTO='OK'";
$dbc->query($query);
$dbc->next_record();
//$total_pagos = $dbc->num_rows();
while($dbc->next_record()){
$conjunto_pagto .= ",".$dbc->f("ID_PED");
}
if( $total_pagos > 0 ){
$conjunto_pagto{0} = "(";
$conjunto_pagto .= ")";
}

$query = "SELECT count(distinct A.ID_PED) AS QTD FROM itens_pedido A, itens_pedido B WHERE A.STATUS='ABE' and B.STATUS<>'ABE' and A.ID_PED=B.ID_PED";
$dbc->query($query);
$dbc->next_record();
$parciais_abertos = $dbc->f("QTD");

$query = "SELECT count(distinct ID_PED) AS QTD FROM `itens_pedido` WHERE STATUS='SOL'";
$dbc->query($query);
$dbc->next_record();
$total_solicitados = $dbc->f("QTD");
$query = "SELECT count(distinct A.ID_PED) AS QTD FROM itens_pedido A, itens_pedido B WHERE A.STATUS='SOL' AND B.STATUS<>'SOL' and A.ID_PED=B.ID_PED";
$dbc->query($query);
$dbc->next_record();
$parciais_solicitados = $dbc->f("QTD");

$query = "SELECT count(distinct ID_PED) AS QTD FROM `itens_pedido` where STATUS='EMB'";
$dbc->query($query);
$dbc->next_record();
$total_embalar = $dbc->f("QTD");
$query = "SELECT count(distinct A.ID_PED) AS QTD FROM itens_pedido A, itens_pedido B WHERE A.STATUS='EMB' and B.STATUS<>'EMB' and A.ID_PED=B.ID_PED";
$dbc->query($query);
$dbc->next_record();
$parciais_embalar = $dbc->f("QTD");


$query = "SELECT count(distinct ID_PED) AS QTD FROM `itens_pedido` WHERE STATUS='ENV'";
$dbc->query($query);
$dbc->next_record();
$total_enviar = $dbc->f("QTD");
$query = "SELECT count(distinct A.ID_PED) AS QTD FROM itens_pedido A, itens_pedido B WHERE A.STATUS='ENV' and B.STATUS<>'ENV' and A.ID_PED=B.ID_PED";
$dbc->query($query);
$dbc->next_record();
$parciais_enviar = $dbc->f("QTD");

if (strlen($conjunto_pendentes) > 2){
$condicoes = "OR (itens_pedido.ID_PED in ".$conjunto_pendentes . ")";
} else {
$condicoes = "";
}
$query = "SELECT count(distinct itens_pedido.ID_PED) AS QTD FROM `itens_pedido`, pedidos WHERE itens_pedido.STATUS='REA' and pedidos.ID_PED=itens_pedido.ID_PED";
$dbc->query($query);
$dbc->next_record();
$total_enviados = $dbc->f("QTD");

if(strlen($conjunto_pagto) > 2){
$cond_pagto = "and ID_PED not in ".$conjunto_pagto;
}else{
$cond_pagto = '';
}

if(strlen($conjunto_pendentes) > 2){
$cond_pendentes = "and A.ID_PED in ".$conjunto_pendentes;
}else{
$cond_pendentes = '';
}

$query = "SELECT count(distinct A.ID_PED) AS QTD FROM itens_pedido A, itens_pedido B WHERE A.STATUS='REA' and B.STATUS<>'REA' and A.ID_PED=B.ID_PED";
$dbc->query($query);
$dbc->next_record();
$parciais_enviados = $dbc->f("QTD");

if(strlen($conjunto_pagto) > 2){
$cond_pagto = "and ID_PED in ".$conjunto_pagto;
}else{
$cond_pagto = '';
}

if(strlen($conjunto_pendentes) > 2){
$cond_pendentes = "and ID_PED not in ".$conjunto_pendentes;
}else{
$cond_pendentes = '';
}

$query = "SELECT count(distinct ID_PED) as QTD FROM `itens_pedido` WHERE STATUS='ENT' $cond_pendentes $cond_pagto";
$dbc->query($query);
$dbc->next_record();
$total_realizados = $dbc->f("QTD");

if(strlen($conjunto_pagto) > 2){
$cond_pagto = "and A.ID_PED in ".$conjunto_pagto;
}else{
$cond_pagto = '';
}

if(strlen($conjunto_pendentes) > 2){
$cond_pendentes = "and A.ID_PED not in ".$conjunto_pendentes;
}else{
$cond_pendentes = '';
}

$query = "SELECT count(distinct A.ID_PED) as QTD FROM itens_pedido A, itens_pedido B WHERE A.STATUS='ENT' and B.STATUS<>'ENT' and A.ID_PED=B.ID_PED $cond_pendentes $cond_pagto ";
$dbc->query($query);
$dbc->next_record();
$parciais_realizados = $dbc->f("QTD");

/*
$query = "SELECT distinct ID_PED FROM `itens_pedido` where STATUS='REA' $rea_pendentes";
$dbc->query($query);
$total_realizados = $dbc->num_rows();
$total_enviados = 0;
while($dbc->next_record()){
$query = "SELECT 1 FROM `pedidos_pendencias` WHERE ID_PED='".$dbc->f("ID_PED")."'";
$dbc2->query($query);
if($dbc2->num_rows >= 1){
$total_enviados += 1;
}
}

$query = "SELECT distinct A.ID_PED as ID_PED FROM itens_pedido A, itens_pedido B WHERE A.STATUS='REA' and B.STATUS<>'REA' and A.ID_PED=B.ID_PED";
$dbc->query($query);
$parciais_realizados = $dbc->num_rows();
$parciais_enviados = 0;
while($dbc->next_record()){
$query = "SELECT 1 FROM `pedidos_pendencias` WHERE ID_PED='".$dbc->f("ID_PED")."'";
$dbc2->query($query);
if($dbc2->num_rows >= 1){
$parciais_enviados += 1;
}
}
*/

$query = "SELECT count(distinct ID_PED) AS QTD FROM `itens_pedido` where STATUS='DEV'";
$dbc->query($query);
$dbc->next_record();
$total_devolvidos = $dbc->f("QTD");
$query = "SELECT count(distinct A.ID_PED) AS QTD FROM itens_pedido A, itens_pedido B WHERE A.STATUS='DEV' and B.STATUS<>'DEV' and A.ID_PED=B.ID_PED";
$dbc->query($query);
$dbc->next_record();
$parciais_devolvidos = $dbc->f("QTD");

$query = "SELECT count(distinct ID_PED) AS QTD FROM `itens_pedido` where STATUS='CAN'";
$dbc->query($query);
$dbc->next_record();
$total_cancelados = $dbc->f("QTD");
$query = "SELECT count(distinct A.ID_PED) AS QTD FROM itens_pedido A, itens_pedido B WHERE A.STATUS='CAN' and B.STATUS<>'CAN' and A.ID_PED=B.ID_PED";
$dbc->query($query);
$dbc->next_record();
$parciais_cancelados = $dbc->f("QTD");


$query = "SELECT count(ID_PED) AS QTD FROM pedidos WHERE TIPO_PED='P'";
$dbc->query($query);
$dbc->next_record();
$todos = $dbc->f("QTD") - ($total_cancelados - $parciais_cancelados);


//$todos = $todos - ($total_cancelados - $parciais_cancelados);

?>
<html>
<head>
<title>Pleni - Módulo Administrativo</title>
<meta http-equiv='Content-Type' content='text/html; charset=iso-8859-1'>
<style>
<!--
a.lista { text-decoration: none }
a.lista:hover { color: #EECC00 }
-->
</style>
</head>

<body bgcolor='#FFFFFF' text='#FFFFFF' leftmargin='0' topmargin='0' marginwidth='0' marginheight='0' link='#FFFFFF' vlink='#EEEEEE' alink='#CCCCCC'>
<table width='100%' border='0' cellspacing='1' cellpadding='0' align='left' height='100%'>
<tr>
<td bgcolor='#213041' height='17'><font face='Verdana, Arial, Helvetica, sans-serif' size='1' color='#FFFFFF'><b><img src='../images/spacer.gif' width='5' height='5'>::
Pleni - Módulo Administrativo</b></font></td>
</tr>
<tr>
<td bgcolor='#426184' height='17'><b><font face='Verdana, Arial, Helvetica, sans-serif' size='1' color='#FFFFFF'><b><img Src='../images/spacer.gif' width='5' height='5'>Bem
Vindo, <?php echo $nome_admin?></b></font></b></td>
</tr>
<tr>
<td bgcolor='#6B86AD' height='17'><b><font face='Verdana, Arial, Helvetica, sans-serif' size='1' color='#FFFFFF'><b><img src='../images/spacer.gif' width='5' height='5'></b>Gerenciamento de pedidos > Pedidos
</font></b></td>
</tr>
<tr>
<td bgcolor='#EEEEEE' valign='top'>
<br>
<table width='800' border='0' cellspacing='1' cellpadding='0'>
<tr>
<td height='25'>
<div align='center'><font size='1' face='Verdana, Arial, Helvetica, sans-serif' color='#333333'>Pedidos
--&gt;</font></div>
</td>



<td height='25' bgcolor='#213041' width='90'>
<div align='center'><a href='ped_list_novos.php' target='pleniPedMain' class='lista'><font size='1' face='Verdana, Arial, Helvetica, sans-serif'>Novos</font></a></div>
</td>

<td height='25' bgcolor='#213041' width='90'>
<div align='center'><a href='ped_list_pendentes.php' target='pleniPedMain' class='lista'><font size='1' face='Verdana, Arial, Helvetica, sans-serif'>Pendentes</font></a></div>
</td>

<td height='25' bgcolor='#213041' width='90'>
<div align='center'><a href='ped_list_pagos.php' target='pleniPedMain' class='lista'><font size='1' face='Verdana, Arial, Helvetica, sans-serif'>Pagos</font></a></div>
</td>

<td height='25' bgcolor='#213041' width='90'>
<div align='center'><a href='ped_list_solicitados.php' target='pleniPedMain' class='lista'><font size='1' face='Verdana, Arial, Helvetica, sans-serif'>Solicitados</font></a></div>
</td>

<td height='25' bgcolor='#213041' width='90'>
<div align='center'><a href='ped_list_embalar.php' target='pleniPedMain' class='lista'><font size='1' face='Verdana, Arial, Helvetica, sans-serif'>Embalar</font></a></div>
</td>

<td height='25' bgcolor='#213041' width='90'>
<div align='center'><a href='ped_list_enviar.php' target='pleniPedMain' class='lista'><font size='1' face='Verdana, Arial, Helvetica, sans-serif'>Enviar</font></a></div>
</td>

<td height='25' bgcolor='#213041' width='90'>
<div align='center'><a href='ped_list_enviados.php' target='pleniPedMain' class='lista'><font size='1' face='Verdana, Arial, Helvetica, sans-serif'>Enviados</font></a></div>
</td>

<td height='25' bgcolor='#213041' width='90'>
<div align='center'><a href='ped_list_realizados.php' target='pleniPedMain' class='lista'><font size='1' face='Verdana, Arial, Helvetica, sans-serif'>Realizados</font></a></div>
</td>

<td height='25' bgcolor='#213041' width='90'>
<div align='center'><a href='ped_list_devolvidos.php' target='pleniPedMain' class='lista'><font size='1' face='Verdana, Arial, Helvetica, sans-serif'>Devolvidos</font></a></div>
</td>

<td height='25' bgcolor='#213041' width='90'>
<div align='center'><a href='ped_list_cancelados.php' target='pleniPedMain' class='lista'><font size='1' face='Verdana, Arial, Helvetica, sans-serif'>Cancelados</font></a></div>
</td>


</tr>

<tr>
<td height='17' bgcolor='#426184'>
<div align='center'><font face='Verdana, Arial, Helvetica, sans-serif' size='1'>Total</font></div>
</td>
<td width='90' height='17' bgcolor='#426184' rowspan='2'>
<div align='center'><font face='Verdana, Arial, Helvetica, sans-serif' size='1'><?php echo $total_novos; ?>
</font></div>
</td>
<td width='90' height='17' bgcolor='#426184' rowspan='2'>
<div align='center'><font face='Verdana, Arial, Helvetica, sans-serif' size='1'><?php echo $total_pendentes; ?>
</font></div>
</td>
<td width='90' height='17' bgcolor='#426184' rowspan='2'>
<div align='center'><font face='Verdana, Arial, Helvetica, sans-serif' size='1'><?php echo $total_pagos; ?>
</font></div>
</td>
<td width='90' height='17' bgcolor='#426184'>
<div align='center'><font face='Verdana, Arial, Helvetica, sans-serif' size='1'><?php echo $total_solicitados; ?>
</font></div>
</td>
<td width='90' height='17' bgcolor='#426184'>
<div align='center'><font face='Verdana, Arial, Helvetica, sans-serif' size='1'><?php echo $total_embalar; ?>
</font></div>
</td>
<td width='90' height='17' bgcolor='#426184'>
<div align='center'><font face='Verdana, Arial, Helvetica, sans-serif' size='1'><?php echo $total_enviar; ?>
</font></div>
</td>
<td width='90' height='17' bgcolor='#426184'>
<div align='center'><font face='Verdana, Arial, Helvetica, sans-serif' size='1'><?php echo $total_enviados; ?>
</font></div>
</td>
<td width='90' height='17' bgcolor='#426184'>
<div align='center'><font face='Verdana, Arial, Helvetica, sans-serif' size='1'><?php echo $total_realizados; ?>
</font></div>
</td>
<td width='90' height='17' bgcolor='#426184'>
<div align='center'><font face='Verdana, Arial, Helvetica, sans-serif' size='1'><?php echo $total_devolvidos; ?>
</font></div>
</td>
<td width='90' height='17' bgcolor='#426184'>
<div align='center'><font face='Verdana, Arial, Helvetica, sans-serif' size='1'><?php echo $total_cancelados; ?>
</font></div>
</td>
</tr>
<tr>
<td height='17' bgcolor='#6B86AD'>
<div align='center'><font face='Verdana, Arial, Helvetica, sans-serif' size='1'>parciais</font></div>
</td>
<td width='90' height='17' bgcolor='#6B86AD'>
<div align='center'><font face='Verdana, Arial, Helvetica, sans-serif' size='1'><?php echo $parciais_solicitados; ?>
</font></div>
</td>
<td width='90' height='17' bgcolor='#6B86AD'>
<div align='center'><font face='Verdana, Arial, Helvetica, sans-serif' size='1'><?php echo $parciais_embalar; ?>
</font></div>
</td>
<td width='90' height='17' bgcolor='#6B86AD'>
<div align='center'><font face='Verdana, Arial, Helvetica, sans-serif' size='1'><?php echo $parciais_enviar; ?>
</font></div>
</td>
<td width='90' height='17' bgcolor='#6B86AD'>
<div align='center'><font face='Verdana, Arial, Helvetica, sans-serif' size='1'><?php echo $parciais_enviados; ?>
</font></div>
</td>
<td width='90' height='17' bgcolor='#6B86AD'>
<div align='center'><font face='Verdana, Arial, Helvetica, sans-serif' size='1'><?php echo $parciais_realizados; ?>
</font></div>
</td>
<td width='90' height='17' bgcolor='#6B86AD'>
<div align='center'><font face='Verdana, Arial, Helvetica, sans-serif' size='1'><?php echo $parciais_devolvidos; ?>
</font></div>
</td>
<td width='90' height='17' bgcolor='#6B86AD'>
<div align='center'><font face='Verdana, Arial, Helvetica, sans-serif' size='1'><?php echo $parciais_cancelados; ?>
</font></div>
</td>
</tr>
</table>
<table width='800' border='0' cellspacing='1' cellpadding='2'>
<tr>
<td>
<script language='JavaScript'>
function redireciona() {
window.open('busca_pedidos.php','pleniPedMain');
}
</script>
<input type='submit' name='search' value='Buscar pedido...' onClick="redireciona()">
</td>
<td>
<script language='JavaScript'>
function refreshMode() {
this.location='ped_menu_refresh.php';
}
</script>
<input type='button' name='search2' value='Modo refresh' onClick="refreshMode()">
</td>
<td bgcolor='#213041'>
<div align='center'><font size='1' face='Verdana, Arial, Helvetica, sans-serif'><a href='prods_a_solicitar.php' target='pleniPedMain' class='lista'>Produtos
a serem solicitados ao fornecedor.</a></font></div>
</td>
<td bgcolor='#213041'>
<div align='center'><font size='1' face='Verdana, Arial, Helvetica, sans-serif'><a href='prods_solicitados.php' target='pleniPedMain' class='lista'>Produtos
aguardando entrega do fornecedor.</a></font></div>
</td>
<td bgcolor='#213041'>
<div align='center'><font size='1' face='Verdana, Arial, Helvetica, sans-serif'>Total
de pedidos:<b> <? echo $todos; ?></b></font></div>
</td>
</tr>
</table>


</td>
</tr>
</table>
</body>
</html>