ListFoundSet kinda...

[mr_vodka]

One of the features that are in FMP13 is a new option for a summary field that will gather the records into a list ( List Of ). Though it isn't exactly a calculation or function that we were looking for, it is great that we finally get something that has been needed for a while.

 

I haven't tried benchmarking it against copy all though.

[David Jondreau]

Interesting....

 

Keep in mind, Copy All[] isn't WebDirect supported.

[Mark Scott]

Hi John,

 

I tested this using Todd Geist's Hyperlist file, which is preloaded with 200K records.  Up until now, the brilliant hyperlist technique has been the fastest way (that doesn't mess with the user's clipboard) to gather IDs.  It remains a wondrous piece of code optimization.

 

I created a new field for gathering IDs: "list_id" (the new "list of" summary field type, referencing the UUID-based "ID" primary key field).  Then, I wrote a benchmarking script to gather the 200,000 IDs into a global variable either by hyperlist or by GetSummary ( TestData::list_id ; TestData::list_id ) and report the delta time between start and end of ID-gathering step (using the new Get ( CurrentTimeUTCMilliseconds ) function).

 

The speed bump was impressive:  

 

     hyperlist:  6.8 sec (averaged over several runs*)

 

     GetSummary (list_id):  2.1 sec (averaged over several runs)

 

I then repeated it, but this time gathering values from an unstored field that grabs FM's internal record IDs ("Get ( RecordID )"), and a "list of" summary field:

 

     hyperlist:  8.4 sec (averaged over several runs; presumably higher than the UUID primary key field because the ids are now being gathered from an unstored field)

 

     GetSummary (list_id):  1.7 sec (averaged over several runs; despite IDs now coming from an unstored field, it performed even faster than the UUID test above, possibly because the values were all numeric and smaller in size than the alphanumeric UUID values)

 

Finally, I repeated the second set of tests, but with a stored field that grabs FM's internal record IDs:

 

     hyperlist:  6.0 sec (averaged over several runs)

 

     GetSummary (list_id):  1.0 sec!!   (wow!)

 

(*Note that when I say "averaged," the variance was small.  In the first test, for example, the 3 times were 6.813, 6.781, and 6.801 sec.)

 

So, yes, I agree that this new feature will likely prove quite useful and is one of the cool "sleeper" features slipped quietly into this very marquee-feature-rich release.

 

Mind you, these tests were all just local; over the LAN/WAN a whole different set of kinetics obviously come into play.

 

Best,

 

Mark

[mr_vodka]

Thanks for doing the initial tests.

[Ocean West]

I created a sample file that shows how to preserve the positional vertical order for records with null values.. http://fmforums.com/forum/topic/90339-listof-preserve-vertical-positon-with-null-values/

[LaRetta]

It irritates me, Stephen, that I cannot rate you.  Many times I wish to give you a + but can't.  So instead, I will have to simply say "Thank you.  This is great."  :-)

[dansmith65]

I'm shocked to hear that hyperlist using an unstored calc of Get ( RecordID ) was slower than a stored UUID field; I've heard this was supposed to be the faster method.

[Mark Scott]

Hi Dan,

 

Great point!  I suspect you're thinking of Daniel Wood's comments on the Hyperlist page over at mFM, in which he advocates (convincingly, I believe) for using FM's internal Get (RecordID) (unstored!) as the preferred way to gather IDs for, say, a master-detail sort of approach.  

 

I think the difference in my tests was that I was just testing locally, in a single file.  His arguments come into play when records have to be transferred from host/server to client.  In that setting, once server processes a query, as I understand it (subject to education where I have anything wrong!), it first sends a complete (and usually fairly tidy) listing of the FileMaker record IDs to client before it begins sending (in batches) any actual record data.  

 

Once client has that listing, and as long as hyperlist is only gathering those FM IDs from an unstored calc field, it can apparently proceed to compile the list without needing any actual record data, and thus proceeds to completion long before even a fraction of actual records are received from server.  (Same presumably goes for the new List Of approach over a network, although that needs to be explicitly verified.)

 

If, on the other hand, hyperlist is tasked with gathering actual record data such as UUID id fields (or even FileMaker record IDs in a stored field), then it needs all of the record data for the entire found set to have been received by client before it can run to completion.  Thus, it stalls repeatedly while awaiting each successive batch of records to be received (in batches of 25 per remote call, IIUC).

 

In fact, it was Daniel's comments, and the implications for a master-detail type layout, that prompted me to extend my (local) test beyond UUID primary keys to include unstored (and, for completeness, stored) FM record IDs.  I think the conclusion is that, whichever field you're gathering, List Of appears, in preliminary testing, to always be at least several-fold quicker than hyperlist, and in a client-server setup, unstored FileMaker record IDs are likely going to be the best way to go for the above reasons.

 

