Script running slow - Improvements!

[fRAiLtY-]

Hi guys,

 

I wrote this script and whilst it works and does what I require it executes quite slowly, dependant on the length of the while loop.

 

Is there anyway I can clean up and improve the page load time of my script?

 

Cheers!

 

<script type="text/javascript" src="../js/formvalidation.js" ></script>

<table class="data" style="width:962px;margin:8px auto;">
<tr class="theader">
	<td>Sales for <?php echo $_GET['user']; ?>
	</td>
</tr>
<tr>
	<td style="font-size:13px;">
<?php

// Connect to Tharstern
require('../includes/thar.php');

// Get today timestamp
$date = date('Y-m-d');
$today = explode('-',$date);

// Get sales information
$todayssales = sqlsrv_query($db,"SELECT * FROM MainJobDetails
								 WHERE InvoiceCustomerName = '" . $_GET['user'] . "' 
								 AND DATEPART(yyyy,CreateDateTime) = '$today[0]' 
								 AND DATEPART(mm,CreateDateTime) = '$today[1]' 
								 AND DATEPART(dd,CreateDateTime) = '$today[2]'",
								 array(), array( "Scrollable" => 'keyset' ))
	or die(print_r(sqlsrv_errors(), TRUE));

$thismonthsales = sqlsrv_query($db,"SELECT * FROM MainJobDetails 
								 WHERE InvoiceCustomerName = '" . $_GET['user'] . "' 
								 AND DATEPART(yyyy,CreateDateTime) = '$today[0]' 
								 AND DATEPART(mm,CreateDateTime) = '$today[1]'",
								 array(), array( "Scrollable" => 'keyset' ))
	or die(print_r(sqlsrv_errors(), TRUE));

$monthly = sqlsrv_query($db,"SELECT YEAR(CreateDateTime) AS 'Year', MONTH(CreateDateTime) AS 'Month'
								 FROM MainJobDetails 
								 WHERE InvoiceCustomerName = '" . $_GET['user'] . "'
								 GROUP BY YEAR(CreateDateTime), MONTH(CreateDateTime)
								 ORDER BY YEAR(CreateDateTime), MONTH(CreateDateTime)",
								 array(), array( "Scrollable" => 'keyset' ))
	or die(print_r(sqlsrv_errors(), TRUE));

// Set some variables
$todayssalescount = sqlsrv_num_rows($todayssales);
$thismonthcount = sqlsrv_num_rows($thismonthsales);
$dailytotal = "0.00";
$thismonthtotal = "0.00";


while($row = sqlsrv_fetch_array($todayssales)) { 
	$dailytotal += $row['PriceEst']; 
}

while($row = sqlsrv_fetch_array($thismonthsales)) { 
	$thismonthtotal += $row['PriceEst']; 
}

?>
		<div id="salestoday"><br />

<?php 

// Get customer start date
$renewal = sqlsrv_query($db,"SELECT * FROM Customers WHERE Name ='" . $_GET['user'] . "' AND Code = '" . $_GET['code'] . "'", array(), array( "Scrollable" => 'keyset' ));
$row = sqlsrv_fetch_array($renewal);

// Set start and now variables
$start = strtotime(date_format($row['CreatedDate'], 'N F Y'));
$now = time();

        $monthlytotals = array();
        $monthaxis = array();
  
        while ($now > $start) {
            $monthlytotal = 0;            
            $query = "SELECT * FROM MainJobDetails
                      WHERE InvoiceCustomerName = ? AND DATEPART(yyyy, CreateDateTime) = ? AND DATEPART(mm, CreateDateTime) = ?";
            $monthlysales = sqlsrv_query($db, $query, array($_GET['user'], date('Y', $now), date('m', $now)), array('Scrollable' => 'keyset'))
                            or die(print_r(sqlsrv_errors(), TRUE));
            while ($row = sqlsrv_fetch_array($monthlysales)) {
                $monthlytotal += $row['PriceEst'];
            }
            $monthlytotals[] = $monthlytotal;
            $monthaxis[] = date("F Y", $now);
    		
            $now = strtotime('-1 month', $now);
        }

?>
			<img src="http://chart.apis.google.com/chart?cht=lc&chs=910x250&chm=B,FAF2E6,0,0,0&chds=0,10000&chd=t:<?php print_r(implode(",", array_reverse($monthlytotals))); ?>&chxt=y,x&chxl=0:|0|1000|2000|3000|4000|5000|6000|7000|8000|9000|100000|1:|<?php print_r(implode("|", array_reverse($monthaxis))); ?>&chg=20,50,1,5" />
			<div class="salestodayfigures">Orders Today:<br /><span class="salesfigure"><?php echo $todayssalescount; ?></span>
			</div>
			<div class="salestodayfigures">Today's Order Total:<br /><span class="salesfigure"><?php echo "£" . number_format($dailytotal, 2); ?></span>
			</div>
			<div class="salestodayfigures">So far this month:<br /><span class="salesfigure"><?php echo "£" . number_format($thismonthtotal, 2); ?></span>
			</div>
			<div class="salestodayfigures">Average order value<br /><span class="salesfigure">
<?php 

				if($thismonthcount == '0') {
					echo "£0.00";
				} else {
					echo "£" . number_format($thismonthtotal / $thismonthcount, 2);
				}
?>
				</span>
			</div>
		</div><br />
<?php

// Get customer start date
$renewal = sqlsrv_query($db,"SELECT * FROM Customers WHERE Name ='" . $_GET['user'] . "' AND Code = '" . $_GET['code'] . "'", array(), array( "Scrollable" => 'keyset' ));
$row = sqlsrv_fetch_array($renewal);

// Set start and now variables
$start = strtotime(date_format($row['CreatedDate'], 'N F Y'));
$now = time();

// Alt row colors
$c = 0;

// Create table
echo "<table class='data'>
		<tr class='theader'>
			<td>Month</td>
			<td>Total Orders</td>
			<td>Average Order Value</td>
			<td>Monthly Total</td>
		</tr>";    
while ($now > $start) {
	$class = $c++ % 2 == 1 ? "odd" : "even";
	$monthlytotal = 0;            
	$query = "SELECT * FROM MainJobDetails
			  WHERE InvoiceCustomerName = ? AND DATEPART(yyyy, CreateDateTime) = ? AND DATEPART(mm, CreateDateTime) = ?";
	$monthlysales = sqlsrv_query($db, $query, array($_GET['user'], date('Y', $now), date('m', $now)), array('Scrollable' => 'keyset'));

	while ($row = sqlsrv_fetch_array($monthlysales)) {
		$monthlytotal += $row['PriceEst'];
	}
	echo "<tr class='$class'>
			<td>" . date("F Y", $now) . "</td>
			<td>" . sqlsrv_num_rows($monthlysales) . "</td>";
	if(sqlsrv_num_rows($monthlysales) == '0') {
	  echo "<td>£0.00</td>";
	} else {
	echo    "<td>£" . number_format($monthlytotal / sqlsrv_num_rows($monthlysales) , 2) . "</td>";
	}
	echo    "<td>£" . number_format($monthlytotal, 2) . "</td>
		  </tr>";
	$now = strtotime('-1 month', $now);
}
echo "</table>";


?>
	</td>
</tr>
</table>

[phpSensei]

Have a read here

 

http://forge.mysql.com/wiki/Top10SQLPerformanceTips