Hi,
I have created an offline system which normally runs very quickly.
But when it comes to displaying more and more information in a table it starts slowing down.

Eg. Only 1 item gets displayed. People can click + or - images and it adds an addition # of item 1. (still only 1 record showing)

As people add more items, (item 2, 3, 4, 5) it now shows 5 items in the table. When people click + or - images it now slows down. It seems sluggish.

When people click + and - it changes database entry and then redirects them. When it redraws the new page it responds slower (people cant spam click + and - anymore)
Is there anyway to display the items in the table faster? or to speed up the page creation?

I think I know one way I can change it but I'm not sure about the code. At the moment its split between 2 functions. Pulling data from 2 database tables.

This is how I pull data from the database. How do I pull from multi tables in same SELECT?

PHP Code:

    $sql_text = "SELECT id, minsize, maxsize FROM ".$this->TABLES[$type]." WHERE id = '$unitid' ORDER BY id"; 


By making this 1 function would it speed everything up?

Databases are really fast, unless your pulling hundreds or thousands of records, changing one query will not make much differnce. Or perhaps your storing the images as blobs in the database? I recon that would take longer time.

I dont really understand what the + and - does?

Maybe its firefox not able to respond quickly.

The + and - are just images the user clicks on to add or subtract an items amount.

Frankly there's no "one size fixes all" approach to this. You could have literally thousands of things slowing your page load down.

Your multiple table problem is pretty easy to solve though. You need to use the SQL keyword "JOIN". Here's a bogus example.

SELECT parent_id, parent_name, child_id, child_name FROM parent JOIN child ON parent.parent_id = child.parent_id

This assumes that there's a match between the parent and child tables on those two columns, but this should give you a general idea of how it works. For more information, you can check out this w3schools page.

http://www.w3schools.com/Sql/sql_join.asp

One thing that can help is to cache the query results

Another things that come to my mind is the structure of the HTML you use.
If you use tables, firefox will wait to have a certain amount of rows before displaying the table, except if the </table> tag is found.
IE, as I remember, starts to draw the table immediately.

On pages with a lot of html, it can lead to the impression that frefox is slow, when it's just a matter of display.

But on a page with 2 or 3 rows, it should not bother the browser the least.
What could happen then is that there is javascript updating the UI as far as the table is constructed.
Depending of the processing made, this can bring firefox to a 5/6 seconds freeze.

Maybe you should post your php code. Two nested SELECTs with only a couple of rows in the resultset cannot be a performance problem. And your select statements seem to be quite simple.

Thanks for your help guys. Here is my PHP

PHP Code:

<?php echo $functions->getCurrentArmy($_GET['armymodel']); ?>

PHP Code:

<?php
require('system/header.inc.php');
if ($functions->*functionname*($_GET['armymodel'], $_GET['option'])) {
      header("Location: index.php?type=".$_GET['type']."&unit=".$_GET['unit']."&armymodel=".$_GET['armymodel']."&opt=".$_GET['opt']."");
        } else {
      header("Location: index.php?p=Error&referer=$referer&code=".$functions->ERROR);
        }
?>

PHP Code:

function getCurrentArmy($armymodel) {
    $sql_text = "SELECT id, unitid, minsize, maxsize, currentsize, points, type, totalpoints, standard, musician, champ,  standardcost, musiciancost, champcost FROM ".$this->TABLES['currentarmy']." ORDER BY id";
    $data = $this->sql->Select($sql_text);
    $i=0;
    $len = count($data);
if ($len < 1) {
    $str .='
         ';  
    } else {
for ($i=0; $i< $len; $i++) {
$str .='
'.$this->GetUnit($data[$i]['id'], $data[$i]['unitid'], $data[$i]['minsize'], $data[$i]['maxsize'], $data[$i]['currentsize'], $data[$i]['points'], $data[$i]['type'], $data[$i]['totalpoints'], $data[$i]['standard'], $data[$i]['musician'], $data[$i]['champ'], $armymodel).' 
';
}}
    return $str;
  }

