Page 1 of 1

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

Posted: Thu Sep 14, 2006 5:06 am
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

Posted: Thu Sep 14, 2006 5:18 am
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.

Posted: Thu Sep 14, 2006 5:43 am
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.

Posted: Thu Sep 14, 2006 5:59 am
by ryuuka
first off all: sorry for the mess i made :)

second: thanks for the tips saved me at least 3 to 4 seconds

Posted: Thu Sep 14, 2006 6:10 am
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?

Posted: Thu Sep 14, 2006 6:29 am
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

Posted: Thu Sep 14, 2006 6:40 am
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?

Posted: Thu Sep 14, 2006 6:55 am
by ryuuka
sorry bout the slight confusion

and it's a datetime field

Posted: Thu Sep 14, 2006 6:59 am
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)

Posted: Thu Sep 14, 2006 7:06 am
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)

Posted: Thu Sep 14, 2006 7:45 am
by volka
There's still servernaam and servicenaam in that table?
Please show me your current query.

Posted: Thu Sep 14, 2006 8:08 am
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