xpdo - more efficent way to query one to many table?

130 Posts

Hat-rack Reply #1, 15 years, 2 months ago

I am a bit of a noob when it comes to xpdo and php, I’ve managed to query a one to many table and display the results using this code:

<?php
$path = MODX_CORE_PATH . 'components/athletes/';
$result = $modx->addPackage('athletes',$path . 'model/','cr_');

/// Grab required values in Runner table, by race. 
$query = $modx->newQuery('Runner');
$query->select(array(
    'AutoId',
    'RegId',
    'FirstLastName',
    'BibNumber'
));
$query->sortby('FirstLastName','ASC');
$query->where(array(
   'RegTypeDescription' => $race
));
///$query->limit(100);

$athletes = $modx->getCollection('Runner', $query);

/// Grab all values in Time table. It grabs about 10 columns, I could restrict this to the 4 or so I need to speed it up? 
$athleteTimes = $modx->getCollection('Time');
$athleteTimesCount = count($athleteTimes);

/// Loop through all the athletes entered for the race
foreach($athletes as $athlete) {
    /// Grab the RegId into a variable to pass as criteria for the next foreach
    $athleteRegId = $athlete->get('RegId');

    /// Loop through all the times that have been recorded so far at each aid station. up to 4,500 records. 
    foreach($athleteTimes as $athleteTime) {
        
        $athleteTimeRegId = $athleteTime->get('timeRunnerId');
        /// If there is a match, add those values to the athlete object
        if ($athleteTimeRegId == $athleteRegId) {
            $aidId = $athleteTime->get('timeStationId');
            $athlete->fromArray(array(
                'aid' . $aidId . 'Time' => $athleteTime->get('timeRunnerSecReadable'),
                'aid' . $aidId . 'Pace' => 'Pace '.$athleteTime->get('timePaceFromStart'),
            ),'',true); 
        }
    }

    /// use a template to display results
    $fields = $athlete->toArray();
    $output .= $modx->getChunk('LiveRaceRow', $fields);
}


return $output;

LiveRaceRow template:

<tr>

<td> 
?
</td>

<td> 
[[+FirstLastName]]
</td>

<td>
[[+BibNumber]]
</td>

<td>
[[+aid1Time]]<br />
<span class="twitterInfo">[[+aid1Pace]] </span>
</td>

<td>
[[+aid2Time]]<br />
<span class="twitterInfo">[[+aid2Pace]] </span>
</td>

<td>
[[+aid3Time]]<br />
<span class="twitterInfo">[[+aid3Pace]] </span>
</td>

<td>
[[+aid4Time]]<br />
<span class="twitterInfo">[[+aid4Pace]] </span>
</td>

<td>
[[+aid5Time]]<br />
<span class="twitterInfo">[[+aid5Pace]] </span>
</td>

<td>
[[+aid6Time]]<br />
<span class="twitterInfo">[[+aid6Pace]] </span>
</td>

<td>
[[+aid7Time]]<br />
<span class="twitterInfo">[[+aid7Pace]] </span>
</td>

<td>
[[+aid8Time]]<br />
<span class="twitterInfo">[[+aid8Pace]] </span>
</td>

<td>
[[+aid9Time]]<br />
<span class="twitterInfo">[[+aid9Pace]] </span>
</td>

<td>
[[+aid10Time]]<br />
<span class="twitterInfo">[[+aid10Pace]] </span>
</td>

<td>
[[+aid11Time]]<br />
<span class="twitterInfo">[[+aid11Pace]] </span>
</td>

<td>
[[+aid12Time]]<br />
<span class="twitterInfo">[[+aid12Pace]] </span>
</td>

</tr>

Now I’m pretty sure I’m going about this the wrong way. There’s a foreach loop within a foreach loop that gets the one to many (Runners -> Time) values from table "time". The page that’s generated is http://centurionrunning.com/live-test/live-ndw26.2-2011-marathon and it takes a long time to parse - 20secs on average, but it is on the cheapest bluehost plan, so slow server too. I will up to the next plan or transfer to site5 as I’ve been hearing good things.

Can anyone shed some light on what is best practice for executing a query like this to lower load times from 20secs to max 5secs? I imagine I’ll be doing this type of one to many query a lot! Thanks in advance.

5,888 Posts

Bruno17 Reply #2, 15 years, 2 months ago

As suggested in the other thread, you can cache a rendered row for each runner into a extra field of your runners table, when saving a new runners-time.

Then your listing-snippet only needs to get the rendered rows and put them together to your html-table.
I would also cache the result of your listing-snippet somehow.

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

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

5,888 Posts

Bruno17 Reply #3, 15 years, 2 months ago

you can also optimize your code a bit:

store all times in one loop to an array:

<?php

foreach ($athleteTimes as $athleteTime) {
    $athleteTimeRegId = $athleteTime->get('timeRunnerId');
    $aidId = $athleteTime->get('timeStationId');
    $times[$athleteTimeRegId][$aidId] = $athleteTime->toArray();
}

and get them later in your runners-loop:

