Script Timing out

slightlyeskew

Hey All! This script I'm working on is driving me nuts...it continues to time out. I don't believe that I'm querying my DB near enough to make the script timed out when run through Firefox. I know the queries could be optimized but I'm not sure how to do that properly, combining multiple queries into one (I know the syntax for the query itself but not how to properly store it into an array to handle later). An FYI that might give you a better idea of how many rows i'm querying, count($destinationCertificate) is returning 56 at most. However the script has failed when count($destinationCertificate) is as low as 3.

	if (isset($_POST['shareholderID']) && (!($_POST['shareholderID'] == '')))
	{
	$getID[0] = $_POST['shareholderID'];
	}
	if(!(isset($currentFirstName[0])))
	{
	$getFirstNameNow = $db->queryOne("SELECT First_Name FROM ShareholderNames 
               WHERE ShareholderID = $getID[0]");

$getLastNameNow = $db->queryOne("SELECT Last_Name FROM ShareholderNames 
           WHERE ShareholderID = $getID[0]");
}

if(!(isset($currentEmail[0])))
{
$getEmailNow = $db->queryOne("SELECT Email_Address FROM   
            ShareholderNames WHERE ShareholderID = $getID[0]");
}
//*************************************************************************************************************************************//
	if ($_POST['summary_or_detail'] == 2)
	{
	$loopCounter = 0;$viewSummary = 1;$destCertCounter = 0;$firstLineCounter = 0;
	$destCerts = $db->query("SELECT Destination_Certificate FROM TransactionHistory 
             WHERE Destination_Shareholder = $getID[0] ORDER BY Transaction_Date ASC"); 
		 while($row = $destCerts->fetchRow(MDB2_FETCHMODE_ASSOC))
		{foreach($row as $key => $getDestCerts)
			{
			$destinationCertificate[$destCertCounter] = $getDestCerts;
			$destCertCounter++;
			}
		}
	$numberOfDestCerts = count($destinationCertificate);
	echo <<<_Detailed_Report_
	<table border="border">
	<tr>
	  <th>First Name</th>
	  <th>Last Name</th>
	  <th>Shareholder ID</th>
	  <th>Certificate ID</th>
	  <th>Purchase Date</th>
	  <th>Number of Shares Purchased</th>
	  <th>Purchase Price per Share</th>
	  <th>Sale Date</th>
	  <th>Number of Shares Sold</th>
	  <th>Sale Price per Share</th>
	  <th>Current Value</th>
	  <th>Transaction Notes</th>
	</tr>
_Detailed_Report_;


	while($loopcounter < $numberOfDestCerts)
	{
		if($destinationCertificate[$loopCounter] >= 107)
		{

		if($firstLineCounter == 0)
		{				
		echo<<<_First_Line_
		<tr>
		  <td>$getFirstNameNow</td>
		  <td>$getLastNameNow</td>
		  <td>$getID[0]</td>
		  <td></td>
		  <td></td>
		  <td></td>
		  <td></td>
		  <td></td>
		  <td></td>
		  <td></td>
		  <td></td>
		  <td></td>
		</tr>
_First_Line_;
			}	

			if($loopCounter < $numberOfDestCerts)
			{
$getTransID = $db->queryOne("SELECT TransactionID FROM TransactionHistory WHERE   

   Destination_Certificate = $destinationCertificate[$loopCounter]");
$getDestinationShareholder = $db->queryOne("SELECT Destination_Shareholder FROM 
    TransactionHistory WHERE TransactionID = $getTransID");
$getSourceShareholder = $db->queryOne("SELECT Source_Shareholder FROM 
   TransactionHistory WHERE TransactionID = $getTransID");
$purchaseDate = $db->queryOne("SELECT Transaction_Date FROM TransactionHistory 
   WHERE TransactionID = $getTransID");
$purchasePrice = $db->queryOne("SELECT Share_Price FROM TransactionHistory WHERE 
   TransactionID = $getTransID");
$numberOfShares = $db->queryOne("SELECT Number_Of_Shares FROM TransactionHistory 
   WHERE TransactionID = $getTransID");
$saleNumberCheckSplit = $db->queryOne("SELECT Number_Of_Shares FROM 
    TransactionHistory WHERE Source_Certificate = $destinationCertificate[$loopCounter]");
$saleDate = $db->queryOne("SELECT Transaction_Date FROM TransactionHistory WHERE 
    Source_Certificate = $destinationCertificate[$loopCounter]");
$salePrice = $db->queryOne("SELECT Share_Price FROM TransactionHistory WHERE 
    Source_Certificate = $destinationCertificate[$loopCounter]");				

			print<<<_HTML_While_
			<tr>
			  <td></td>
			  <td></td>
			  <td></td>
			  <td>$destinationCertificate[$loopCounter]</td>
			  <td>$purchaseDate</td>
			  <td>$numberOfShares</td>
			  <td>$purchasePrice</td>
			  <td>$saleDate</td>
			  <td>$saleNumberCheckSplit</td>
			  <td>$salePrice</td>
			</tr>
_HTML_While_;

			}
		$loopCounter++;
		$firstLineCounter++;
		}
	}	
echo<<<_END_Table_
</table>
_END_Table_;
	echo<<<_Email_Report_
	<form action="mailReport.php" method=POST>
	<input type=submit value="Email Report">
	</form>
_Email_Report_;
	echo<<<_View_Summary_
	<form action="SummaryOrDetailedReport.php method=POST>
	<input type=hidden name="summary_or_detail" value=1>
	<input type=submit value="View Summary Report">
	</form>
_View_Summary_;
	echo<<<_Back_
	<form action="ShareholderSelection.php" method=POST>
	<input type=hidden name="summary_or_detail" value=1>
	<input type=submit value="Back">
	</form>
_Back_;
	}

As always, thanks for any help you can give. Also, can anyone recommend a good IDE? I'm looking for something, preferably cheap and easy to hit the ground running with.

NogDog

You definitely want to get it down to as few queries as possible. Right now it looks like you do about 4 separate queries to start, the each iteration of that loop does 9 more. So even if you have just three iterations, that's over 30 queries. That probably in and of itself should not be enough to time out unless the database tables involved are not properly indexed and have lots of data (for some arbitrary amount of "lots").

At a quick glance, I suspect it could all be done with a single query with a JOIN on the tables involved. However, the specifics would take more time than I have right now to figure out your data design and the relevant query and PHP code.

slightlyeskew

Thanks for the response Nog. I took the following:

$getDestinationShareholder = $db->queryOne("SELECT Destination_Shareholder FROM
    TransactionHistory WHERE TransactionID = $getTransID");
$getSourceShareholder = $db->queryOne("SELECT Source_Shareholder FROM
   TransactionHistory WHERE TransactionID = $getTransID");
$purchaseDate = $db->queryOne("SELECT Transaction_Date FROM TransactionHistory
   WHERE TransactionID = $getTransID");
$purchasePrice = $db->queryOne("SELECT Share_Price FROM TransactionHistory WHERE
   TransactionID = $getTransID");
$numberOfShares = $db->queryOne("SELECT Number_Of_Shares FROM TransactionHistory
   WHERE TransactionID = $getTransID");
$saleNumberCheckSplit = $db->queryOne("SELECT Number_Of_Shares FROM
    TransactionHistory WHERE Source_Certificate = $destinationCertificate[$loopCounter]");
$saleDate = $db->queryOne("SELECT Transaction_Date FROM TransactionHistory WHERE
    Source_Certificate = $destinationCertificate[$loopCounter]");
$salePrice = $db->queryOne("SELECT Share_Price FROM TransactionHistory WHERE
    Source_Certificate = $destinationCertificate[$loopCounter]");

and compacted it to:

$getAll = $db->query("SELECT Destination_Shareholder,Source_Shareholder, Transaction_Date, 
					Share_Price,Number_Of_Shares FROM TransactionHistory WHERE TransactionID = $getTransID");
				$row1 = $getAll->fetchRow();

			$getAll2 = $db->query("SELECT Number_Of_Shares,Transaction_Date,Share_Price FROM TransactionHistory WHERE 	
				Source_Certificate = $destinationCertificate[$loopCounter]");
			$row2 = $getAll2->fetchRow();

This seems to be working fine; no timeouts. I'm not familiar with the JOIN command. When you have time I'd appreciate any example you could provide. Thanks again!

slightlyeskew

Hey All. So as I said above the script started running fine when I made the modifications...at least for queries where the number of entries in destinationCertificate[] was single digit. Halo made a suggestion in a related post that I tinker with the set_time_limit so that the larger queries wouldn't time out. Unfortunately I'm still taking an outrageous amount of time with my queries when the number of entries in destinationCertificate[] is large. The largest count for that array is 56 and the number is only going to get larger. Do you have any suggestions on what I can do to speed this script up? Thanks for your time.

igorek

HTML_While; is that a built in php function?

Kudose

igorek;10938601 wrote:
HTML_While; is that a built in php function?

No, the OP is using the HEREDOC syntax.

Kudose

Based on the revised SQL you posted, it seems you want the data if the TransactionID meets a specific ID OR if the Source_Certificate matches a value, so you should be able to combine your query pulling records on either criteria.

i.e.

SELECT Destination_Shareholder,Source_Shareholder, Transaction_Date, Share_Price,Number_Of_Shares FROM TransactionHistory WHERE TransactionID = $getTransID OR Source_Certificate = $destinationCertificate[$loopCounter]

JOINs are really easy, and you may have already used them before without knowing.

i.e.

SELECT columnA, columnB FROM tableA, tableB WHERE columnA = 1

MySQL optimizes your query and creates an INNER JOIN on the two tables

SELECT tableA.columnA, tableB.columnB FROM tableA INNER JOIN tableB WHERE tableA.columnA = 1