fast MsSQL query but slow mysql_fetch_array

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
jsdegard
Forum Newbie
Posts: 3
Joined: Thu Jun 17, 2010 10:01 am

fast MsSQL query but slow mysql_fetch_array

Post by jsdegard »

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
Kurby
Forum Commoner
Posts: 63
Joined: Tue Feb 23, 2010 10:51 am

Re: fast MsSQL query but slow mysql_fetch_array

Post by Kurby »

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.
jsdegard
Forum Newbie
Posts: 3
Joined: Thu Jun 17, 2010 10:01 am

Re: fast MsSQL query but slow mysql_fetch_array

Post by jsdegard »

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.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: fast MsSQL query but slow mysql_fetch_array

Post by Eran »

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.
jsdegard
Forum Newbie
Posts: 3
Joined: Thu Jun 17, 2010 10:01 am

Re: fast MsSQL query but slow mysql_fetch_array

Post by jsdegard »

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.

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'].');">&nbsp;'.$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'].');">&nbsp;'.$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>';
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: fast MsSQL query but slow mysql_fetch_array

Post by Eran »

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.
Kurby
Forum Commoner
Posts: 63
Joined: Tue Feb 23, 2010 10:51 am

Re: fast MsSQL query but slow mysql_fetch_array

Post by Kurby »

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.
Post Reply