Caching custom table results

16 Posts

mike85 Reply #1, 14 years, 4 months ago

Hi,

I have a custom table that I output with a custom snippet and getPage. Every few minutes a new entry is made to the database table. At the moment the results don’t get cached what makes it very slow.

What’s the best way to cache the results in that case?

5,888 Posts

Bruno17 Reply #2, 14 years, 4 months ago

you didn't say a lot of what you think is the reason to be slow.

if you have datarows with lots of columns and placeholders,
perhaps its possible to speed it up, by storing a rendered datarow-output into the records, on saving-time.

Or perhaps its possible to get the old ones cached (dataset from/to) and add the new ones uncached and refresh the cache from time to time, (every hour, every day)

tell us more about your complete scenario.

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

16 Posts

mike85 Reply #3, 14 years, 4 months ago

I made several tests to figure out what could possibly slow down the data-output. I work with getPage and a custom snippet that gets the data from the database.

With the following code the load time is at about 0.8s: (Note that I don’t loop through the results to generate the output)

$offset = $modx->getOption('offset',$scriptProperties,0);
$limit = $modx->getOption('limit',$scriptProperties,50);
$sort = $modx->getOption('sort',$scriptProperties,'approvedon');
$dir = $modx->getOption('dir',$scriptProperties,'DESC');

$c = $modx->newQuery('Supporter');

$c->where(array(
    'locked' => 0,
    'approved' => 1,
));

$c->sortby($sort,$dir);

//for getPage
$total = $modx->getCount('Supporter',$c);
$modx->setPlaceholder('total',$total);
$c->limit($limit,$offset);

$supporters = $modx->getIterator('Supporter', $c);

// Without looping through the results

$modx->setPlaceholder('supporterList', $output);

With this code I get load times at about 1.68s:

$offset = $modx->getOption('offset',$scriptProperties,0);
$limit = $modx->getOption('limit',$scriptProperties,50);
$sort = $modx->getOption('sort',$scriptProperties,'approvedon');
$dir = $modx->getOption('dir',$scriptProperties,'DESC');

$c = $modx->newQuery('Supporter');

$c->where(array(
    'locked' => 0,
    'approved' => 1,
));

$c->sortby($sort,$dir);

//for getPage
$total = $modx->getCount('Supporter',$c);
$modx->setPlaceholder('total',$total);
$c->limit($limit,$offset);

$supporters = $modx->getIterator('Supporter', $c);

foreach ($supporters as $supporter) {
	
    $supporterArray = $supporter->toArray();
    $output .= $modx->getChunk('supporterList', $supporterArray);

}

$modx->setPlaceholder('supporterList', $output);

When I output just one field it saves me just 0.15s

$output .= $supporterArray['name'];

I also tried it with Rowboat and caching the results but I got the almost same load time.

I just figured out that the use of $c->select($modx->getSelectColumns('Supporter','Supporter','',array('id','name'))); slows down the page load by 0.25s at average. [ed. note: mike85 last edited this post 14 years, 4 months ago.]

5,888 Posts

Bruno17 Reply #4, 14 years, 4 months ago

what is the content of your chunk supporterList

how long does it take with something like that in the loop:

foreach ($supporters as $supporter) {
     
    $output .= 'Testoutput/'.$supporter->get('id');
 
}

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

16 Posts

mike85 Reply #5, 14 years, 4 months ago

The content of the chunk is just:

[[+firstname]] [[+name]], [[+city]]

With your output It takes about 1.56s. Almost the same like:

$output .= $supporterArray['name'];

How do others/you cache such results?
Am I expecting too much or is it possible to go under 1s.
Standard modx pages in the same website with getResources, wayfinder and tvs take about 0.4s.