"caching" a report then resort it...
Moderator: General Moderators
"caching" a report then resort it...
I have a report that I have created here which can take well over 10 minutes to run (loads of data), I then output my data into an html table with 3 different columns (school name, student name, student PIN). I want to be able to have my column headers "sort-by" links which would allow my users to click them, then repull the data sorted by whatever they've specified. Problem should be obvious, I dont' want them to have to wait another 10 minutes just for the resort.
I'm thinking there's got to be a way to just put all of my results from the previous run into some sort of array (cached) so that if they click a sort link, it just grabs the stored info and repopulates my html table (reordered).
any suggestions?
thanks,
Burrito
I'm thinking there's got to be a way to just put all of my results from the previous run into some sort of array (cached) so that if they click a sort link, it just grabs the stored info and repopulates my html table (reordered).
any suggestions?
thanks,
Burrito
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
All of the original "raw" data is stored in a mysql db.
the reason it takes so long to run the report is, I first have to grab all of the student names, then loop over all of their courses, then loop over all of the assignments in that course, then loop over all of the grades within those courses.
some students can be enrolled in 25 courses which have between 30 and 50 assignments / course. I then have to figure out their grades for those assignements to determine whether they've "passed" the course or not....long story short, it takes forfriggin' ever to run it and I'd like to just be able to save the data once it's been run (don't want to use a temp table if I can avoid it), and then use the "cached" data for a re-sort if they so desire.
I've already got a pagination set up to only show 25 students at a time (as well as a student list by name (with pagination))...still very very long.
the reason it takes so long to run the report is, I first have to grab all of the student names, then loop over all of their courses, then loop over all of the assignments in that course, then loop over all of the grades within those courses.
some students can be enrolled in 25 courses which have between 30 and 50 assignments / course. I then have to figure out their grades for those assignements to determine whether they've "passed" the course or not....long story short, it takes forfriggin' ever to run it and I'd like to just be able to save the data once it's been run (don't want to use a temp table if I can avoid it), and then use the "cached" data for a re-sort if they so desire.
I've already got a pagination set up to only show 25 students at a time (as well as a student list by name (with pagination))...still very very long.
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
- SystemWisdom
- Forum Commoner
- Posts: 69
- Joined: Sat Mar 26, 2005 5:54 pm
- Location: A Canadian South of the 49th Parallel
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
- SystemWisdom
- Forum Commoner
- Posts: 69
- Joined: Sat Mar 26, 2005 5:54 pm
- Location: A Canadian South of the 49th Parallel
Instruct them to enable it, not hard.. if they dont, then they dont get to re-sort the data.
Especially since his target audience is school of students (or teachers?), then they can be Instructed to have JS enabled for the system to work. Students can learn, teachers can teach!
Besides, almost all browsers have support for JS, and many popular websites use JS, so people who disable JS are only limiting themselves, their choice!
For the developer it is merely a trade-off:
Less Server Load for Enforced Javascript usage.
When a single request can take up to 10 minutes, the trade-off seems more than reasonable..
Especially since his target audience is school of students (or teachers?), then they can be Instructed to have JS enabled for the system to work. Students can learn, teachers can teach!
Besides, almost all browsers have support for JS, and many popular websites use JS, so people who disable JS are only limiting themselves, their choice!
For the developer it is merely a trade-off:
Less Server Load for Enforced Javascript usage.
When a single request can take up to 10 minutes, the trade-off seems more than reasonable..
in your sql statement, how are you getting your records? are you querying and retrieving every single record? cuz that is what it sounds like to me, thus why it takes so long.
in other words, why not query it to only grab the first 25 records, or 50, or however many they have specified to display per page?
if you are doing it this way, it should not take that long to query.. and if it is, post the code
in other words, why not query it to only grab the first 25 records, or 50, or however many they have specified to display per page?
if you are doing it this way, it should not take that long to query.. and if it is, post the code
here you go...
Code: Select all
<?
error_reporting(E_ALL);
if(!isset($_GET['st'])){
$start = 0;
} else {
$start = $_GET['st'];
}
if(isset($_GET['let'])){
$nlet = $_GET['let'];
}else{
$nlet = "";
}
set_time_limit(0);
mysql_connect(DB_HOST,DB_USER,DB_PASSWORD)
or die(mysql_error());;
//SELECT g.id, CONCAT(c.DistrictID, '|',c.RegionID, '|',c.SchoolID) AS DRS, CONCAT(g.PIN, ' - ', c.Name) AS PINClName FROM NewRegSys.Groups g, NewRegSys.Clients c WHERE c.id = g.ClientID ORDER By c.Name
$getdists = mysql_query("SELECT g.id, c.DistrictID, c.Name AS PINClName FROM NewRegSys.Groups g, NewRegSys.Clients c WHERE c.id = g.ClientID ORDER By c.Name")
or die(mysql_error());
if(isset($_GET['gott'])){
$s_sid = ($_GET['sid'] !== "%" ? " and u.SchoolID = ".$_GET['sid'] : "");
$f_Did = $_GET['did'];
$getdisid = mysql_query("SELECT id, Name FROM NewRegSys.Clients WHERE DistrictID = $f_Did")
or die(mysql_error());
$gtdisid = mysql_fetch_assoc($getdisid);
$f_Cid = $gtdisid['id'];
$getstustot = mysql_query("select concat(u.fname,' ',u.lname) as name, u.BbID, sc.Name from NewRegSys.Users u, NewRegSys.Schools sc where u.SchoolID = sc.id and u.ClientId = $f_Cid".($nlet !== "" ? " and u.fname like '".$nlet."%'" : "") ."$s_sid order by name")
or die("query one".mysql_error());
$getstusnum = mysql_num_rows($getstustot);
$getstus = mysql_query("select concat(u.fname,' ',u.lname) as name, u.BbID, sc.Name from NewRegSys.Users u, NewRegSys.Schools sc where u.SchoolID = sc.id and u.ClientId = $f_Cid".($nlet !== "" ? " and u.fname like '".$nlet."%'" : "") ."$s_sid order by name limit $start, 25")
or die("query two".mysql_error());
} // end if for gott set...burrito
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Student Report By School District</title>
<style>
td
{
font-family:tahoma; font-size:9.0pt; color:00000f
}
</style>
</head>
<body>
<form name="MyForm" action="student-report.php">
<table width="400">
<tr>
<td>Select District</td>
</tr>
<tr>
<td><select name="did" onChange="location='student-report.php?did='+this.value">
<?while($gtdists = mysql_fetch_assoc($getdists)){?>
<option value="<?=$gtdists['DistrictID'];?>" <?if(isset($_GET['did']) && $_GET['did'] == $gtdists['DistrictID']) echo "selected";?>><?=$gtdists['PINClName'];?></option>
<?} // end while for result set...burrito ?>
</select></td>
</tr>
<?if(isset($_GET['did'])){ ?>
<tr>
<td>
Select School
</td>
</tr>
<?
$getschools = mysql_query("SELECT s.id, s.Name FROM NewRegSys.Regions r INNER JOIN NewRegSys.Schools s ON s.RegionID = r.id WHERE r.DistrictID = ".$_GET['did']." and s.Name <> 'All' order by s.Name")
or die(mysql_error());
?>
<tr>
<td><select name="sid">
<option value="%">All</option>
<?while($gtschools = mysql_fetch_assoc($getschools)){?>
<option value="<?=$gtschools['id'];?>" <?if(isset($_GET['sid']) && $_GET['sid'] == $gtschools['id']) echo "selected";?>><?=$gtschools['Name'];?></option>
<?} // end while for result set...burrito ?>
</select></td>
</tr>
<tr>
<td><input type="submit" value="Run Report"></td>
</tr>
<?} // end if for did set...burrito?>
</table>
<input type="hidden" name="gott" value="1">
</form>
<?if(isset($_GET['gott'])){ ?>
<table border="1" cellspacing="0" bordercolor="#00000f">
<tr>
<?$getcourses = mysql_query("select c.title,a.course_id from NewRegSys.Users u, atutor.AT_course_enrollment a, atutor.AT_courses c where u.ClientID = $f_Cid and a.member_id = u.BbID and a.course_id = c.course_id group by c.title")
or die("query three".mysql_error());
$numcrs = mysql_num_rows($getcourses);
echo "<td colspan=\"".($numcrs + 2)."\">";
if(isset($_GET['st']) && $_GET['st'] != 0) echo "<a href=\"student-report.php?".(isset($_GET['sid']) ? "sid=".$_GET['sid']."&" : "")."gott=1&did=".$_GET['did']."&st=".($_GET['st'] - 25).(isset($_GET['let']) ? "&let=".$_GET['let'] : "")."\">«Previous</a> | ";?>
<?if($getstusnum > 25){ ?>
<a href="student-report.php?<?=(isset($_GET['sid']) ? "sid=".$_GET['sid']."&" : "");?>gott=1&did=<?=$_GET['did'];?>&st=<?=(isset($_GET['st']) ? $_GET['st'] + 25 : "25").(isset($_GET['let']) ? "&let=".$_GET['let'] : "");?>">Next »</a>
<?} // end if for less than 25 records...burrito
$end = (($start+24) >= $getstusnum ? $getstusnum : ($start+24));?>
Records: <?=($start + 1)."-".$end." of ".$getstusnum;?></td>
</tr>
<?
?>
<tr>
<td>School</td>
<td>Student Name</td>
<?while($gtcourses = mysql_fetch_assoc($getcourses)){
$tit = explode(" ",$gtcourses['title']);
?>
<td><?=$tit[0];?></td>
<?} // end while for result set...burrito ?>
</tr>
<?
while($gtstus = mysql_fetch_assoc($getstus)){
echo "<tr><td>".$gtstus['Name']."</td><td>".$gtstus['name']."</td>";
mysql_data_seek($getcourses,0);
while($gtcourses = mysql_fetch_assoc($getcourses)){
$getenrollment = mysql_query("select course_id from atutor.AT_course_enrollment where member_id = ".$gtstus['BbID']." and course_id = ".$gtcourses['course_id'])
or die(mysql_error());
if($gtenrollment = mysql_fetch_assoc($getenrollment)){
$getassign = mysql_query("SELECT * FROM atutor.AT_assignments WHERE course_id = ".$gtcourses['course_id'])
or die("query four".mysql_error());
while($gtassign = mysql_fetch_assoc($getassign)){
$getgrades = mysql_query("SELECT grade FROM atutor.AT_grades WHERE assignment_id = ".$gtassign['id']." AND member_id = ".$gtstus['BbID'])
or die("query five".mysql_error());
$thisgrade = "-";
$flag = FALSE;
while($gtgrades = mysql_fetch_assoc($getgrades)){
if($gtgrades['grade'] < 4 || $gtgrades['grade'] == ""){
$flag = TRUE;
} // end if for first lesson failure or lesson not done...burrito
if($flag && ($gtgrades['grade'] < 4 || $gtgrades['grade'] == "")){
$thisgrade = "E";
break;
} // end if for haven't finished the course...burrito
if(strpos($gtassign['assignment_title'],"Post Test") !== FALSE){
if($gtgrades['grade'] < 85){
$thisgrade = "E";
break;
}
} // end if for post test assignment...burrito
$thisgrade = "C";
}
} // end while for result set...burrito
} else {// end if for students enrolled in class...burrito
$thisgrade = "-";
}
echo "<td align=\"center\">".$thisgrade."</td>";
} // end while for result set...burrito
echo "</tr>";
} // end while loop for result set...burrito ?>
</table>
<? $lets = array("a","b","c","d","e","f","g","h","i","j","k","l","m","n","o","p","q","r","s","t","u","v","w","x","y","z");
echo "<div align=\"center\">";
foreach($lets as $let){
$ulet = strtoupper($let);
echo "<a href=\"student-report.php?".(isset($_GET['sid']) ? "sid=".$_GET['sid']."&" : "")."did=".$_GET['did']."&let=".$let."&gott=1\">".$ulet."</a> ".($let == "l" ? "<br>" : ($let !== "z" ? " | " : "<br><a href=\"student-report.php?".(isset($_GET['sid']) ? "sid=".$_GET['sid']."&" : "")."did=".$_GET['did']."&gott=1\">All</a>"));
}
echo "</div>";
} // end if for gott set...burrito
?>
</body>
</html>Your sql statements are what's causing the lag then cuz you are retrieving every single record that matches any query you are throwing at it, dumping them into an array, and then posting the ones you need.
This, most definately, will cause lag when dealing with large tables...
You may want to look into using LIMIT. This will reduce the amount of php code you are using, and also speed up your overall query drastically. Typical syntax is :
You can find more on LIMIT in the mysql doc included with your mysql distro, or the [url=ttp://dev.mysql.com/doc/mysql/en/select.html]Online MySQL Manual[/url]. If you use the online manual, you'll have to scroll down to LIMIT.
This, most definately, will cause lag when dealing with large tables...
You may want to look into using LIMIT. This will reduce the amount of php code you are using, and also speed up your overall query drastically. Typical syntax is :
Code: Select all
mysql_query("SELECT * FROM table LIMIT 5,10;");I'm already using limit to limit the number of students per load (at present 25, see the top of the code I posted).
the problem comes in when I have to loop over the courses for which the are signed up, then loop over the assignments in those courses, and finally loop over the grades to determine whether or not they've passed the course...I can't limit any of this stuff. As I said in a previous post, I think all of the queries are necessary evils but someone asked for my code, so I posted. thought maybe they could help speed things up with a "join" or something that I may have missed (as again, someone above suggested).
at this point, I'm thinking I"ll go with SW's idea and just throw it into a JS array, thansk for helping everyone.
Burr
the problem comes in when I have to loop over the courses for which the are signed up, then loop over the assignments in those courses, and finally loop over the grades to determine whether or not they've passed the course...I can't limit any of this stuff. As I said in a previous post, I think all of the queries are necessary evils but someone asked for my code, so I posted. thought maybe they could help speed things up with a "join" or something that I may have missed (as again, someone above suggested).
at this point, I'm thinking I"ll go with SW's idea and just throw it into a JS array, thansk for helping everyone.
Burr
Sorry about that, I missed it. Was kinda speeding through them anyways (@work right now..). Anyways, there still should be no reason for it to take 10 minutes each time you do this. No reason at all. but SW's idea will work, though I'm not exactly sure that's the best method... Will have to look at it when I get home.