Hope this makes sense—based, as I say, on my fuzzy understanding of data movement btwn host and client.

 

Best,

 

Mark

[bruceR]

Mark: why use GetSummary ( TestData::list_id ; TestData::list_id )?

 

Since you have defined List_ID as a summary field, why not just use the field by itself?

[bruceR]

And then again, if we DO use the getSummary( summaryField; sortField ) method then of course it does capture the list of IDs grouped by SortField, for the found set. This could be used in the Fast Summary technique; and it probably also works in sorted portals.

[bruceR]

I suspect the faster performance for the unstored native record ID calc is a mistake. It certainly isn't the result I get. 

 

My tests show 2X faster performance for using ListOf on the stored RecordID calc.

 

On a LAN I'm getting 135,000 ID/second for the stored native record ID calc.

I get half that for ListOf unstored record ID calc.

I get 56,000 ID/second for a ListOf on a stored UUID field.

I get 135,000 ID/second for ListOf on a stored integer ID field.

[Mark Scott]

Mark: why use GetSummary ( TestData::list_id ; TestData::list_id )?

 

Since you have defined List_ID as a summary field, why not just use the field by itself?

 

Great question, Bruce, with a simple answer to start with, but which also raises something BIG that may not have occurred to me.

 

First, the simple answer:  I was trying to directly ("apples to apples," as much as possible) compare vs. Hyperlist for gathering IDs into a variable.  Thus, I used two scripts:

 

Set Variable [$startTime; Value: Get ( CurrentTimeUTCMilliseconds )]

Perform Script [<hyperlist script to gather IDs into $$IDLIST>]

Set Variable [$endTime; Value: Get ( CurrentTimeUTCMilliseconds )]

 

vs.

 

Set Variable [$startTime; Value: Get ( CurrentTimeUTCMilliseconds )]

Set Variable [$$IDLIST; Value: GetSummary ( TestData::summary_listid ; TestData::summary_listid )]

Set Variable [$endTime; Value: Get ( CurrentTimeUTCMilliseconds )]

 

 

In the looooong run, however, I was thinking about gathering the values so that they could be referenced in a virtual list technique—you've heard of that technique, I presume?  —to do something along the lines of Todd Geist's Master-Detail approach.  The key value in each row in the virtual table would then be an unstored "GetValue ( $$IDLIST ; n )", where "n" is, of course, the row number.  

 

But your spot-on question made me wonder why not dispense with both the "Virtualize Found Set" script (to gather the IDs) and the $$IDLIST variable altogether and just define the unstored key in the virtual table as "GetValue ( summary_listid ; n)"?  Ideal, it would seem!   MInd you, I need to test it:  something in the back of my mind is saying that that won't work, but I can't put my finger on the reason, and would love for that little voice to be wrong.

 

Thanks,

 

Mark

[Mark Scott]

I suspect the faster performance for the unstored native record ID calc is a mistake. It certainly isn't the result I get. 

 

My tests show 2X faster performance for using ListOf on the stored RecordID calc.

 

On a LAN I'm getting 135,000 ID/second for the stored native record ID calc.

I get half that for ListOf unstored record ID calc.

I get 56,000 ID/second for a ListOf on a stored UUID field.

I get 135,000 ID/second for ListOf on a stored integer ID field.

 

Hi Bruce,

 

I believe we're getting the same relative result, if I'm reading your post correctly.  

 

My results using ListOf on 200K records:

 

Best performance on stored native RecordID: 1.0 sec, or 200K ID/sec

Second best performance on unstored native RecordID:  1.7 sec, or 118K ID/sec

Slowest performance on stored UUID field:  2.1 sec, or 95K ID/sec

 

(Unhosted, mind you, compared with your test over LAN.)

 

Best,

 

Mark

 

P.S.  And all of those times are 3–6-fold faster than their respective hyperlist counterparts.

[bruceR]

You did not answer the question.

 

Why use GetSummary? You don't need it.

It sounds like you are unclear about the difference between a summary field; and the GetSummary function.

They are two different things.

 

This is all you need:

 

Set Variable [$$IDLIST; TestData::summary_listid ]

[Mark Scott]

You did not answer the question.

 

Why use GetSummary? You don't need it.

 

This is all you need:

Set Variable [$$IDLIST; TestData::summary_listid ]

Oops, I guess I didn't even understand the question.  After reading your post, I was trying to do away with the Set Variable altogether, which might still be the ultimate goal.  Re the question as you actually intended it:  my answer—the one I'm sticking with—is "I dunno."  

 

