troubleshooting Question

How to speed up php-script

Avatar of tromm
tromm asked on
PHP
4 Comments1 Solution357 ViewsLast Modified:
I have a php script that is supposed to generate a list of plate numbers with some data about coverage for cardealers that have multiple cars covered under one policy.
The data for this list is fetched through mysqli_connect mysqli_query and mysqli_fetch_array and subsequently converted to HTML.
All goes well until we reach about 300 platenumbers, then as a result I get only part of the list and a Fatal error:
"Fatal error: Maximum execution time of 60 seconds exceeded in c:\Inetpub\Scripts\printallemutaties.php on line 65" (see below for a code listing)

Before this happens you see that the page is being built up; the scrollbar is getting smaller every few seconds and after a minute it getts really small of a sudden and processing has stopped.
I didn't write the code and it never gave us any problems, but some cardealers have been changing their behavior and have submitted hundreds of cars. These customers now cannot print an entire list of insured cars.

The question: what can I do to keep performance under an acceptable amount of time (without increasing maximum execution time in php.ini) independent of the number of platenumbers that is retrieved (well at least not linear dependent or worse).

This is the actual php-code: (Do not be scared by the dutch variable and function names, it shouldn't matter)


##  START ##

<html>
<head>
<?php include("auth.inc");
    $polisnummer = $_REQUEST["polisnummer"];
    $kenteken = $_REQUEST["kenteken"];
    $naam = $_REQUEST["naam"];
?>
<title></title>
<style type="text/css">  
      TD,P {font :  9pt arial, helvetica, sans-serif;}
      P.afdrukdatum { font :  8pt arial, helvetica, sans-serif;}
      TD.header {font : bold 9pt arial, helvetica, sans-serif;}
</style>
</head>
<body>
<?php


function print_kenteken_info($link,$kenteken,$polisnummer,$polisid,$naam)
{

//      $querystring="select meldcode,soort_voertuig,merk,uitvoering,kilometers,bouwjaar,bouwmaand,catwaarde
//                 from kenteken where kentekennummer=\"" . $kenteken . "\"";
      $querystring="select meldcode,soort_voertuig,merk,uitvoering,kilometers,bouwjaar,bouwmaand,catwaarde
                 from mutatie where kentekennummer=\"" . $kenteken . "\" and einddatum IS NULL";

      $result= mysqli_query($link, $querystring);
      $info=mysqli_fetch_array($result);      

      $huidigjaar=date(Y);
      $huidigemaand=date(m);
      $leeftijd = (  (($huidigjaar*12)+$huidigemaand) - (($info[bouwjaar]*12)+$info[bouwmaand]) );


      print "<table border=0><tr>
      <td align=left width=440>
      <table border=0>
      <tr>
      <td>Kenteken:<td><b>$kenteken</b>
      <td>Meldcode:<td><b>$info[0]</b><tr>
      <td>Soort:<td><b>$info[1]</b>
      <td>Merk/Type:<td><b>$info[2] $info[3]</b>
      </table>
      <td align=right>
      <table border=0><tr>
      <td>Bouwjaar/maand:<td><b>$info[bouwjaar]/$info[bouwmaand]</b><tr>
      <td>Cataloguswaarde:<td><b>$info[catwaarde] &#8364;</b></table>
      </table><hr>";

   

#      $querystring="select date_format(startdatum,\"%d-%m-%Y\") as datum_van, date_format(startdatum, \"%H:%i\") as tijd_van,
#                   date_format(einddatum,\"%d-%m-%Y\") as datum_tot, date_format(einddatum, \"%H:%i\") as tijd_tot,
#                soort_gebruik,bestuurder,eigen_risico,afgemeld,casco,casco_dekking,ongevallen,schade,rechtsbijstand,kilometers,default_dekking
#                   from mutatie where kentekennummer=\"$kenteken\" and polis_id=$polisid order by startdatum desc,hour(startdatum) desc ,minute(startdatum) desc, second(startdatum) desc, einddatum desc ,hour(einddatum) desc ,minute(einddatum) desc, second(einddatum) desc";


      $querystring="select date_format(startdatum,\"%d-%m-%Y\") as datum_van, date_format(startdatum, \"%H:%i\") as tijd_van,
                   date_format(einddatum,\"%d-%m-%Y\") as datum_tot, date_format(einddatum, \"%H:%i\") as tijd_tot,
                soort_gebruik,bestuurder,eigen_risico,afgemeld,casco,casco_dekking,ongevallen,schade,rechtsbijstand,kilometers,default_dekking
                    from mutatie where kentekennummer=\"$kenteken\" and polis_id=$polisid order by volgnummer desc";


      #print $querystring;
         $result2=mysqli_query($link, $querystring);
      while ($mut=mysqli_fetch_array($result2))
      {
         if ($i==0)
         {
            print "<table border=0>";
            if ($j==0)
            {
               print "<tr><td class=header width=110>Dekking vanaf<td class=header width=110>Dekking tot<td class=header width=90>Gebruik<td class=header width=120>Bestuurder
                     <td class=header width=120>Dekking<td class=header width=40>Eigen<br> risico<td class=header width=10>OI
                      <td class=header width=10>SI<td class=header width=10>Rb";
              if (strcasecmp($info[kilometers],"yes") == 0)
                 print "<td class=header width=40>Kilometerstand";
             print "<tr><tr>";
               $j=1;
            }  
            $i=1;
         }
         print "<tr><td>$mut[0] $mut[1]";
         print "<td>$mut[2] $mut[3]";
         print "<td>$mut[soort_gebruik]<td>$mut[bestuurder]<td>";
      
         
         if ($mut[casco]=="on")
         {
           if ($leeftijd <= 36)
         {
            if ($mut[default_dekking]=="yes")
              print "WA + Casco";
            else
               print "WA + Casco";
         }
           else
         if (($leeftijd > 36) and ($leeftijd <= 84))
         {
            if ($mut[default_dekking]=="yes")
              print "WA + Casco";
            else
               print "WA + Casco beperkt";
         }
         else      
         if (($leeftijd > 84) and ($leeftijd <= 240))
         {
            if ($mut[default_dekking]=="yes")
              print "WA + Casco beperkt";
            else
               print "WA + Casco";
         }
         else      
         if (($leeftijd > 84) and ($leeftijd <= 240))
         {
            if ($mut[default_dekking]=="yes")
              print "WA";
            else
               print "WA";
         }
           else
             print "WA";
       }
       else
          if (!(strcmp($mut[soort_gebruik],"Afgemeld")==0 ))
            print "WA";
         
         print "<td>";
         if ($mut[eigen_risico]==0)
          print "nvt<td>";
         else
            print "$mut[eigen_risico]<td>";

        if ($mut[ongevallen]=="on")
           print "J";
        else
           print "N";
        print "<td>";
        if ($mut[schade]=="on")
           print "J";
        else
           print "N";
        print "<td>";
        if ($mut[rechtsbijstand]=="on")
           print "J";
        else
           print "N";
      if (strcasecmp($info[kilometers],"yes") == 0)
              print "<td>$mut[kilometers]";

    }
    if ($i)
       print "</table><br><br><br>";    
}


   $link=mysqli_connect($host,$user,$passwd,"automeld") or die ("Database error!");

   ##
   ## Haal polidid
   ##

   $querystring="select id from polis where polisnummer=\"" . $polisnummer . "\"";
   $result= mysqli_query($link, $querystring) or $stop=1;  

   if ($stop or !($id=mysqli_fetch_array($result)))
     print "<center><b>Geen gegevens gevonden.....</b></center>";

   else
   {
      print "<table width=100%><tr><td align=left><img src=../images/topframe_adres.gif width=130 height=44>
             <td align=right><img src=../images/topframe_logo.gif width=80 height=41 border=0></table>";
      print "<p class=afdrukdatum> Afdrukdatum: " .date ("d-m-Y  / H:i ") . "<p align=center>Alle mutaties</p>";
      print "<p style=font:arial>Polisnummer: &nbsp;&nbsp;$polisnummer &nbsp;&nbsp;&nbsp;&nbsp; $naam</p>";
      $querystring="select distinct kentekennummer from mutatie where polis_id=$id[0] and verwijderen=\"no\" order by kentekennummer";
      $result= mysqli_query($link, $querystring);
      while ($kenteken=mysqli_fetch_array($result))
      {  
       print_kenteken_info($link, $kenteken[0],$polisnummer,$id[0],$naam);
     }
  }
?>

</body>
</html>


## FINISH ##










ASKER CERTIFIED SOLUTION
Richard Quadling
Senior Software Developer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros