How can I improve the speed of this script?

IBMKenobi
IBMKenobi used Ask the Experts™
on
This script works well but generated time out errors after 30 seconds.
Now that I have filled the database with almost 400 entries I receive this error:


FastCGI Error
The FastCGI Handler was unable to process the request.

Error Details:

    * The FastCGI process exceeded configured activity timeout
    * Error Number: 258 (0x80070102).
    * Error Description: The wait operation timed out.

HTTP Error 500 - Server Error.
Internet Information Services (IIS)

The only thing I have done to improve the script is add this info from Fasthosts:
ob_start("ob_gzhandler"); //Fasthosts Gzip compression for optimising script handling time

Please ensure your replies are readable by a complete programming noob, thanks very much!
<?PHP include 'dbconnection.php'; ?>

<!-- NAME SEARCH -->
<form action="<?=$_SERVER['PHP_SELF']?>" method="POST">
<h4>Looking for a particular venue?</h4> 
<br />
Enter the name here: <input type="text" name="name" size="30" maxlength="50" /><input type="submit" name="namesearch" value="Go" />
</form>

<br />
<!-- ADVANCED SEARCH -->
<div class="wheretoeatsearchwrapper">
<h4>Advanced Search:</h4>
<form action="<?=$_SERVER['PHP_SELF']?>" method="POST">
<div class="searchlocation">
	<h4>Choose a Location:</h4><br />
		<select name="county[]" size="6" multiple="multiple" id="body">
                <?PHP
$result = @mysql_query('SELECT ID, county FROM counties ORDER BY county ASC');
if (!$result) {
die ('<p>Error performing query<p>');
}
while ($row = mysql_fetch_array($result) ) {
echo ('<option name="county[]" value="'.$row['ID'].'">' .$row['county']. '</option>');
}
?>
              </select>
          <p class="body-small">To choose multiple counties, <br />
        hold down the Ctrl or Cmd key. </p>
</div>

<div class="searchvenuetype">        
	<h4>Choose a Venue Type:</h4><br />
		<select name="venuetype[]" size="6" multiple="multiple" id="body">
		<option name="venuetype[]" value="all" selected="selected">All Types</option>
                  <?PHP
$result = @mysql_query('SELECT ID, venuetype FROM venuetype ORDER BY venuetype ASC');
if (!$result) {
die ('<p>Error performing query<p>');
}
while ($row = mysql_fetch_array($result) ) {
echo ('<option name="venuetype[]" value="'.$row['ID'].'">' .$row['venuetype']. '</option>');
}
?>
		</select>
            <p class="body-small">To choose multiple styles, <br />
              hold down the Ctrl or Cmd key. </p>
</div>

<div class="searchrating">
	<h4>Choose a Rating:</h4><br />
                <select name="rating[]" size="6" multiple="multiple" id="body">
                  <option name="rating[]" value="0">No Rating</option>
                  <option name="rating[]" value="1">1 Star</option>
                  <option name="rating[]" value="2">2 Stars</option>
                  <option name="rating[]" value="3">3 Stars</option>
                  <option name="rating[]" value="4">4 Stars</option>
                  <option name="rating[]" value="5">5 Stars</option>
                </select>
            <p class="body-small">To choose multiple ratings, <br />
              hold down the Ctrl or Cmd key. </p>
</div>

<div class="wheretoeatsubmit">
	<input type="submit" name="venuesearch" value="Show me the food" />
</div>
    </form>
</div> <!-- END SEARCH WRAPPER -->

<?PHP
ob_start("ob_gzhandler"); //Fasthosts Gzip compression for optimising script handling time

/* NAME SEARCH */
if ( isset($_POST['namesearch']) ){
$name = $_POST['name'];
if (!preg_match("/^[a-zA-Z_]{1,}$/D", $name)) die();
$sql_name = mysql_query("SELECT *, venue.ID AS VID FROM venue, counties, countylookup, paid, ratinglookup WHERE venue.name LIKE ('%$name%') AND venue.ID = countylookup.venueID AND counties.ID=countylookup.countyID AND venue.ID=paid.venueID AND venue.ID=ratinglookup.venueID ORDER BY venue.name ASC LIMIT 0, 50"); 

$numresults = mysql_num_rows($sql_name);
echo ('<h3 class="titleresults">Results: '.$numresults.'</h3>');


if ($sql_name) {

while ($nameresults = mysql_fetch_array($sql_result)) {
	$venueID = $nameresults['VID'];
	$name = $nameresults['name'];
	$address1 = $nameresults['address1'];
	if ($address2 = $nameresults['address2']) {
		$address2 = $nameresults['address2'].'<br />';
	}
	if ($vcounty = $nameresults['county']) {
		$vcounty = $nameresults['county'].'<br />';
	}
	if ($postcode = $nameresults['postcode']) {
		$postcode = $nameresults['postcode'].'<br />';
	}
	if ($rating = $nameresults['total_rating']) {
		if ($rating == 0) {
			$rating = '<p class="ratethisvenue"><a href="rateavenue.php">Be the first to rate this venue.</a></p>'; 
		} elseif ($rating == 1){
			$rating = '<form name="reviews" action="reviews.php" method="POST"><img src="graphics/star_1.png" width="85" height="16" alt="1 star" /><input type="hidden" name="venueID" value="'.$venueID.'" /><input type="hidden" name="name" value="'.$name.'" /> <input type="submit" value="Read Reviews" class="textButton"></form><br />';
		} elseif ($rating == 2){
			$rating = '<form name="reviews" action="reviews.php" method="POST"><img src="graphics/star_2.png" width="85" height="16" alt="2 star" /><input type="hidden" name="venueID" value="'.$venueID.'" /><input type="hidden" name="name" value="'.$name.'" /> <input type="submit" value="Read Reviews" class="textButton"></form><br />';
		} elseif ($rating == 3){
			$rating = '<form name="reviews" action="reviews.php" method="POST"><img src="graphics/star_3.png" width="85" height="16" alt="3 star" /><input type="hidden" name="venueID" value="'.$venueID.'" /><input type="hidden" name="name" value="'.$name.'" /> <input type="submit" value="Read Reviews" class="textButton"></form><br />';
		} elseif ($rating == 4){
			$rating = '<form name="reviews" action="reviews.php" method="POST"><img src="graphics/star_4.png" width="85" height="16" alt="4 star" /><input type="hidden" name="venueID" value="'.$venueID.'" /><input type="hidden" name="name" value="'.$name.'" /> <input type="submit" value="Read Reviews" class="textButton"></form><br />';
		} elseif ($rating == 5){
			$rating = '<form name="reviews" action="reviews.php" method="POST"><img src="graphics/star_5.png" width="85" height="16" alt="5 star" /><input type="hidden" name="venueID" value="'.$venueID.'" /><input type="hidden" name="name" value="'.$name.'" /> <input type="submit" value="Read Reviews" class="textButton"></form><br />';
		}
	}
	if ($nameresults['paid']) {
		$paid = 'Website: <a href="http://'.$nameresults['website'].'" target="_blank">'.$nameresults['website'].'</a><br /> 
				 Telephone: '.$nameresults['telephone'].'<br />
				 E-mail: <a href="mailto:'.$nameresults['email'].'">'.$nameresults['email'].'</a><br />';
	} else {
	$paid = '<p class="ownthisvenue">Own this venue? 
			<a href="advertise.php">Add contact details</a>.</p>';
	  }	
	
	echo ('
	<h4>'.$name.'</h4><br />
	'.$address1.'<br />
	'.$address2.'
	'.$vcounty.'
	'.$postcode.'
	'.$paid.'
	'.$rating.'
	<hr />
	');
	}
}

if ($numresults < 1) {
	echo ('
    <p class="noresults">Unfortunately, no results are available, try a different name or spelling, or use the advanced search below.<br />
    If you know of a venue but cannot see it listed, <a href="recommend.php">Recommend It</a>.</p>');
}
}



/* MENU SEARCH */
if ( isset($_POST['venuesearch']) ){

/* COUNTY */
if (isset($_POST['county'])) {
$countyIDs = implode(",",array_map("intval",$_POST['county'])); // selected county values combined into comma separated list
if (!preg_match("/^[0-9_]{1,}$/D", $countyIDs)) die();
$sql_county = mysql_query("SELECT venueID FROM countylookup WHERE countyID IN ($countyIDs)"); // get venue IDs from database where they match $countyIDs
$countyarray=array(); // creates empty array
while ($countyresults = mysql_fetch_array($sql_county)) { // fetch results from $sql_county
	array_push($countyarray,$countyresults['venueID']); // array_push adds each new value onto the end of the $countyarray with a numeric key
}
	$filtercountyarray = array_unique($countyarray); // remove duplicate entries from the array */
} else {
echo ('<div class="noresults"><h4>Please choose one or more Counties to begin your search.</h4></div>');
die;
}

/* VENUETYPE */
if (in_array('all',$_POST['venuetype'])) {

$sql_venuetype = mysql_query("SELECT venueID FROM venuetypelookup");
$venuetypearray=array();
while ($venuetyperesults = mysql_fetch_array($sql_venuetype)) {
	array_push($venuetypearray,$venuetyperesults['venueID']);	
}
	$filtervenuetypearray = array_unique($venuetypearray);

} else {

$venuetypeIDs = implode(",",array_map("intval",$_POST['venuetype']));
if (!preg_match("/^[0-9_]{1,}$/D", $venuetypeIDs)) die();
$sql_venuetype = mysql_query("SELECT venueID FROM venuetypelookup WHERE venuetypeID IN ($venuetypeIDs)");
$venuetypearray=array();
while ($venuetyperesults = mysql_fetch_array($sql_venuetype)) {
	array_push($venuetypearray,$venuetyperesults['venueID']);	
}
	$filtervenuetypearray = array_unique($venuetypearray);
} 

/* RATING */

if (isset($_POST['rating'])) {
$totalrating = implode(",",array_map("intval",$_POST['rating']));
if (!preg_match("/^[0-9_]{1,}$/D", $totalrating)) die();
$sql_totalrating = mysql_query("SELECT venueID FROM ratinglookup WHERE total_rating IN ($totalrating)");
$ratingarray=array();
while ($totalratingresults = mysql_fetch_array($sql_totalrating)) {
	array_push($ratingarray,$totalratingresults['venueID']);
}
	$filterratingarray = array_unique($ratingarray);
} else {
$sql_totalrating = mysql_query("SELECT venueID FROM ratinglookup");
$ratingarray=array();
while ($totalratingresults = mysql_fetch_array($sql_totalrating)) {
	array_push($ratingarray,$totalratingresults['venueID']);
}
	$filterratingarray = array_unique($ratingarray);
}

/* COMPARE 3 RESULT ARRAYS FOR MATCHING venueIDs */
$compare1 = array_intersect($filtercountyarray,$filtervenuetypearray,$filterratingarray);

if ($compare1) {

$numresults = count($compare1);
echo ('<h3 class="titleresults">Results: '.$numresults.'</h3>');

$comparelist = implode(",",$compare1);
$sql_result = mysql_query("SELECT *, venue.ID AS VID FROM venue, counties, countylookup, paid, ratinglookup WHERE venue.ID IN ($comparelist) AND venue.ID=countylookup.venueID AND counties.ID=countylookup.countyID AND venue.ID=paid.venueID AND venue.ID=ratinglookup.venueID ORDER BY venue.name ASC LIMIT 0, 50"); 

	if (!$sql_result) {
		die( '<span class="resultbad">Error retrieving results from venue table.<br /></span>' );
	}
	
	while ($searchresults = mysql_fetch_array($sql_result)) {
	$venueID = $searchresults['VID'];
	$name = $searchresults['name'];
	$address1 = $searchresults['address1'];
	if ($address2 = $searchresults['address2']) {
		$address2 = $searchresults['address2'].'<br />';
	}
	if ($vcounty = $searchresults['county']) {
		$vcounty = $searchresults['county'].'<br />';
	}
	if ($postcode = $searchresults['postcode']) {
		$postcode = $searchresults['postcode'].'<br />';
	}
	if ($rating = $searchresults['total_rating']) {
		if ($rating == 0) {
			$rating = '<p class="ratethisvenue"><a href="rateavenue.php">Be the first to rate this venue.</a></p>'; 
		} elseif ($rating == 1){
			$rating = '<form name="reviews" action="reviews.php" method="POST"><img src="graphics/star_1.png" width="85" height="16" alt="1 star" /><input type="hidden" name="venueID" value="'.$venueID.'" /><input type="hidden" name="name" value="'.$name.'" /> <input type="submit" value="Read Reviews" class="textButton"></form><br />';
		} elseif ($rating == 2){
			$rating = '<form name="reviews" action="reviews.php" method="POST"><img src="graphics/star_2.png" width="85" height="16" alt="2 star" /><input type="hidden" name="venueID" value="'.$venueID.'" /><input type="hidden" name="name" value="'.$name.'" /> <input type="submit" value="Read Reviews" class="textButton"></form><br />';
		} elseif ($rating == 3){
			$rating = '<form name="reviews" action="reviews.php" method="POST"><img src="graphics/star_3.png" width="85" height="16" alt="3 star" /><input type="hidden" name="venueID" value="'.$venueID.'" /><input type="hidden" name="name" value="'.$name.'" /> <input type="submit" value="Read Reviews" class="textButton"></form><br />';
		} elseif ($rating == 4){
			$rating = '<form name="reviews" action="reviews.php" method="POST"><img src="graphics/star_4.png" width="85" height="16" alt="4 star" /><input type="hidden" name="venueID" value="'.$venueID.'" /><input type="hidden" name="name" value="'.$name.'" /> <input type="submit" value="Read Reviews" class="textButton"></form><br />';
		} elseif ($rating == 5){
			$rating = '<form name="reviews" action="reviews.php" method="POST"><img src="graphics/star_5.png" width="85" height="16" alt="5 star" /><input type="hidden" name="venueID" value="'.$venueID.'" /><input type="hidden" name="name" value="'.$name.'" /> <input type="submit" value="Read Reviews" class="textButton"></form><br />';
		}
	}
	if ($searchresults['paid']) {
		$paid = 'Website: <a href="http://'.$searchresults['website'].'" target="_blank">'.$searchresults['website'].'</a><br /> 
				 Telephone: '.$searchresults['telephone'].'<br />
				 E-mail: <a href="mailto:'.$searchresults['email'].'">'.$searchresults['email'].'</a><br />';
	} else {
	$paid = '<p class="ownthisvenue">Own this venue? 
			<a href="advertise.php">Add contact details</a>.</p>';
	  }	
	
	echo ('
	<h4>'.$name.'</h4><br />
	'.$address1.'<br />
	'.$address2.'
	'.$vcounty.'
	'.$postcode.'
	'.$paid.'
	'.$rating.'
	<hr />
	');
}

} else {
	echo ('
	<h3 style="clear:both;">Results:</h3>
    <p class="noresults">Unfortunately, no results are available, widen your search by trying different venue styles or multiple ratings.<br />
    If you know of a venue but cannot see it listed, <a href="recommend.php">Recommend It</a>.</p>');
}
}
?>

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Are you using connect or pconnect in your database connections?

Author

Commented:
<?PHP
/* Connection to Database Server */
$dbcnx = @mysql_connect('IPaddress','username','password');
if (!$dbcnx) {
    echo ('<span class="resultbad">Unable to connect to the ' . 'database server at this time.<br />'.mysql_error().'</span>');
    exit();
}

/* Choosing a Database */
mysql_select_db('dbname', $dbcnx);
if (! @mysql_select_db('dbname') ) {
    die ('<span class="resultbad">Unable to connect to the dbname ' . 'database at this time.<br />'.mysql_error().'</span>');
}
?>
Commented:
Your script doesn't seem bad, and 400 entries shouldn't cause a problem unless you're on really really bad hardware. My guess is that your queries are not optimized. Try reading this article:
http://www.experts-exchange.com/Database/MySQL/A_1250-3-Ways-to-Speed-Up-MySQL.html

Unless the resulting page is HUGE (in terms of the HTML that gets returned), using the gzhandler shouldn't make too much of a difference, so I wouldn't bother with it.

If, for some reason, your script truly takes forever to load after optimizing your queries, try adding set_time_limit(0); to the top of your script.
OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

Commented:
You could also use time() throughout your script to figure out where the slowest parts are:

<?php
// Top of your script
$starttime = time();
file_put_contents("time.log", "Starting timer.\n");

.... chunks of code ....
file_put_contents("time.log", "Line " . __LINE__ . ": " . (time()-$starttime) . " seconds elapsed.\n",FILE_APPEND);

.... chunks of code ....
file_put_contents("time.log", "Line " . __LINE__ . ": " . (time()-$starttime) . " seconds elapsed.\n",FILE_APPEND);

.... chunks of code ....
file_put_contents("time.log", "Line " . __LINE__ . ": " . (time()-$starttime) . " seconds elapsed.\n",FILE_APPEND);

// end of script
?>

That should create a file called time.log that shows you how many seconds have elapsed by the time a certain line number has been reached.

Author

Commented:
Hi, that article looks like it addresses a good number of issues within my script.
They are going to take a while to work through and test so I'll assign the points to your answer for your quick reply and helpful timing comments now. If I run into problems after I've worked though that article I'll post a new question.

Thanks very much,
Mike
Do you have both a production and then a separate live sever to test on to see if the behavior is consistent (i.e., its definitely the script and not a hardware issue)?
I was thinking on the off chance I would check/test my disk and memory.
chkdsk for the drive(s)
and a mem test tool is built into most motherboards these days, or you can download one.

I know you didn't necessarily get a mem error, but as a not-so-inexperienced developer
I have to say it crossed my mind ;^)
Just my two-sense ;^p

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial