[FX.php List] Showing last related record - sort in FM or just
PHP?
Joel Shapiro
jsfmp at earthlink.net
Tue Oct 17 18:29:55 MDT 2006
As per Dan's request, I've done some testing: comparing times of FM
returning all related records vs. returning only an unstored calc.
In all my tests, using the calc field produced faster results -- even
when there was just one related record(!) (this seems odd, based on
others' reports. see my notes below for particulars)
Goal: Display field from last of sorted child records
R = Sorted relationship, related field on layout, using: end($v
['REL::field'])
C = Calc field* [unstored], no related field(s) on layout
* for calc field, I tried both:
= max(REL::numberField) -- unsorted relationship
= REL::textField -- relationship sorted: descending by text field
both had similar results
Times are in seconds
Find 1 parent record with 1 child record
R: 2.37168607
C: 2.32556838
Find 1 parent record with 20 child records
R: 2.37210186
C: 2.29863138
Find 1 parent record with 200 child records
R: 2.80792115
C: 2.48299909
Find 1 parent record with 800 child records
R: 4.33599532
C: 2.74986251
Find 4 parent records, each with 200 - 800 child records
R: 6.63211448
C: 3.94424827
Notes:
- times are averages of 6-10 trials each (& incl. 2 sec sleep)
- not optimized (other fields on the layout) but consistent throughout
- single development machine running FMSA, FM, browser
- separation model: Web file (no data) and Data file
- match fields: kp_ParentID -< kf_ParentID (in Data file for calc,
and in Web file)
(could this have been different if it were a multi-predicate
relationship?)
(aside: Dan, why include the sleep(2) in the timing script?)
-Joel
On Oct 17, 2006, at 1:31 PM, DC wrote:
> hi joel,
>
> since you have your testing architecture set up already i'd love it
> if you would take your testing one step further and enlighten us
> all about this issue since we all seem to be guessing and
> thumbnailing it and it would be cool to know exactly what the
> tradeoffs are when you give certain jobs to FMP:
>
> speed comparison between an unstored calc field that plucks off the
> 'last' record from a relationship and sending related records back
> through FX.php's XML processing. the test would have to be made
> with a range of different related record numbers starting at 1
> related records and maybe going up to 1000.
>
> my assumption is that unstored calc field will be slightly slower
> when there aren't too many related records, but with many related
> records, the overhead of processing them in WPE and XML will start
> to get slow.
>
> my question is at what number of related records does the XML
> processing go slower than the unstored related calc field?
>
> thanks!
> dan
>
> On Oct 17, 2006, at 4:22 PM, Joel Shapiro wrote:
>
>> Thanks Dale
>>
>> I didn't know about end(), so that's great.
>>
>> In quick testing on my local development machine (running FMSA as
>> well as FMP and everything else), I couldn't see any real time
>> difference between FM's sending up to 1000 related records (just
>> for testing) sorted vs. unsorted -- for 4 parent records returned.
>> I had thought it'd be slower for FM to sort before sending, but it
>> seems negligible. (And I will sort the relationship -- or even
>> just the portal(?) -- just to catch any out-of-order entries, as
>> Steve warned of.)
>>
>> Thanks all!
>>
>> -Joel
>>
>>
>> On Oct 16, 2006, at 6:15 PM, Dale Bengston wrote:
>>
>>> Hi Joel,
>>>
>>> Well, if your relationship is sorted last-to-first, it will be
>>> element 0 of the related field array, so you can just reference
>>> that explicitly:
>>>
>>> echo $value['relationship::myField'][0];
>>>
>>> If your relationship is not sorted, you can easily find the last
>>> element with the PHP end function:
>>>
>>> echo end($value['relationship::myField']);
>>>
>>> I like to use PHP to do as much of the work as possible. No
>>> unstored calc fields necessary here.
>>>
>>> Dale
>>>
>>> On Oct 16, 2006, at 7:31 PM, Joel Shapiro wrote:
>>>
>>>> Great. Thanks Steve!
>>>>
>>>> I'll plan on returning all related records (by default) and then
>>>> sorting in php to display only the one I want.
>>>>
>>>> Best,
>>>> -Joel
>>>>
>>>>
>>>> On Oct 16, 2006, at 5:16 PM, Steve Winter wrote:
>>>>
>>>>> Hi Joel,
>>>>>
>>>>>>> my best advice, sort in fmp in the relationship definition.
>>>>>>
>>>>>> but if the WPE will spit back *all* related records regardless,
>>>>>> wouldn't it be faster to have FM return all the data unsorted
>>>>>> and do
>>>>>> the sorting within PHP?
>>>>>
>>>>> Probably... if you were actually going to do some form of sort,
>>>>> then it
>>>>> would most likely be quicker to do it in php. If it was a sort
>>>>> order
>>>>> applied to the relationship I don't think it will make any
>>>>> difference...
>>>>>
>>>>>>> if you want to get really trick and you are sure you only ever
>>>>>>> need one (most recent) record you can make a calc field that
>>>>>>> just
>>>>>>> returns the last record. that way PHP doesn't have to
>>>>>>> transform a
>>>>>>> bunch of XML data that you'll never use.
>>>>>>
>>>>>> I've heard it's optimal to not have calc fields on the web
>>>>>> layout if
>>>>>> possible (for speed), but do you think that in this case it could
>>>>>> still be faster than returning all related records?
>>>>>
>>>>> Personally I think this would be the slowest option... unstored
>>>>> calculation fields are going to be slower for the wpe to
>>>>> process than all
>>>>> of the related records (IMHO)
>>>>>
>>>>> Cheers
>>>>> Steve
>>>>>
>>>>>
>>>>> _______________________________________________
>>>>> FX.php_List mailing list
>>>>> FX.php_List at mail.iviking.org
>>>>> http://www.iviking.org/mailman/listinfo/fx.php_list
>>>>
>>>> _______________________________________________
>>>> FX.php_List mailing list
>>>> FX.php_List at mail.iviking.org
>>>> http://www.iviking.org/mailman/listinfo/fx.php_list
>>>
>>> _______________________________________________
>>> FX.php_List mailing list
>>> FX.php_List at mail.iviking.org
>>> http://www.iviking.org/mailman/listinfo/fx.php_list
>>
>> _______________________________________________
>> FX.php_List mailing list
>> FX.php_List at mail.iviking.org
>> http://www.iviking.org/mailman/listinfo/fx.php_list
>
> _______________________________________________
> FX.php_List mailing list
> FX.php_List at mail.iviking.org
> http://www.iviking.org/mailman/listinfo/fx.php_list
More information about the FX.php_List
mailing list