Slow data retrieval which requires improvement..plz help

joshtheflame · December 3, 2009

I am working on a Help Desk Ticketing system and have a page called MY TICKETS which shows all tickets in grids. the problem is that the number of records are around 30 to 40 but there is a lag around more then 10 seconds while populating the grid. I think its the way I've written. So I need suggestion to improve or correct my problems if any. Please help..here is the code

MyTickets.php

<?php
session_start();
if (!isset($_SESSION['uid']))
{
  header('location: login.php');
}
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>CRPL Support - My Tickets</title>
<link rel="stylesheet" href="css/eticket.css" media="screen">
</head>
<!--<link rel="stylesheet" type="text/css" media="all" href="css/niceforms-default.css" /> -->
<script type="text/javascript" src="js/jquery-1.3.2.min.js"></script> 

<!--<script type="text/javascript" src="js/supportvalidations.js"></script> -->
<script type="text/javascript" src="js/testajax.js"></script>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>CRPL Support System</title>
</head>

<body>
<div class="container">
    <h1><img src="images/logosupport.gif" alt="crplTicket" width="222" height="59" border="0"></h1>			
    <div class="nav">
    <ul>
        <li>
  </li>
   		<li id="home"><a href="newticket.php"><img src="images/home.gif" border="0" alt="Main" />New Ticket </a></li>
       <li id="mytickets"><a href="mytickets.php"><img src="images/my_tickets.gif" border="0" alt="Main" /> My Tickets</a></li>
    </ul>
    </div>
    <div class="content">
  
    <center> </center>
   <div style="margin: 10px 0 60px 0;">
	 <table width="100%" border="0" cellspacing=0 cellpadding=0 align="center">
	    <tr>
           <td nowrap >
               <a href="javascript:filterOpen()"><img src="images/view_open_btn.gif" alt="View Open" border=0></a>            
               <a href="javascript:filterClosed()"><img src="images/view_closed_btn.gif" alt="View Closed" border=0></a>               <a href="javascript:nofilter()"><img src="images/refresh_btn.gif" alt="Refresh" border=0></a>
    		    </td>
	    </tr>
	  </table>

        <div id="grid_data">
        </div>

<div style="clear:both"></div> 
</div>
    <div class="pre-footer"><br />
      <br />
      <br />
  Support Ticket S</div>	
  <div class="footer"><table border="0" width="100%">

        <tr>
        <td><a href="http://www.cr-pl.com" target="_blank">Powered by CRPL</a>
          <form id="form1" name="form1" method="post" action="">
          <input type="hidden" name="vtype" id="vtype" value="G"/>
          
           <?php session_start();  
	      $mycus_idx = $_SESSION["uid"];  
		?> 
           <input type="hidden" name="mycusid" id="mycusid" value=<?php echo $mycus_idx ?> > 
          </form></td>
        <td align="right"> </td></tr>
    </table></div>
</div>
</div>
<br/>
<script>
   //call after page loaded
   window.onload=defaultgrid; 
</script>

</body>
</html>

As you can see at the bottom I've used a javascript which triggers at the end of the page..

defaultgrid javascript

-----------------------------

function defaultgrid()
{
var dataString = "mycusid="+document.getElementById('mycusid').value+"&dtype="+document.getElementById('vtype').value+"&sid="+Math.random();	
  $.ajax({
       type: "GET",                   //GET Method used instead of POST
       url: "buildgrid.php",       //php file that processes the data
       data: dataString,          //Pass the data
       cache: false,                 //Do not cache the page  
       success: function(returnString) 
      {
            document.getElementById('grid_data').innerHTML = returnString;
	  }
        });
}

Finally the code which actually populates the grid...

---------------------

buildgrid.php

---------------------

<?php
require_once('hostinfo.php');

// Connect to server and select databse.
$connection = mysql_connect($dbhost, $dbuser, $dbpass) or die("Error : Cannot connect to the database..");
mysql_select_db($dbname) or die("Error : Cannot select Database...");

//Display type
$vtype = $_GET['dtype'];
$pcusid = $_GET['mycusid'];

if($vtype == 'G')
{
//Default query 
$squery = "SELECT ticketno, c_id, city, department, subject, priority, status, ticketdate, messagebody from ticketmaster where c_id = '$pcusid' order by ticketdate desc";
}

if($vtype == 'O')
{
//Default query 
  $squery = "SELECT ticketno, c_id, city, department, subject, priority, status, ticketdate, messagebody from ticketmaster where c_id = '$pcusid' AND status='O' order by ticketdate desc";
}

if($vtype == 'C')
{
//Default query 
  $squery = "SELECT ticketno, c_id, city, department, subject, priority, status, ticketdate, messagebody from ticketmaster where c_id = '$pcusid' AND status='C' order by ticketdate desc";
}

if($vtype == 'D')
{
//Default query 
  $squery = "SELECT ticketno, c_id, city, department, subject, priority, status, ticketdate, messagebody from ticketmaster where c_id = '$pcusid' and department='S' order by ticketdate desc";
}
  //Execute Statement using mySQL database
  $result = mysql_query($squery, $connection) or die(mysql_error()); 
  $ticketRS = mysql_fetch_assoc($result);

  if($ticketRS["ticketno"] == "")
    {
     echo "<br/><br/> No ticket found ...";
 return;
} 
  echo "<table width='100%' border='0' cellspacing=0 cellpadding=2>";
  echo "<tr>";
  echo "<td>";
  echo "<table border='0' cellspacing=0 cellpadding=2 class='tgrid' align='center'>";
  echo "<tr>";
  echo "<th width='105' height='20' nowrap>";
//  echo "<a href='view.php?sort=ID&order=ASC&' title='Sort By Ticket ID ASC'>Ticket #</a></th>";
  echo "Ticket #</th>";
  echo "<th width='100'>";
//  echo "<a href='view.php?sort=date&order=ASC&' title='Sort By Date ASC'>Creation Date</a></th>";
  echo "Creation Date</th>";
  echo "<th width='60' align='center'>Status</th>";
  echo "<th width='240'>Subject</th>";
  echo "<th width='150' align='center'>";
//  echo "<a href='view.php?sort=dept&order=ASC&' title='Sort By Category ASC'>Department</a></th>";
  echo "Department</th>";
  echo "<th width='150'>Priority</th>";
  echo "<th width='150'>City</th>";
  do 
  {
     $theticket = "'" .$ticketRS["ticketno"] . "'";

 echo "<tr class='row1' id='496115'>";
     echo "<td align='center' title='" .$ticketRS["ticketno"]. "' nowrap>";
    // echo "<a class='Icon webTicket' href=javascript:viewticket(" .$ticketRS["ticketno"]. ",'Grid')>" .$ticketRS["ticketno"]."</a></td>";
  echo "<a class='Icon webTicket' href=javascript:viewticket(" .$theticket. ",'Grid')>" .$ticketRS["ticketno"]."</a></td>";
//	 echo "<a class='Icon webTicket' href='javascript:zahid()'>" .$ticketRS["ticketno"]."</a></td>";
     echo "<td nowrap> " .$ticketRS["ticketdate"]. "</td>";

     echo "<td> " ;
 if($ticketRS["status"] == "O") 
 { 
 echo "Open" ;
 } else 
 echo "Close"; 
 echo " </td>";
     echo "<td> " .$ticketRS["subject"] . " </td>";
     echo "<td align='center'> ";
 if($ticketRS["department"] == 'H') 
 { 
   echo "Hardware" ;
 }else 
   echo "Software"; echo " </td>";
     echo "<td align='center'>";
     if($ticketRS["priority"] == 'H') echo "High" ;  if($ticketRS["priority"] == 'N') echo "Normal" ;
 if($ticketRS["priority"] == 'L') echo "Low" ; echo "</td>";
 echo "<td align='center'>" .$ticketRS["city"] . "</td>";
     echo "</tr>";
  } while ($ticketRS = mysql_fetch_assoc($result)); 
   echo "</table> </td> </tr> <tr> <td> </table>";
?>

[attachment deleted by admin]

premiso · December 3, 2009

In your database is the c_id a primary key? If not set it to index, other than that try running that query on it's own and see how long it takes, if it still takes 10 seconds, well it is your SQL setup. If it takes way less (should take no more than 3 seconds) the issue is with the javascript retrieval. Narrow it down that way and post your results so we can better help you.

joshtheflame · December 3, 2009

permiso thank you for the reply. Well I have executed the following query.

select * from ticketmaster on my host and it retrieved all 123 rows in 1297 mili seconds keep this in mind that in my tickets i am retrieving few records of a particular customer. so its not my database setup for sure and yes c_id is already indexed so its not an indexing issue as well.

Now what about javascript...what should I do with javascript? should I write that buildgrid php code directly inside the mytickets page rather call through javascript??

Sign In

Slow data retrieval which requires improvement..plz help

Recommended Posts

joshtheflame

Link to comment

Share on other sites

premiso

Link to comment

Share on other sites

joshtheflame

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information