(Just to be clear, the GetSummary, unnecessary as it was, was indeed based on the "Summary: List Of…" field you showed in your screenshot.  I'm certain, of course, that you realized that and were just pointing out [most helpfully!] that the GetSummary function itself was an extraneous intermediary here, but just making sure.)

 

Mark

 

Thanks for the tip, btw.

[Mark Scott]

BTW, I'll repeat (albeit not right at this moment) my 200K record-set tests using ListOf, without the superfluous GetSummary, and will report back with updated results.  

 

Mark

[bruceR]

Extraneous; yes. And requires a  sort operation, which could slow performance.

As mentioned in the other post, once we recognize this the feature can be put to good use when intentionally handling sorted data.

[Mark Scott]

Extraneous; yes. And requires a  sort operation, which could slow performance.

As mentioned in the other post, once we recognize this the feature can be put to good use when intentionally handling sorted data.

 

Quick question, Bruce—I don't have FMP13 in front of me at this moment to confirm one way or the other:  Does ListOf, by itself, not provide IDs in their current sort order, without the GetSummary?

 

(If so, then that would be the answer to both why I used GetSummary in my testing, and why a little voice is telling me that I can't get rid of it or the $$IDLIST var.  My goal was to see which was faster [btwn hyperlist and ListOf] for compiling sorted IDs from the found set, in order to implement in a master-detail layout that respects the current found set and sort order.  For other purposes, where sort order is immaterial, it could absolutely be faster to omit the GetSummary.  As mentioned, I'll update my test results accordingly.)

 

Thanks!

 

Mark

[David Jondreau]

Summary.List returns data in the order of the current found set.

[bruceR]

After looking further at the GetSummaryFunction; and doing some testing; it seems that though it is extraneous it doesn't make any performance difference if the summary field and break field are the same.

 

And as a matter of fact - this behavior of the function is an intentional feature of the GetSummary function in order to enable a grand summary result (value across all records of the found set).

 

From the function definition in HELP:

 

summaryField - field of type summary, or an expression that returns a reference to one.

 

breakField - field, or an expression that returns a reference to one.

To calculate a grand summary value, use the same summary field for both the summary field and the break field parameters.

 

GetSummary must be set up in the same table as the break field. 

 

https://fmhelp.filemaker.com/fmphelp_13/en/html/func_ref3.33.47.html#1029667

[Mark Scott]

Summary.List returns data in the order of the current found set.

 

Thanks, David!  That's great to know.

 

 

After looking further at the GetSummaryFunction; and doing some testing; it seems that though it is extraneous it doesn't make any performance difference if the summary field and break field are the same.

 

And as a matter of fact - this behavior of the function is an intentional feature of the GetSummary function in order to enable a grand summary result (value across all records of the found set).

 

From the function definition in HELP:

 

summaryField - field of type summary, or an expression that returns a reference to one.

 

breakField - field, or an expression that returns a reference to one.

To calculate a grand summary value, use the same summary field for both the summary field and the break field parameters.

 

GetSummary must be set up in the same table as the break field. 

 

 

Yes, that description in Help is precisely where I originally got my syntax from, but I still don't remember why I even thought to go that route, rather than the simpler approach you suggested.  Probably the latter just escaped me at the time.  At least it's heartening to hear that it doesn't make any difference in speed.  Thanks for checking, Bruce.

 

(Now to test out virtual list, and master-detail, by directly referencing the ListOf field without a global variable (or the script to declare it) at all…  Looking forward to playing around with that a bit.)

 

Mark

[Mark Scott]

OK, two follow-ups:

 

1.  Not that I expected any different results than yours, Bruce, but since I had promised to test the "direct" (referencing the ListOf field without the unnecessary GetSummary( ) function) approach on my 200K record set, it seemed only proper to fulfill that promise. In full agreement with your observations, the times were identical to the GetSummary-based approach.  'Nuff said.

 

2.  Re the other idea I raised, namely dispensing with the $$VARIABLE and script for a master-detail layout, I thought about it on my walk home this evening and that "little voice" I mentioned earlier spoke up loud and clear this time. There is a glaringly obvious reason why I can't just reference the ListOf function in the virtual-list table's unstored key (i.e., "=GetValue ( summary_listid ; n )"), rather than "=GetValue ( $$IDLIST ; n )", where "n" is, of course, the row number. The virtual-list table has no concept of the main table's found set, so referencing the summary field (with or without GetSummary) just won't work!  The ID list has to be set (by script) into a $$VARIABLE in order to be accessed by the key's unstored calc. What aggravates me is that I had thought this whole thing through once before and should have remembered that.

 

 

For anyone stumbling onto this thread who isn't familiar with the Master-Detail layout technique, check it out at the Modular FileMaker site. Crafted by Todd Geist, it's based on a combination of his hyperlist script and Bruce's virtual-list technique (in one of its many flavors). The important point here is that the main table (let's call it "Customer") is joined to the virtual-list table by a "≥" join between found count, set into a global field in Customer, and the stored row-number field ("n") in the virtual-list table. The only other field in the latter table is the "key" field that is an unstored calc mentioned above that, as mentioned, has no knowledge of Customer's found set, and thus cannot simply reference it through the ListOf field in Customer. That's why the found-set ID list has to be pushed into a global variable by script that is run from the context of Customer, and can thus pull its current found set's ID list out of the ListOf field. The final component of the technique is an equijoin from that unstored key in the virtual-list table to the respective field—most likely either a UUID-based primary key or a stored version of Get(RecordID)—in a second TO of Customer.  Very elegant!

 

Best,

 

Mark

[bruceR]

My results for HyperList V2 vs ListOf.

 

NOTE: this is performed on-machine, not LAN or WAN.

[bruceR]

Results on 2013 MacBookPro w/Retina/SSD

Local tests only

(IDN = simple integer ID field)

 

[Mark Scott]

Thanks, Bruce: a great service!  Just for reference, do I understand correctly that the Hyperlist V2 tests were executed against the UUID field?

 

Mark

 

BTW, not that I should be surprised, but your 2013 MacBook Pro tests were about 40% faster than my 2011 MBA (1.7 GHz Core i5).  What machine did you use for the first set of tests?

[bruceR]

Yes, the Hyperlist v2 tests are unchanged from the original example file ( ID field, which is a text UUID field )

 

First graph is from a 2009 Mac Mini 3.1 with 8GB memory, SSD, 2.53GHz Core 2 Duo, OSX 10.8.5

All my tests were local tests.

[Charity]

Does this mean that we can use Perform Script On Server to find the records, sort them, get summary List Of their IDs then put them in a global variable as a list that can be retrieved when this script ends?  Great discussion.

[David Jondreau]

Pretty much. Except you wouldn't use a global variable but a script result. 

[Charity]

Is it because you can't pass a global variable from one file to another?  Or instead is it because you can't pass a global variable to one client from another?  Or something else?  IOW, why can't you use global variable.

 

Thank you very much, David.

[David Jondreau]

Well, a global variable is user- and file-specific. So, in a sense, both of those things are true, but your terminology is off since you can pass a variable, using scripts for example, but the variable is not shared. And the issue with PSOS[] is that the server is another user; obviously, the script is working on the same file.

[Charity]

I appreciate the explanation and I hope John does not mind my question on his post.  I was wondering because if a User is restricted and PSOS runs will it automatically omit records if person does not have record access?  Will all records of the find be pushed to client and then omitted like now or will they not be pushed to client at all since the find will take place on that client?  I have no idea how to even test that.  

 

If that summary.ListOf skipped those records that would be great.  Thank you again.  

[bruceR]

Good question. Tested this briefly. PSOS scripts appear to be performed on the server with the current privilege set.

Which makes sense.

Yes, ListOf respects the privilege set and only returns IDs of the records the user is allowed to see.

 

I wrote a script with the following steps.

I wrote a second script that does PSOS with this script, and displays the result.

The result in my case was:

 

100

userwa

12

 

Set Error Capture [ On ]
Go to Layout [ “Customer Details” (Customers) ]

Show All Records

Set Variable [ $fc; Value:Get( FoundCount ) ]
Set Variable [ $ps; Value:Get( AccountPrivilegeSetName) ]

Set Variable [ $list; Value:Customers::sListOf_ID ]

Go to Layout [ original layout ]
Exit Script [ Result: List( $fc; $ps ; ValueCount( $list )) ] 

[Charity]

Hello Bruce,

 

I understand that even if fields are restricted, the record must still download of course but if record is restricted then I won't have to constrain.  What a good test and thank you for showing me.  

 

So can server sort for us also?  Currently the records are downloaded and clients sorts, right?  Would server keep the records there? And would not a regular indexed sort be faster by the server-client than sorting through GetSummary which is an unstored calculation?  Or am I missing something here in this discussion about GetSummary?

 

Again, I thank you.  This forum is a life-saver.

[bruceR]

I don't understand anything you are saying.

You asked to perform a server side script. If the script has a sort operation why would it not be performed?

[Charity]

I was told that sorts must be downloaded to client so try not to sort.  This was on 12. 

 

And your discussion about GetSummary I thought was because of this sorting issue on how to perform a sort there instead.