<?php
foreach ($times[$athleteRegId] as $aidId => $time) {

    $athlete->fromArray(array('aid' . $aidId . 'Time' => $time['timeRunnerSecReadable'], 'aid' . $aidId . 'Pace' => 'Pace ' . $time['timePaceFromStart'], ), '', true);

}

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

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

MODX Staff
730 Posts

netProphET Reply #4, 15 years, 2 months ago

Also if you have a lot of Athletes to loop through, you might save a bit of memory using

$athleteTimes = $modx->getIterator('Runner');

Mike Schell
Lead Developer, MODX Cloud
Email: [email protected]
GitHub: https://github.com/netProphET/
Twitter: @mkschell

24,544 Posts

BobRay Reply #5, 15 years, 2 months ago

I think you would save some time by getting the chunk once (outside your loop) and using str_replace in the loop to fill in the placeholders. And using the chunk’s ID rather than the chunk name in the getChunk() argument will also save a little time.

You might also consider leaving both the page and snippet cached and just clearing the cache after you enter the data for each race.

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 #6, 15 years, 2 months ago

FWIW, you should always use $modx->getChunk(’chunkname’, $properties) within the loop because it is now optimized and uses a built-in caching mechanism to avoid database queries; this was one of the major improvements in MODX 2.1+. You can also now use $modx->parser->getElement(’modChunk’, ’chunkname’) in 2.1+ to get any Element by name using the same internal caching mechanism.

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

24,544 Posts

BobRay Reply #7, 15 years, 2 months ago

Interesting. Are they cached by name rather than ID number?

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

130 Posts

Hat-rack Reply #8, 15 years, 2 months ago

I’ve upgraded to 2.1.3 and the speed issue is still there. I’ve done some more sniffing around - the query and loop itself is not the issue as if I do a print_r, the array displays really quickly, ie <1sec.

It’s this line:

$output .= $modx->getChunk('LiveRaceRow', $fields);

that is causing all the slowdown. As soon as I comment and use print_r, it displays the data <1sec, unformatted of course. I uncomment we are back up to 20+ second loads. Here is a runner that has passed all the checkpoints:

<pre>Array
(
    [AutoId] => 145
    [RegId] => 30108165
    [EventId] => 
    [EventTitle] => 
    [ClientEventId] => 
    [GroupId] => 
    [RegDate] => 
    [CancelDate] => 
    [StatusDescription] => 
    [RegTypeDescription] => 
    [first_Name] => 
    [last_Name] => 
    [FirstLastName] => Aaron Martin
    [Address_1] => 
    [Address_2] => 
    [City] => 
    [CityRegion] => 
    [postal_Code] => 
    [Country] => 
    [Phone] => 
    [HomePhone] => 
    [Photo] => 
    [CellPhone] => 
    [Email_Address] => 
    [SecondaryEmail] => 
    [DirectoryOptOut] => 
    [DateOfBirth] => 
    [Gender] => 
    [Nationality] => 
    [GroupName] => 
    [BibNumber] => 0
    [EmergencyContactName] => 
    [EmergencyContactPhone] => 
    [roomSharerID] => 
    [TotalCharge] => 
    [BalanceDue] => 
    [AddBy] => 
    [AddDate] => 
    [ModBy] => 
    [ModDate] => 
    [ChooseYourCategory] => 
    [TshirtSize] => 
    [ClubName] => 
    [MedicalIssues] => 
    [MoreDetails] => 
    [Fitness] => 
    [PreviousRunningExperience] => 
    [Shuttle] => 
    [aid1Time] => 00:45:45
    [aid1Pace] => Pace 06:55
    [aid2Time] => 02:02:02
    [aid2Pace] => Pace 09:18
    [aid3Time] => 03:03:03
    [aid3Pace] => Pace 07:39
    [aid4Time] => 05:05:55
    [aid4Pace] => Pace 09:37
    [aid5Time] => 06:45:55
    [aid5Pace] => Pace 09:26
    [aid6Time] => 10:02:30
    [aid6Pace] => Pace 11:58
    [aid7Time] => 13:27:00
    [aid7Pace] => Pace 14:05
    [aid8Time] => 15:55:00
    [aid8Pace] => Pace 13:56
    [aid9Time] => 18:44:06
    [aid9Pace] => Pace 14:42
    [aid10Time] => 20:33:06
    [aid10Pace] => Pace 14:08
    [aid11Time] => 23:55:08
    [aid11Pace] => Pace 15:18
    [aid12Time] => 24:59:08
    [aid12Pace] => Pace 14:54
)
</pre>

vs a runner that’s only passed through one aid station:

