Hello,
I have a MySQL query that returns about 3000 rows of 8 fields from a 120,000 record database. The necessary fields are indexed. If I run the query without fetching the result rows, the page loads in a second or two. If I do something like this:
while ($row = mysql_fetch_array($qry))
echo '<tr><td>'.$row[0].'</td><td>'.$row[1].'</td><td>'.$row[2].'</td><td>'.$row[3].'</td><td>'.$row[4].'</td><td>'.$row[5].'</td><td>'.$row[6].'</td><td>'.$row[7].'</td></tr>';
..the page loads in about 3-4 sexconds. However, I'm wanting to echo something like the above, but with an input tag in each td to make the result set live and updateable. This makes the page load take almost 2 minutes. The idea works but may not be feasible, because all of those input tags seem to be what slows the page down, unless there is a way to speed up fetching the result rows, or perhaps another idea entirely? I tried using an unbuffered query with little if any benefit. Any ideas?
Thanks in advance
Jeremy
fast MsSQL query but slow mysql_fetch_array
Moderator: General Moderators
Re: fast MsSQL query but slow mysql_fetch_array
I can't imagine it takes php too long to generate the HTML for an input box. It must be the browser having to draw all of them. If thats the case I don't see anyway to speed that up. However, you could make it so input boxes are only drawn if they need to be using javascript. You could create an "edit" button in each row that will create the input boxes. Drawing 8 input boxes should be much faster than drawing 24000 of them.
You could also reduce the amount of rows being queried at one time. Check out pagination using LIMIT.
You could also reduce the amount of rows being queried at one time. Check out pagination using LIMIT.
Re: fast MsSQL query but slow mysql_fetch_array
Thanks. I didn't really want to get into pagination. But yeah, the edit button for an individual row instead of making the whole result live is a good idea. I can justify that thinking since it forces the user to make a decision to make a change rather than the probability of inadvertent/ accidental changes. i'll try that.
Re: fast MsSQL query but slow mysql_fetch_array
Rendering 3000 inputs takes about half a second on my local server, including the browser rendering. PHP indicates it took it 276ms to complete its part. It will probably be faster on a production server.
Post your code and we'll try to see where the problem is - buy yeah, I'd recommend pagination as well.
Post your code and we'll try to see where the problem is - buy yeah, I'd recommend pagination as well.
Re: fast MsSQL query but slow mysql_fetch_array
Well I just wanted to stay away from pagination because there is sortability and I it always seems different people want different osrting rules, like either sorting just the records on the page or sorting the whole recordset.
Anyway, the difference with input tags to witohut is ridiculous, as I ste in the code below. Let me know if you see something, and thanks for your interest.
Anyway, the difference with input tags to witohut is ridiculous, as I ste in the code below. Let me know if you see something, and thanks for your interest.
Code: Select all
foreach ($_GET as $name => $value)
{
$nss = substr($name,0,2); if ($nss == 'D_') { if ($whr_d > '') $whr_d .= "OR date_worked='".adodb_date('Y-m-d',$value)."'"; else $whr_d .= "date_worked='".adodb_date('Y-m-d',$value)."'";}
elseif ($nss == 'W_') { if ($whr_w > '') $whr_w .= "OR wc_sched='".$value."'"; else $whr_w .= "wc_sched='".$value."'";}
elseif ($nss == 'E_') { if ($whr_e > '') $whr_e .= "OR empl_no='".$value."'"; else $whr_e .= "empl_no='".$value."'";}elseif ($name == 'clk_sort') $ob = $value;
}
if ($whr_d > '') $whr .= " AND (".$whr_d.")"; if ($whr_w > '') $whr .= " AND (".$whr_w.")"; if ($whr_e > '') $whr .= " AND (".$whr_e.")";
if ($whr > '')
{
include '/var/www/Connections/DB_Open_Connections.php'; if($ob == '') $ob = 'date_worked'; $sob = str_replace(' DESC','',$ob); $clss = "table_h_on"; $i=0;
$sql = "SELECT spt_emplhours.ID, empl_no, job_no, step_no, DATE_FORMAT(time_in,'%l:%i %p') AS time_in2, DATE_FORMAT(time_out,'%l:%i %p') AS time_out2, DATE_FORMAT(date_worked,'%c/%e/%Y') AS date_worked2,
CASE WHEN insu=-1 THEN 'Yes' ELSE 'No' END AS insu2, wc, hrs_total FROM spt_emplhours LEFT JOIN spt_wcmastr ON spt_emplhours.wc = spt_wcmastr.wc_rte WHERE hrs_type='02'".$whr.' ORDER BY '.$ob; $qry = mysql_query($sql);
$rws=mysql_num_rows($qry);
while ($row = mysql_fetch_array($qry))
{
if($temp > '' && $temp != strtoupper($row[$sob]))
{echo '</table><hr/><img class="plh" src="http://lambda/SPT/images/SPlogo_s.jpg"/><div class="plh">'; include'/var/www/SPT/lists/clock_list_h.php'; echo '</div><table cellspacing="0" id="tblm" class="td_list">';}
/*
//This takes 3-4 minutes
echo '<tr id="r'.$row['ID'].'" class="'.$clss.'">
<td class="lft"><input style="width: 80px;" id="g'.$i++.'" name="empl_no" class="c" onfocus="foc=this; row='.$row['ID'].'; gots(\'r\'+'.$row['ID'].');" onchange="foc2=this; el_chk(this);" value="'.$row['empl_no'].'" /></td>
<td><input style="width: 60px;" id="g'.$i++.'" name="job_no" class="c" onfocus="foc=this; row='.$row['ID'].'; gots(\'r\'+'.$row['ID'].');" onchange="foc2=this; el_chk(this);" value="'.$row['job_no'].'" /></td>
<td><input style="width: 48px;" id="g'.$i++.'" name="step_no" class="c" onfocus="foc=this; row='.$row['ID'].'; gots(\'r\'+'.$row['ID'].');" onchange="foc2=this; el_chk(this);" value="'.$row['step_no'].'" /></td>
<td><input style="width: 80px;" id="g'.$i++.'" name="time_in" class="c" onfocus="foc=this; row='.$row['ID'].'; gots(\'r\'+'.$row['ID'].');" onchange="foc2=this; tm_chk(this);" value="'.$row['time_in2'].'" /></td>
<td><input style="width: 80px;" id="g'.$i++.'" name="time_out" class="c" onfocus="foc=this; row='.$row['ID'].'; gots(\'r\'+'.$row['ID'].');" onchange="foc2=this; tm_chk(this);" value="'.$row['time_out2'].'" /></td>
<td><input style="width: 80px;" id="g'.$i++.'" name="date_worked" class="c" onfocus="foc=this; row='.$row['ID'].'; gots(\'r\'+'.$row['ID'].');" onchange="foc2=this; dt_chk(this);" value="'.$row['date_worked2'].'" /></td>
<td><input style="width: 70px;" id="g'.$i++.'" name="insu" class="c" onfocus="foc=this; row='.$row['ID'].'; gots(\'r\'+'.$row['ID'].');" onchange="foc2=this; el_chk(this);" value="'.$row['insu2'].'" /></td>
<td><input style="width: 80px;" id="g'.$i++.'" name="wc" class="c" onfocus="foc=this; row='.$row['ID'].'; gots(\'r\'+'.$row['ID'].');" onchange="foc2=this; el_chk(this);" value="'.$row['wc'].'" /></td>
<td align="right" style="width: 60px;" id="g'.$i++.'" onclick="row='.$row['ID'].'; gots(\'r\'+'.$row['ID'].');"> '.$row['hrs_total'].'</td></tr>';
*/
//This takes 2 seconds
echo '<tr id="r'.$row['ID'].'" class="'.$clss.'">
<td class="lft">'.$row['empl_no'].'</td>
<td>'.$row['job_no'].'</td>
<td>'.$row['step_no'].'</td>
<td>'.$row['time_in2'].'</td>
<td>'.$row['time_out2'].'</td>
<td>'.$row['date_worked2'].'</td>
<td>'.$row['insu2'].'</td>
<td>'.$row['wc'].'</td>
<td align="right" style="width: 60px;" id="g'.$i++.'" onclick="row='.$row['ID'].'; gots(\'r\'+'.$row['ID'].');"> '.$row['hrs_total'].'</td></tr>';
if ($clss == 'table_h_off') $clss = 'table_h_on'; else $clss = 'table_h_off'; $temp = strtoupper($row[$sob]);
}
include '/var/www/Connections/DB_Close_Connections.php';
}
echo '</table>';
Re: fast MsSQL query but slow mysql_fetch_array
I see. So it's actually not 3000 inputs but 3000 rows of 8 inputs, which comes to 24000 inputs. That is quite a difference. My browser is having a tough rendering that, especially since it is in a table (tables require more resources to render). There are some things you can improve in your code, but generally speaking you should really move to pagination + search. You can't expect someone to go over 24000 inputs in one page do you? 3000 sounded excessive, but this is really an overkill.
Re: fast MsSQL query but slow mysql_fetch_array
Your problem sounds like a perfect reason to use dataTables jquery plugin.
http://www.datatables.net/
It applies sorting and filtering quite easily with a large dataset.
http://www.datatables.net/
It applies sorting and filtering quite easily with a large dataset.