[SOLVED] i really need to make this one go faster

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
ryuuka
Forum Contributor
Posts: 128
Joined: Tue Sep 05, 2006 8:18 am
Location: the netherlands

[SOLVED] i really need to make this one go faster

Post by ryuuka »

ok here's the deal:
i am making a site that needs to give a list of the differtent status for different services the status
comes from a database that get a new row every 5 min.
The following script gets the information
from this table and another one it needs to see wich services to check the problem is that it takes 19 sec to load
and i really need to cut this time down.

any good on how to do this

Code: Select all

<?php
$rs = $db->Execute("SELECT     b.servernaam, a.displaynaam, a.servicenaam, b.status, b.lastcheck
                          FROM dbo.*****_log_Services a LEFT OUTER JOIN
                          *****_services_status b ON a.computernaam = b.servernaam AND a.servicenaam = b.servicenaam LEFT OUTER JOIN
                          *****_services_status c ON b.servernaam = c.servernaam AND b.servicenaam = c.servicenaam AND b.lastcheck < c.lastcheck
                     WHERE (a.controleren = 1) AND (c.lastcheck IS NULL)");
   

   // Kop tekstblok
   header("Refresh: 300;");
      head("Status van de services","m");

      
   
  // XHTML
  echo ('<table>
            <tr>
              <th>Status</th>
              <th>ServerName</th>
              <th>Displaynaam</th>
              <th>Servicenaam</th>
              <th>LastCheck</th>
            </tr>');
  
    // LUS
  while (!$rs->EOF) {

    // ZET RECORD IN EEN ARRAY  
    $a_Row = $rs->fields;

    // ============= \\
    // PING COMPUTER \\
    // ============= \\

    // VARIABELEN OPSLAAN
    $s_ServerName   = $a_Row['servernaam'];
    $s_Displaynaam  = $a_Row['displaynaam']; 
    $s_service      = $a_Row['servicenaam'];
    $s_Status       = $a_Row['status'];
    $s_LastCheck    = $a_Row['lastcheck'];

    // VARIABLE ADODB OMZETTEN NAAR EEN STRING
    settype($s_ServerName,  "string");
    settype($s_Displaynaam, "string");
    settype($s_service,     "string");
    settype($s_Status,      "string");
    settype($s_LastCheck,   "string");

      echo "\n  <tr>";

      if ($s_Status == "") 
      {
        echo "\n    <td class=\"pijl\"><img src=\"skin/".$s_MenuSkin."/No_Status_Available.gif\" title=\"No Status Available\" /></td>";
      }
      else
      {
        echo "\n    <td class=\"pijl\"><img src=\"skin/".$s_MenuSkin."/".$s_Status.".gif\" title=\"".$s_Status."\" /></td>";
      }
    echo('  
      <td>'.$s_ServerName.' </td>
      <td>'.$s_Displaynaam.'</td>
      <td>'.$s_service.'    </td>
      <td>'.$s_LastCheck.'  </td>
      </tr>
    ');
    
    
    // Ga naar volgend record
    $rs->MoveNext();
  }
  // XHTML
  echo "\n</table>";
     
 
 
 $rs = $db->Execute("SELECT computernaam FROM *****_computers WHERE (computernaam LIKE 'sr0%') OR (computernaam LIKE 'dc0%')");
 
  echo ('<form action=index.php?pagename=servicestatussort method="POST">
          <table>
           <tr>
           <th>
           <td>
              <select name="servernaam">              
              <option value= "0" selected> Kies server: 
              
       ');
                                while (!$rs->EOF)
                                {
                                  $value = $rs->Fields['computernaam']->value;
                                  echo "<option value=$value>$value";
                                  $rs->movenext();
                                }
           
  echo ('</select></td></th>
                        
            </table>
          <input type=submit value=submit>
        </form>');
  foot();
?>

the ***** are the name of the db and company i work for
Last edited by ryuuka on Thu Sep 14, 2006 8:09 am, edited 1 time in total.
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post by GM »

You might be able to speed it up if you add some indexes to your db tables.

If you are using MySQL, use the "EXPLAIN" query to see potential problems, and add indexes accordingly.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

man, what did you do to my nicely structured query? Barely readable anymore ;)

Code: Select all

SELECT
	b.servernaam, a.displaynaam, a.servicenaam, b.status, b.lastcheck
FROM
	dbo.*****_log_Services as a
LEFT OUTER JOIN
	*****_services_status as b
ON
	a.computernaam = b.servernaam
	AND a.servicenaam = b.servicenaam
LEFT OUTER JOIN
	*****_services_status as c
ON
	b.servernaam = c.servernaam
	AND b.servicenaam = c.servicenaam
	AND b.lastcheck < c.lastcheck
WHERE
	a.controleren=1
	AND c.lastcheck IS NULL
If you create a new table service_desc: service_id, servernaam, servicenaam,displaynaam
you can get rid of a lot of string comparisons. No other table contains servernaam, servicenaam or displaynaam. They are all referenced by service_id which leads to a simple comparison of numbers instead of a complex one with strings.
e.g. instead of

Code: Select all

ON
	a.computernaam = b.servernaam
	AND a.servicenaam = b.servicenaam
the much simpler to perform

Code: Select all

ON
	a.service_id = b.service_id
Set up indices for service_id in all tables and the query should run much faster.
ryuuka
Forum Contributor
Posts: 128
Joined: Tue Sep 05, 2006 8:18 am
Location: the netherlands

Post by ryuuka »

first off all: sorry for the mess i made :)

second: thanks for the tips saved me at least 3 to 4 seconds
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

ryuuka wrote:comes from a database that get a new row every 5 min.
Does _services_status.lastcheck reflect that, i.e. can records with _services_status.lastcheck older than (let's say) 10min be filtered out right from the start? How many records are there approx. in this table and how "old" are they? (i.e. would lastcheck<[last 10 min|last hour|last day] filter out the vast majority of records?)
What type of field is _services_status.lastcheck?
ryuuka
Forum Contributor
Posts: 128
Joined: Tue Sep 05, 2006 8:18 am
Location: the netherlands

Post by ryuuka »

service_status.LastCheck gives contains the date at wich the new status was put in

example:

servernaam
DC0001

service
DNS

status
Running

lastcheck
11-9-2006 14:51:21

this means that at 11-9-2006 14:51:21 the status of the DNS was running
and unfortunatly the boss didn't want this to be deleted after the entries were more then
20 min old because he wanted a history of when a specified service was down.

this time btw is the first entry it's been running continously 24/7 since then
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

i.e.
"can records with _services_status.lastcheck older than (let's say) 10min be filtered out right from the start?" yes. (it doesn't mean delete)
"would lastcheck<[last 10 min|last hour|last day] filter out the vast majority of records?" yes.
:?:

What type of field is _services_status.lastcheck? DATE, DATETIME,TIMESTAMP,INT? VARCHAR?
ryuuka
Forum Contributor
Posts: 128
Joined: Tue Sep 05, 2006 8:18 am
Location: the netherlands

Post by ryuuka »

sorry bout the slight confusion

and it's a datetime field
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

Uh, I forgot
AND b.lastcheck < c.lastcheck
Do you have an index for lastcheck?
If not, do so ...stante pede :D (but EXPLAIN should have told you that)
ryuuka
Forum Contributor
Posts: 128
Joined: Tue Sep 05, 2006 8:18 am
Location: the netherlands

Post by ryuuka »

yes lastcheck has a index seeing as a pk has an automatic index
in mssql anyway.
the primary key of a table is automaticly indexed
at least that's what it says in the mssql manual

oh and servnaam, servicenaam and lastcheck are all pk's (combined key)
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

There's still servernaam and servicenaam in that table?
Please show me your current query.
ryuuka
Forum Contributor
Posts: 128
Joined: Tue Sep 05, 2006 8:18 am
Location: the netherlands

Post by ryuuka »

nm it's already solved

was kind of a race between me and the boss to see who could speed it up faster
i lost
and i owe him 5 bucks

he rewrote the entire thing only thing missing here is the dbase connection

this is the new thing:

Code: Select all

$rs = $objConn->Execute('SELECT servicenaam, computernaam FROM Gispen_log_Services ORDER BY computernaam') or die($objConn->ErrorMsg());
 while (!$rs->EOF)
 {
$Server = $rs->Fields['computernaam']->value;
$Service = $rs->Fields['servicenaam']->value;
	
print ("Server = ".$Server." Service = ".$Service."<br>");
// selecteren service status
 $rs2 = $objConn->Execute("SELECT     TOP 1 ****_services_status.servernaam, ****_services_status.servicenaam, ****_services_status.status, gispen_services_status.lastcheck, 
							****_log_Services.displaynaam
FROM         
****_services_status INNER JOIN
   ****_log_Services ON ****_services_status.servernaam = ****_log_Services.computernaam 
AND 
 ****_services_status.servicenaam = ****_log_Services.servicenaam
							WHERE     (****_services_status.servernaam = '$Server') AND (****_services_status.servicenaam = '$Service') ORDER BY ****_services_status.lastcheck DESC") or die($objConn->ErrorMsg());
	while (!$rs2->EOF)
	{
		$Servernaam = $rs2->Fields['servernaam']->value;
		$Servicenaam = $rs2->Fields['servicenaam']->value;
		$Displaynaam = $rs2->Fields['displaynaam']->value;
		$Status = $rs2->Fields['status']->value;
		$Lastcheck = $rs2->Fields['lastcheck']->value;
     echo ('<table>
            <tr>
              <th>Status</th>
              <th>ServerName</th>
              <th>Displaynaam</th>
              <th>Servicenaam</th>
              <th>LastCheck</th>
            </tr>');
            
      echo "\n  <tr>";
      if ($Status == "") 
      {
        echo "\n    <td class=\"pijl\"><img src=\"skin/".$s_MenuSkin."/No_Status_Available.gif\" title=\"No Status Available\" /></td>";
      }
      else
      {
        echo "\n    <td class=\"pijl\"><img src=\"skin/".$s_MenuSkin."/".$Status.".gif\" title=\"".$Status."\" /></td>";
      }
    echo('  
      <td>'.$Servernaam.' </td>
      <td>'.$Displaynaam.'</td>
      <td>'.$Servicenaam.'    </td>
      <td>'.$Lastcheck.'  </td>
      </tr>
    ');
    
    
    
    
    
		// echo $Servernaam."+".$Displaynaam."+".$Status."+".$Lastcheck."<br><br>";
		$rs2->movenext();
	}
	$rs->movenext();
}


this code makes it go in like 2.5 sec
while i only got it down to 15 using the previous code

sorry volka he raped your code
Post Reply