<pre>Array
(
    [AutoId] => 217
    [RegId] => 33846707
    [EventId] => 
    [EventTitle] => 
    [ClientEventId] => 
    [GroupId] => 
    [RegDate] => 
    [CancelDate] => 
    [StatusDescription] => 
    [RegTypeDescription] => 
    [first_Name] => 
    [last_Name] => 
    [FirstLastName] => Ruth Belsom
    [Address_1] => 
    [Address_2] => 
    [City] => 
    [CityRegion] => 
    [postal_Code] => 
    [Country] => 
    [Phone] => 
    [HomePhone] => 
    [Photo] => 
    [CellPhone] => 
    [Email_Address] => 
    [SecondaryEmail] => 
    [DirectoryOptOut] => 
    [DateOfBirth] => 
    [Gender] => 
    [Nationality] => 
    [GroupName] => 
    [BibNumber] => 0
    [EmergencyContactName] => 
    [EmergencyContactPhone] => 
    [roomSharerID] => 
    [TotalCharge] => 
    [BalanceDue] => 
    [AddBy] => 
    [AddDate] => 
    [ModBy] => 
    [ModDate] => 
    [ChooseYourCategory] => 
    [TshirtSize] => 
    [ClubName] => 
    [MedicalIssues] => 
    [MoreDetails] => 
    [Fitness] => 
    [PreviousRunningExperience] => 
    [Shuttle] => 
    [aid1Time] => 01:00:00
    [aid1Pace] => Pace 09:05
)
</pre>

So the aidxTime and aidxPace doesn’t exist until a runner passes through an aid station. Therefore there are a lot of object field columns that dont exist until a runner goes through an aid station, could these undeclared columns be slowing it down to a massive degree? If so, short of not using a chunk template and hard coding it in, how can I speed it up?

5,888 Posts

Bruno17 Reply #9, 15 years, 2 months ago

is it faster when you use str_replace?

I mean something like that:

$placeholders = '[+field1+],[+field2+],[+field3+],[+field4+],[+field5+],[+field6+]';//add here all your fields
$placeholders = exlode(',',$placeholders);
$chunk = $modx->getObject('modChunk', array('name' => 'LiveRaceRow'));
$template = $chunk->getContent();

foreach ($athletes as $athlete) {
    /*
    * more code
    */
    
    $fields = $athlete->toArray();
    $output .= str_replace($placeholders, $fields, $template);
}

... and again, I would really try to save a rendered row, when saving a new or edited record in a extra field.

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

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

130 Posts

Hat-rack Reply #10, 15 years, 2 months ago

Thanks Bruno for all your help.

I didn’t want to save a rendered row as it feels like a "hack" to make the system do what it should be able to do easily. I mean, if modx can’t query live data without creating and then querying a rendered row, it all becomes a bit pointless.

Anyway, I thought that all these undeclared columns and therefore empty placeholders could be causing the issue and indeed it was. My code now looks like:

<?php
$path = MODX_CORE_PATH . 'components/athletes/';
$result = $modx->addPackage('athletes',$path . 'model/','cr_');




$query = $modx->newQuery('Runner');
$query->select(array(
    'AutoId',
    'RegId',
    'FirstLastName',
    'BibNumber'
));
$query->where(array(
   'RegTypeDescription' => $race
));
///$query->limit(100);


$athletes = $modx->getCollection('Runner', $query);
$athleteTimes = $modx->getCollection('Time');



$athleteTimesCount = count($athleteTimes);


foreach($athletes as $athlete) {
    $athleteRegId = $athlete->get('RegId');
    /// echo 'athleteRegId:' . $athleteRegId . '<br/>';
   
     for ($i = 1; $i <= 14; $i++) {
        $athlete->fromArray(array(
                'aid' . $i . 'Time' => '-',
                'aid' . $i . 'Pace' => '-',
            ),'',true);
    }

    foreach($athleteTimes as $athleteTime) {
        $athleteTimeRegId = $athleteTime->get('timeRunnerId');
        /// echo 'athleteTimeRegId:' . $athleteTimeRegId . '<br/>';
        if ($athleteTimeRegId == $athleteRegId) {
            /// echo 'We have a match <br />';
            /// echo $athleteTime->get('timeRunnerSecReadable') . '<br />';
            /// echo $athleteTime->get('timeStationId') . '<br />';
            /// echo $athleteTime->get('timePaceFromStart') . '<br />';
            $aidId = $athleteTime->get('timeStationId');
            


            $athlete->fromArray(array(
                'aid' . $aidId . 'Time' => $athleteTime->get('timeRunnerSecReadable'),
                'aid' . $aidId . 'Pace' => 'Pace '.$athleteTime->get('timePaceFromStart'),
            ),'',true);
            
            
        }

       
    }

    

    /// $output .= '<pre>' . print_r($athlete->toArray(),true) . '</pre>' . $allTimesRegId;

    $fields = $athlete->toArray();
    ///print '<b>Before sorting</b>: <br> <pre>';
    ///print_r($fields);
    ///print '</pre>';   


    $output .= $modx->getChunk('LiveRaceRow', $fields);
    ///$output .= $modx->getChunk('LiveRaceRow', $fields);
}


return $output;

with the big change being

for ($i = 1; $i <= 14; $i++) {
        $athlete->fromArray(array(
                'aid' . $i . 'Time' => '-',
                'aid' . $i . 'Pace' => '-',
            ),'',true);
    }

So the fields are declared and we’re looking at ~2 sec loading time. Much better and now it’s acceptable. I wonder if this is part of a bigger problem in modx? Surely blank placeholders shouldn’t slow everything down to the degree it has?