MetaData['totalRecords'] huge lag

4 posts by 2 authors in: Forums > CMS Builder
Last Post: March 7, 2011   (RSS)

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

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