MetaData['totalRecords'] huge lag
4 posts by 2 authors in: Forums > CMS Builder
Last Post: March 7, 2011 (RSS)
By rjbathgate - March 2, 2011
Hello,
I have a script which is calling MetaData['totalRecords'] and it causes a massive (5+ second) lag in pageload whilst it gets this value.
It is called near the top of the page, so causes the remaining code/page to hang whilst this finishes loading.
It's called a few in the script, to get totalRecords of the same table based on different where criteria:
The properties table has approx 600 records in it, so the script is calling a selection of these 600 records for each 'area'.
Explaining it doesn't sound very efficient...!!
Is there likely to be either
a) a method of doing this which is more efficient?
b) a way I can speed it up through server settings
c) a way I can get remainder of page to continue loading whilst the script runs -- if I physically put the script at the bottom of the page, it'll visually fix the load speed problem, BUT the layout requires the script to be populated near the top of the page...
Bit vague, I know... sorry!
Thanks for any thoughts...
I have a script which is calling MetaData['totalRecords'] and it causes a massive (5+ second) lag in pageload whilst it gets this value.
It is called near the top of the page, so causes the remaining code/page to hang whilst this finishes loading.
It's called a few in the script, to get totalRecords of the same table based on different where criteria:
list loader for table areas (15 records)
foreach area (15), list loaded for table properties where area = area
display totalRecords foreach areaThe properties table has approx 600 records in it, so the script is calling a selection of these 600 records for each 'area'.
Explaining it doesn't sound very efficient...!!
Is there likely to be either
a) a method of doing this which is more efficient?
b) a way I can speed it up through server settings
c) a way I can get remainder of page to continue loading whilst the script runs -- if I physically put the script at the bottom of the page, it'll visually fix the load speed problem, BUT the layout requires the script to be populated near the top of the page...
Bit vague, I know... sorry!
Thanks for any thoughts...
Re: [rjbathgate] MetaData['totalRecords'] huge lag
By Jason - March 3, 2011
Hi,
Are you finding you're having performance problems in other areas, ie inside CMS Builder, etc? A table with 600 records isn't actually that big, so you may be experiencing a slow server.
calling MetaData['totalRecords'] is just accessing a variable in an array, so it shouldn't take any time at all. The thing that would take up time is using getRecords() to query the database.
If you could attach a copy of your .php file, I can take a look and maybe suggest ways you can make it more efficient.
Hope this helps.
Are you finding you're having performance problems in other areas, ie inside CMS Builder, etc? A table with 600 records isn't actually that big, so you may be experiencing a slow server.
calling MetaData['totalRecords'] is just accessing a variable in an array, so it shouldn't take any time at all. The thing that would take up time is using getRecords() to query the database.
If you could attach a copy of your .php file, I can take a look and maybe suggest ways you can make it more efficient.
Hope this helps.
---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com
Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/
Jason Sauchuk - Project Manager
interactivetools.com
Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/
Re: [Jason] MetaData['totalRecords'] huge lag
By rjbathgate - March 3, 2011
Hey Jason,
No, CMSB is relatively fine elsewhere, and this specific problem occurs on two different servers (local and server based).
Here's the code...
The basis is:
We have approx 20 areas, each is assigned to either North, South, East or West (NSEW)
We output, for example:
North (xx)
+ {North Area 1} (yy)
+ {North Area 2} (yy)
South (xx)
+ {South Area 1} (yy)
etc...
where xx is the totalRecords which are in 'North' (or SEW)
where yy is the totalRecords which are in the north area
The records being counted are associated to AREA only. NSEW is then obtained by looking at the AREA and detmining which NSEW it is in.
good luck!
If you comment out the totalRecords sections, it works at a proper speed, ie
Don't spend too long on it, we've commented it out for now and were going to reasses it at a later date.
:)
No, CMSB is relatively fine elsewhere, and this specific problem occurs on two different servers (local and server based).
Here's the code...
The basis is:
We have approx 20 areas, each is assigned to either North, South, East or West (NSEW)
We output, for example:
North (xx)
+ {North Area 1} (yy)
+ {North Area 2} (yy)
South (xx)
+ {South Area 1} (yy)
etc...
where xx is the totalRecords which are in 'North' (or SEW)
where yy is the totalRecords which are in the north area
The records being counted are associated to AREA only. NSEW is then obtained by looking at the AREA and detmining which NSEW it is in.
good luck!
<?php
$nsew_array = array( "North", "South", "East", "West");
$totalNsew = 0;
$i = 0;
while ( $i <= 3 ) {
$nsew = $nsew_array[$i];
$i++;
list($list_areasRecords, $list_areasMetaData) = getRecords(array(
'tableName' => 'list_areas',
'allowSearch' => false,
'where' => "nsew = '$nsew'"
));
$areaCount = '0';
$totalAreas = $list_areasMetaData['totalRecords'];
echo "<option value=\"";
foreach ($list_areasRecords as $record)
{
$currentArea = $record['area'];
list($saleCountRecords, $saleCountMetaData) = getRecords(array(
'tableName' => 'sale',
'where' => "area = '$currentArea'",
'allowSearch' => false,
));
$areaCount ++;
echo $record['num'];
if($areaCount != $totalAreas)
{
echo ", ";
}
//$totalNsew = $totalNsew + $saleCountMetaData['totalRecords'];
}
echo "\">All ";
echo $nsew ;
echo " (";
echo $totalNsew;
echo ")";
echo "</option>";
foreach ($list_areasRecords as $record)
{
$thisArea = $record['area'];
list($saleCountRecords, $saleCountMetaData) = getRecords(array(
'tableName' => 'sale',
'where' => "area = '$thisArea'",
'allowSearch' => false,
));
echo "<option value=\"";
echo $record['num'];
echo "\">+ ";
echo join(', ', getListLabels('sale', 'area', $record['area']));
echo " (";
echo $saleCountMetaData['totalRecords'];
echo ")";
echo "</option>";
}
}
?>If you comment out the totalRecords sections, it works at a proper speed, ie
<?php
$nsew_array = array( "North", "South", "East", "West");
$totalNsew = 0;
$i = 0;
while ( $i <= 3 ) {
$nsew = $nsew_array[$i];
$i++;
list($list_areasRecords, $list_areasMetaData) = getRecords(array(
'tableName' => 'list_areas',
'allowSearch' => false,
'where' => "nsew = '$nsew'"
));
$areaCount = '0';
$totalAreas = $list_areasMetaData['totalRecords'];
// $totalNsew = '0';
echo "<option value=\"";
foreach ($list_areasRecords as $record)
{
echo $record['num'];
if($areaCount != $totalAreas)
{
echo ", ";
}
//$totalNsew = $totalNsew + $saleCountMetaData['totalRecords'];
}
echo "\">All ";
echo $nsew ;
echo "</option>";
foreach ($list_areasRecords as $record)
{
echo "<option value=\"";
echo $record['num'];
echo "\">+ ";
echo join(', ', getListLabels('sale', 'area', $record['area']));
echo "</option>";
}
}
?>Don't spend too long on it, we've commented it out for now and were going to reasses it at a later date.
:)
Re: [rjbathgate] MetaData['totalRecords'] huge lag
By Jason - March 7, 2011
Hi,
I think the reason you're running into performance issues is because you're looping through the same records multiple times and then calculating the same counts multiple times.
For example:
And
are both looping through the same record set ( $list_areasRecord) and getting totals for each area. Basically, you're getting the same total multiple times.
You might want to loop through the data once to get your totals and store them in an array.
Consider trying something like this:
At the end of this code, we have an array that stores all the areas and the totals in the sale table for each area.
You can then access this array to get these totals without having to re-select them from the database like this:
Hope this helps get you started.
I think the reason you're running into performance issues is because you're looping through the same records multiple times and then calculating the same counts multiple times.
For example:
foreach ($list_areasRecords as $record)
{
$currentArea = $record['area'];
list($saleCountRecords, $saleCountMetaData) = getRecords(array(
'tableName' => 'sale',
'where' => "area = '$currentArea'",
'allowSearch' => false,
));
$areaCount ++;
echo $record['num'];
if($areaCount != $totalAreas)
{
echo ", ";
}
//$totalNsew = $totalNsew + $saleCountMetaData['totalRecords'];
}And
foreach ($list_areasRecords as $record)
{
$thisArea = $record['area'];
list($saleCountRecords, $saleCountMetaData) = getRecords(array(
'tableName' => 'sale',
'where' => "area = '$thisArea'",
'allowSearch' => false,
));
echo "<option value=\"";
echo $record['num'];
echo "\">+ ";
echo join(', ', getListLabels('sale', 'area', $record['area']));
echo " (";
echo $saleCountMetaData['totalRecords'];
echo ")";
echo "</option>";
}are both looping through the same record set ( $list_areasRecord) and getting totals for each area. Basically, you're getting the same total multiple times.
You might want to loop through the data once to get your totals and store them in an array.
Consider trying something like this:
$areaToTotal = array();
foreach ($list_areasRecords as $record)
{
$where = "area = '". mysql_escape( $record['area']) ."'";
$areaCount = mysql_select_count_from( 'sale', $where );
$areaToTotal [ $record['area'] ] = $areaCount;
}At the end of this code, we have an array that stores all the areas and the totals in the sale table for each area.
You can then access this array to get these totals without having to re-select them from the database like this:
foreach($areaToTotal as $area => $areaTotal){
}Hope this helps get you started.
---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com
Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/
Jason Sauchuk - Project Manager
interactivetools.com
Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/