How can I speed up my query?
Posted: Fri Oct 03, 2008 2:35 pm
I have a table that holds all of our products and the upcoming availability of each product. It is currently laid out with the following columns:
ProductID---WeekDate---ProductAvailability
We forecast around15 weeks of availability so that means for each product we are creating 15 rows. It is extremely slow to load and edit. Here is the cut down version of the current code:
I have two questions.
First, would it be smarter to lay the table out differently?
ProductID--_ProductAvailability--Week1---Week2---Week3---Week4--etc
That layout would mean that I would only have to connect with one statement and then report the weeks I am looking for instead of having to connect multiple times.
Secondly, am I going about the current query correctly? I need a "spreadsheet" type of layout for the office guys and this achieves it but, again, is very slow (I suspect because of multiple connections).
Thanks for the help,
Mick
ProductID---WeekDate---ProductAvailability
We forecast around15 weeks of availability so that means for each product we are creating 15 rows. It is extremely slow to load and edit. Here is the cut down version of the current code:
Code: Select all
$today=date("N")-1;
$mon=strtotime('-'.$today.' days');
$monformat=date("Y-m-d",$mon);
$baseweekorig=date("Y-m-d",strtotime("".$monformat." +0 week"));
$postamtweeks="8";
echo "<table border='1'><tr><td>Product Name</td>";
while ($i < $postamtweeks){
echo "<td>".date("Y-m-d",strtotime("".$baseweekorig." +".$i." week"));"</td>";
$i++;
}
echo "</tr>";
$resultprod=mysql_query("select ProductID, ProductName from product where ProdType='1' Order By ProductName");
while($rowprod=mysql_fetch_assoc($resultprod)){
$id=$rowprod['ProductID'];
echo "<tr><td>".$rowprod['ProductName']."</td>";
$j="0";
$Qty="0";
while ($j < $postamtweeks){
$baseweek=date("Y-m-d",strtotime("".$baseweekorig." +".$j." week"));
$result=mysql_query("select * from availability where UnrootedID='$id' and WeekDate = '$baseweek'");
while($row=mysql_fetch_assoc($result)){$Qty=$row['UnrootedAvailable'];}
echo "<td>".number_format($Qty)."</td>";
$j++;
}
echo "</tr>";
$Qty="0";
}
First, would it be smarter to lay the table out differently?
ProductID--_ProductAvailability--Week1---Week2---Week3---Week4--etc
That layout would mean that I would only have to connect with one statement and then report the weeks I am looking for instead of having to connect multiple times.
Secondly, am I going about the current query correctly? I need a "spreadsheet" type of layout for the office guys and this achieves it but, again, is very slow (I suspect because of multiple connections).
Thanks for the help,
Mick