Link to home
Start Free TrialLog in
Avatar of tromm
tromm

asked on

How to speed up php-script

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 ##










Avatar of Richard Quadling
Richard Quadling
Flag of United Kingdom of Great Britain and Northern Ireland image

Hi iextensive,


<?php
set_time_limit(0);
...
?>

Regards,

Richard Quadling.
Hi tromm,

Sorry about calling you iextensive!

Each PHP installation has a config file which controls, among other things, how long a PHP script can run for.

The function set_time_limit() allows you to change this in your script.

Setting it to 0 means the script can run forever, so watch out for that. Make sure your script can finish!!!!


Regards,

Richard.
Avatar of tromm
tromm

ASKER

Ok thanks, now I have at least a way to generate the full list. But how can I make it go faster? putting more code in sql? or change the way HTML is generated? or anything else that speeds up php-processing?

regards,

Martijn Tromm
ASKER CERTIFIED SOLUTION
Avatar of Richard Quadling
Richard Quadling
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial