very poor performance with multiple getChunk calls

☆ A M B ☆
450 Posts

jrotering Reply #1, 15 years, 2 months ago

I converted a simple PHP/Oracle application into MODx tonight, and found that one of my queries was timing out in MODx (exceeding the max request time in php.ini). The query was returning 900+ results, and I was setting placeholders with each returned row and using getChunk to output the results in a table row tpl chunk. After trying several different approaches to optimizing the query, I determined that it was the $modx->getChunk call that was the perfomance hit. Somewhere around 300 or so rows was the most I could return using getChunk before the page would time out.

Should I expect a significant improvement in this area if I upgrade to 2.1? Or is there just a better way of solving this problem in general? I typically use getChunk to iterate over result sets and put them in <option> chunks or table rows, but I don’t usually work with data sets this large.

5,888 Posts

Bruno17 Reply #2, 15 years, 2 months ago

what’s the content of your chunk?

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

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

24,544 Posts

BobRay Reply #3, 15 years, 2 months ago

If you’re using getCollection(), try using getIterator() instead. The arguments are the same, but it get’s the rows one at a time rather than all at once.

Did I help you? Buy me a beer
Get my Book: MODX:The Official Guide
MODX info for everyone: http://bobsguides.com/modx.html
My MODX Extras
Bob's Guides is now hosted at A2 MODX Hosting

MODX Staff
10,725 Posts

opengeek Reply #4, 15 years, 2 months ago

Quote from: jrotering at Jul 28, 2011, 10:28 PM

Should I expect a significant improvement in this area if I upgrade to 2.1? Or is there just a better way of solving this problem in general? I typically use getChunk to iterate over result sets and put them in <option> chunks or table rows, but I don’t usually work with data sets this large.

You should experience an extreme improvement in this in 2.1, as the Chunk source is now cached on first use as well as into the Resource cache it is used in, which avoids a database trip to grab the Chunk again which is what is happening in 2.0.

Jason Coward
Chief Architect @ MODX
http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek

☆ A M B ☆
450 Posts

jrotering Reply #5, 15 years, 2 months ago

I just tried this on 2.1.3 and it is working much, much better. There was still a significant lag initially, then I discovered that my tplChunk had a malformed placeholder in it that was probably choking the parse engine - [[+$placeholder]]. (To Bruno17’s point, I think). Took out the stray ’$’ and now the initial (uncached) load is about 5-6 seconds, with subsequent loads basically immediate.

@Bob, this is using native PHP Oracle calls, since there is not an xpdo-for-Oracle solution to date.

For those interested, my code looks like this:

$sql = "SELECT (really long arduous Oracle SQL statement)";

$stid = oci_parse($conn, $sql);
oci_execute($stid);
$vars = array();
$nrows = oci_fetch_all($stid, $res, null, null, OCI_FETCHSTATEMENT_BY_ROW);
  
foreach ($res as $row) { 
  
    $vars['grp_descr']  = $row['SFS_ACCOUNT_GROUP_DESCRIPTION'];
    $vars['status']     =  $row['ACCOUNT_STATUS'];
    $vars['acct']       = $row['SFS_ACCOUNT'];
    $vars['acctalign']  = (ctype_alpha(substr($vars['acct'],0,1))) ? 'left' : 'center';   
    $vars['type']       = $row['SFS_ACCOUNT_TYPE'];
    $vars['title']      = $row['SFS_ACCOUNT_TITLE'];
    $vars['color']      = $row['SFS_ACCOUNT_COLOR'];
    $vars['definition'] = $row['SFS_ACCOUNT_DEFINITION']; 
  
    $modx->toPlaceholders($vars);
    $resultRows .= $modx->getChunk('tplResultsRow');
}
$modx->setPlaceholder('result_rows',  $resultRows );

The chunk looks like this:

<tr class="[[+color]]">
  <td align="left">[[+grp_descr]]</td>
  <td align="center">[[+status]]</td>
  <td align="[[+acctalign]]">[[+acct]]</td>
  <td align="center">[[+type]]</td>
  <td align="left">[[+title]]</td>
  <td align="left">[[+definition]]</td>
</tr>

Returns 935 rows and now loads in about 5 or so seconds. There are some Oracle php.ini settings I might try to fiddle with to improve on that.

MODX Staff
10,725 Posts

opengeek Reply #6, 15 years, 2 months ago

You don’t need to call toPlaceholders either — pass those to the getChunk call so they are only set for the scope of the Chunk...

<?php
$resultRows .= $modx->getChunk('tplResultsRow', $vars);

Jason Coward
Chief Architect @ MODX
http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek