[FX.php List] Unstored calculations not showing
DC
dan.cynosure at dbmscan.com
Tue Jan 2 15:01:48 MST 2007
Andrew Denman had written:
> Frank,
SNIP
>
> And a kindly reminder: there is going to be a big performance hit when using
> unstored calculation fields on the web.
Nope. Not true. That is now a verified myth (since October 2006).
Unstored calcs are not the performance problem many of us suspected. The
biggest speed problems can be traced to other sources - too many fields
on a layout, too many records returned in a set (no constraints), etc...
See this report from Joel Shapiro from October 17th, 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
_______________________________________________
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