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:

list loader for table areas (15 records)
foreach area (15), list loaded for table properties where area = area
display totalRecords foreach area

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

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!

<?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:

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/