function GetUnit($id, $unitid, $minsize, $maxsize, $currentsize, $points, $type, $totalpoints, $standard, $musician, $champ, $armymodel) {
    $sql_text = "SELECT id,    name, M, WS, BS, S, T, W, I, A, LD, weapons, armour, armoursave, unitstrength, valourofages, speedofasuryan, martialprowess, minsize, maxsize, points, champcost FROM ".$this->TABLES[$type]." WHERE id = '$unitid' ORDER BY id";
    $data = $this->sql->Select($sql_text);
    $i=0;
    $len = count($data);
if ($len < 1) {
    $str .='
          <tr>
          <td>There was a problem and couldnt pull any core units from database.</td>
        </tr>
         ';  
    } else {
for ($i=0; $i< $len; $i++) {

if ($armymodel == $id) {
$class1 = 'unitselected';
$class2 = 'unitstatsselected';
}else{
$class1 = 'unitlinks';
$class2 = 'tabletop';
}


$str .='<tr>
          <td class="tabletopfirst"><a href="index.php?type='.$_GET['type'].'&unit='.$_GET['unit'].'&armymodel='.$id.'&opt='.$unitid.'" class="'.$class1.'">'.$data[$i]['name'].'</a></td>';
if ($champ > 1) { $adjustedsize = $currentsize - 1;}else{$adjustedsize = $currentsize;}
      
$str .='  <td class="'.$class2.'"><div align="center">'.$adjustedsize.'</div></td>
          <td class="'.$class2.'"><div align="center">'.$data[$i]['M'].'</div></td>
          <td class="'.$class2.'"><div align="center">'.$data[$i]['WS'].'</div></td>
          <td class="'.$class2.'"><div align="center">'.$data[$i]['BS'].'</div></td>
          <td class="'.$class2.'"><div align="center">'.$data[$i]['S'].'</div></td>
          <td class="'.$class2.'"><div align="center">'.$data[$i]['T'].'</div></td>
          <td class="'.$class2.'"><div align="center">'.$data[$i]['W'].'</div></td>
          <td class="'.$class2.'"><div align="center">'.$data[$i]['I'].'</div></td>
          <td class="'.$class2.'"><div align="center">'.$data[$i]['A'].'</div></td>
          <td class="'.$class2.'"><div align="center">'.$data[$i]['LD'].'</div></td>
          <td class="'.$class2.'"><div align="center">'.$data[$i]['armoursave'].'</div></td>
          <td class="'.$class2.'"><div align="center">'.$data[$i]['wardsave'].'</div></td>
          <td class="'.$class2.'"><div align="center">'.$data[$i]['unitstrength'].'</div></td>
          <td class="'.$class2.'"><div align="left">';
        
if ($standard > 1) {$str .='Std &nbsp;&nbsp;';}
if ($musician > 1) {$str .='Mus ';}
          
$str .=' </div></td>
          <td class="'.$class2.'"><div align="center">'.$totalpoints.'</div></td>
        </tr>';
if ($champ > 1) {
$str .='
          <td class="firstgreytext"><div align="left">˜Champ Name</div></td>
          <td class="greytext"><div align="center">1</div></td>
          <td class="greytext"><div align="center">'.$data[$i]['M'].'</div></td>
          <td class="greytext"><div align="center">'.$data[$i]['WS'].'</div></td>
          <td class="greytext"><div align="center">'.$data[$i]['BS'].'</div></td>
          <td class="greytext"><div align="center">'.$data[$i]['S'].'</div></td>
          <td class="greytext"><div align="center">'.$data[$i]['T'].'</div></td>
          <td class="greytext"><div align="center">'.$data[$i]['W'].'</div></td>
          <td class="greytext"><div align="center">'.$data[$i]['I'].'</div></td>
          <td class="greytext"><div align="center">'.$data[$i]['A'].'</div></td>
          <td class="greytext"><div align="center">'.$data[$i]['LD'].'</div></td>
          <td class="greytext"><div align="center">'.$data[$i]['armoursave'].'</div></td>
          <td class="greytext"><div align="center">'.$data[$i]['wardsave'].'</div></td>
          <td class="greytext"><div align="center">'.$data[$i]['unitstrength'].'</div></td>
          <td class="greytext">&nbsp;</div></td>
          <td class="greytext"><div align="center">['.$data[$i]['champcost'].']</div></td>
        </tr>';}
}}
    return $str;
  } 


Each time it loads the index page. It has 5 function pulls.

In that case, I'd recommend running your queries simultaneously using threads.
Then, just assemble your page afterward using the resultant data.

5 could take the time of one

Originally Posted by anderswc

Frankly there's no "one size fixes all" approach to this. You could have literally thousands of things slowing your page load down.

Couldn't agree more.

Originally Posted by anderswc

Your multiple table problem is pretty easy to solve though. You need to use the SQL keyword "JOIN". Here's a bogus example.

JOINs will work but they are slow.

JOINs will work but they are slow.

No, they are slow only if you don't use them correctly.
In fact, you need to use joins, if you don't want to use an normalized db structure.

It's what an OLAP cube is doing.
The dimensions are nothing less than aggregated datas pulled out from joined table to normalize them in one big table.

Database views in Microsoft SQL Server 2000 provide a flexible and efficient way to display data. Using a view allows us to pull data from many tables, incorporate sophisticated Transact-SQL and user defined functions, grab the Top N or Bottom N records, and even pull records from partitioned tables and tables located on disparate servers. Iron Speed Designer provides developers with some great properties to facilitate working with views. Virtual primary keys and virtual foreign keys, the ability to select the column you want displayed in place of a foreign key value...these all greatly enable the use of views in an Iron Speed Designer application.
Recently I found myself needing to display users’ expense data in an application. By using a view I could easily bring in other supporting data and give the user a richer and more meaningful data viewing experience than would be possible by pulling data from the Expenses table alone.



================================================== ====
famous vegan
permanente make up

I have created an offline system which normally runs very quickly.
But when it comes to displaying more and more information in a table it starts slowing down.

Harry

Is there a maximum to how many rows you are pulling from the table? If there is, it's always a good practice to put a LIMIT statement on the end of your query so that when the limit is reached MySQL does not search for more rows matching your query. For example if you KNOW that only one row will be returned, put a "LIMIT 1" on the end of the query to tell MySQL to stop after it finds one row, because while you may know that you are only looking for one row MySQL does not and will look through the entire table if you do not explicitly set a limit.

This most likely would not slow down your result too much since you're not pulling that much information from the table, and it would only make a difference with hundreds and thousands of rows, but it's always good to put a limit there to speed things along.