Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6

Thread: performance

  1. #1
    Regular Coder
    Join Date
    Nov 2006
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    performance

    Just wondering how long it should take to run this query? The server is fast, dual cpu xeon 2ghz 4 gig of ram fast raid0+1 HD's
    Each table has about 6,000 records with the full query pulling in about 10,000 records.
    I have the result paginated to 25 results. It takes around 3-5 seconds to display each page.

    $query = "SELECT company.CompanyName,
    contact.Name,
    contact.Email,
    branch.Address,
    branch.City,
    branch.Province,
    branch.PostalCode,
    branch.Phone,
    contact.Cell,
    branch.Fax,
    company.CompanyID,
    branch.BranchID,
    contact.ContactID,
    credit.Status
    FROM soileng.branch branch
    LEFT OUTER JOIN soileng.contact contact ON branch.BranchID=contact.ParentBranchID
    LEFT OUTER JOIN soileng.company company ON branch.ParentCompanyID=company.CompanyID
    LEFT OUTER JOIN soileng.credit credit ON company.CompanyID=credit.CompanyID
    WHERE $searched LIKE '%$trimmed%'
    ORDER BY $sorting ";

  • #2
    Regular Coder
    Join Date
    Nov 2006
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts
    when I run the same query through MySQL control center it takes 0.28 sec to retrieve 1000 results.

  • #3
    Senior Coder
    Join Date
    Aug 2003
    Location
    One step ahead of you.
    Posts
    2,815
    Thanks
    0
    Thanked 3 Times in 3 Posts
    A miracle?
    You could time the query in PHP and see how much does the actual query take and how much does the rest of your PHP code take.
    You really should post some code. How is someone supposed to help you having only the query which you think is not the problem?
    I'm not sure if this was any help, but I hope it didn't make you stupider.

    Experience is something you get just after you really need it.
    PHP Installation Guide Feedback welcome.

  • #4
    Super Moderator
    Join Date
    May 2002
    Location
    Perth Australia
    Posts
    4,095
    Thanks
    11
    Thanked 101 Times in 99 Posts
    I see no LIMIT set.. are you pulling all 10,000 records out and then using PHP to show only 25 ? you should be using `LIMIT $offset,25` or similar ... but as marek notes we are guessing without seeing more code.
    resistance is...

    MVC is the current buzz in web application architectures. It comes from event-driven desktop application design and doesn't fit into web application design very well. But luckily nobody really knows what MVC means, so we can call our presentation layer separation mechanism MVC and move on. (Rasmus Lerdorf)

  • #5
    Regular Coder
    Join Date
    Nov 2006
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Sorry here is the code for the page.
    Pretty bare bones, search and retrieve page, but it gets the job done, justa bit slowly right now.
    I should also mention that I doubt it is network performance or anything like that the end pc is very fast as well as the switched gigabit network. I have a feeling it is like you say,

    LIMIT is defined as a variable just before I call the query.

    Code:
           <form name="form" action="contactlookup.php" method="get">
              <label>
              Look for
              <input name="q" type="text"/>
    under
    <select name="column">
              <option value="contact.Name">Contact Name</option>
              <option value="contact.Email">Contact Email</option>
              <option value="company.CompanyName">Company Name</option>
              <option value="branch.Address">Address</option>
              <option value="branch.City">City</option>
              <option value="branch.Province">Province</option>
              <option value="branch.PostalCode">Postal Code</option>
              <option value="branch.Phone">Phone</option>
              <option value="contact.Cell">Contact Cell</option>
              <option value="branch.Fax">Fax</option>
              <option value="company.CompanyID">Company ID</option>
              <option value="branch.BranchID">Branch ID</option>
              <option value="contact.ContactID">Contact ID</option>
           	  <option value="credit.Status">Black List Status</option>
              </select>
            </label>
             and show 
             <label>
             <select name="perpage">
               <option value="1">1</option>
               <option value="10">10</option>
               <option value="25" selected="selected">25</option>
               <option value="50">50</option>
               <option value="100">100</option>
               <option value="200">200</option>
               <option value="500">500</option>
               <option value="1000">1000</option>
             </select>
             </label>
             results per page and sort by 
             <select name="sort">
              <option value="company.CompanyName" selected="selected">Company Name</option>
              <option value="contact.Name" >Contact Name</option>
              <option value="contact.Email">Contact Email</option>
              <option value="branch.Address">Address</option>
              <option value="branch.City">City</option>
              <option value="branch.Province">Province</option>
              <option value="branch.PostalCode">Postal Code</option>
              <option value="branch.Phone">Phone</option>
              <option value="contact.Cell">Contact Cell</option>
              <option value="branch.Fax">Fax</option>
              <option value="company.CompanyID">Company ID</option>
              <option value="branch.BranchID">Branch ID</option>
              <option value="contact.ContactID">Contact ID</option>
              <option value="credit.Status">Black List Status</option>
              </select> 
             <input type="submit" name="Submit" value="Search" />
          </form>
            <span class="style1">
            <?php
      $var = @$_GET['q'] ;
      $trimmed = trim($var);
      $searched = @$_GET['column'] ;
      $sorting = @$_GET['sort'] ;
      $rowsPerPage = @$_GET['perpage'] ;
    
    @mysql_connect("x.x.x.x","pass","password");
    @mysql_select_db("soileng") or die("Can not find database"); 
    
    $searched . ' column.';
    
    $pageNum = 1;
    
    if(isset($_GET['page']))
    {
        $pageNum = $_GET['page'];
    }
    
    
    $offset = ($pageNum - 1) * $rowsPerPage;
    		
    $query = "SELECT company.CompanyName, 
    				contact.Name,
    				contact.Email, 
    				branch.Address, 
    				branch.City, 
    				branch.Province,
    				branch.PostalCode, 
    				branch.Phone, 
    				contact.Cell,
    				branch.Fax, 
    				company.CompanyID, 
    				branch.BranchID, 
    				contact.ContactID,
    				credit.Status
    		FROM soileng.branch branch 
    			 LEFT OUTER JOIN soileng.contact contact ON branch.BranchID=contact.ParentBranchID 
    			 LEFT OUTER JOIN soileng.company company ON branch.ParentCompanyID=company.CompanyID 
    			 LEFT OUTER JOIN soileng.credit credit ON company.CompanyID=credit.CompanyID
    		WHERE $searched LIKE '%$trimmed%' 
    		ORDER BY $sorting ";
    
    $pagingQuery = "LIMIT $offset, $rowsPerPage";
    $result = mysql_query($query . $pagingQuery) or die('Please enter a Search String, Leave Search field blank to see all records');
    
    echo '<table border="2" cellspacing="1" cellpadding="1" align="left" >
    <tr><td align="left"><b>CompanyName</b></td>
    	<td align="left"><b>ContactName</b></td>
    	<td align="left"><b>Email</b></td>
    	<td align="left"><b>Address</b></td>
    	<td align="left"><b>City</b></td>
    	<td align="left"><b>Province</b></td>
    	<td align="left"><b>PostalCode</b></td>
    	<td align="left"><b>Phone</b></td>
    	<td align="left"><b>Cell</b></td>
    	<td align="left"><b>Fax</b></td>
    	<td align="left"><b>Comp ID</b></td>
    	<td align="left"><b>Branch ID</b></td>
    	<td align="left"><b>Cont ID</b></td>
    	<td align="left"><b>Status</b></td>
    	</tr>';
    
    while(list( $CompanyName, $Name, $Email, $Address, $City, $Province, $PostalCode, $Phone, $Cell, $Fax, $CompanyID, $BranchID,$ContactID,$Status) = mysql_fetch_array($result))
    {
    if ($Status == 'Black') { echo "<tr bgcolor=#FF0000>"; }
    elseif ($Status == 'Doubtful') { echo "<tr bgcolor=#FFFF00>"; }
    else { echo "<tr bgcolor=#FFFFFF>"; }
    echo"<td nowrap>$CompanyName</td>
    	<td nowrap>$Name</td>
    	<td nowrap>$Email</td>
    	<td nowrap>$Address</td>
    	<td nowrap>$City</td>
    	<td nowrap>$Province</td>
    	<td nowrap>$PostalCode</td>
    	<td nowrap>$Phone</td>
    	<td nowrap>$Cell</td>
    	<td nowrap>$Fax</td>
    	<td nowrap>$CompanyID</td>
    	<td nowrap>$BranchID</td>
    	<td nowrap>$ContactID</td>
    	<td nowrap>$Status</td>
    	</tr>";
    }
    
    echo '<tr>';
    
    $result  = mysql_query($query) or die('Error, query failed you sucka');
    $numrows = mysql_num_rows($result);
    
    $maxPage = ceil($numrows/$rowsPerPage);
    
    $self = $_SERVER['PHP_SELF'];
    
    if ($pageNum > 1)
    {
        $page = $pageNum - 1;
        $prev = " <a href=\"$self?page=$page&q=$var&column=$searched&perpage=$rowsPerPage&sort=$sorting\">[Prev]</a> ";
    
        $first = " <a href=\"$self?page=1&q=$var&column=$searched&perpage=$rowsPerPage&sort=$sorting\">[First Page]</a> ";
    }
    else
    {
        $prev  = ' [Prev] '; 
        $first = ' [First Page] '; 
    }
    
    if ($pageNum < $maxPage)
    {
        $page = $pageNum + 1;
        $next = " <a href=\"$self?page=$page&q=$var&column=$searched&perpage=$rowsPerPage&sort=$sorting\">[Next]</a> ";
    
        $last = " <a href=\"$self?page=$maxPage&q=$var&column=$searched&perpage=$rowsPerPage&sort=$sorting\">[Last Page]</a> ";
    }
    else
    {
        $next = ' [Next] ';      
        $last = ' [Last Page] '; 
    }
    
    echo $first . $prev . " Showing page <strong>$pageNum</strong> of <strong>$maxPage</strong> pages " . $next . $last;
    echo '</tr>';
    echo '</table>';
    echo '<br>';
    ?>
        </span> </td>
      </tr>
    </table>
    </body>

  • #6
    Regular Coder
    Join Date
    Nov 2006
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts
    How do I setup a timer for the query in php? I have seen that in a few pages before. I have always thought that was really cool.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •