Gathering LIST from found set in v11 vs v12

[LaRetta]

I've finally had some free time to spend on testing some of the things which puzzle me. One was suggestion by BruceR to use Replace Field Contents[] against a global for populating variables, such as a list of IDs from a found set. The results on 6,000 records were quite impressive:

Loop in version 11 - 19 seconds

Global in version 11 - 1 second

I have a new favorite technique. Thank you Bruce from this post ... see post #7

http://fmforums.com/...ds/#entry394842

Even more astonishing ... when I converted the file to 12 and ran the same tests:

Loop in version 12 - 2 seconds

Global in version 12 - 1 second

And I was sure to restart FM between tests. If anyone would like the test file just ask. For those who are worried about version 12's speed this should impress. And it isn't because we aren't setting the global with a value either ... I tested setting the global thinking the need to index/commit might slow it down but not by much ... only takes 2 seconds if I set the global with a value.

I thought others would be as interested in the results as I was!

[HOnza]

What an interesting discovery! Can't wait to try it out myself. Thanks for sharing!

HOnza

[LaRetta]

Hi HOnza,

Well, I discovered the loop only appears to be significantly different if using table view. I assume it's because FMI changed the layout redraw. But the difference using RFC over loop is also very impressive - go Bruce! Every nanosecond counts. What a cool way to create lists (of any kind)! :laugh2:

[bruceR]

It does have the limitation that for large found sets or other circumstances that affect speed of the replace operation, you may see the replace progress window with its "Records remaining to update" message. Since we are targeting a global, no data is really being replaced. But the message might be disconcerting to users and can't be suppressed.

Glad you like the technique. I've mentioned it a few times, such as when we were all discussing the "Fast Variables" file by Ralph Learmont with some mods by me.

[Daniel Wood]

Just been doing some testing with this technique as I have a similar issue with obtaining primary keys from a found set of records. Currently I'm using List function via the parent to grab the IDs (which can number 20,000+) .

As with everything in FM over WAN the big performance killer is the record size & pulling down entire records from server to client. It appears that when using the replace function, even though technically you may think nothing is being replaced on the records, in my testing the replace method is considerably slower than using List through relationship:

Test: listing 95,000 primary key fields from table

List function: 40 seconds

Replace function: over 10 minutes, had to cancel it

Looking at activity monitor it was obvious that all the records were being pulled down from server, just like the list function, but there was some traffic in the opposite direction, so client was pushing data back to server for whatever reason - perhaps this is just inherint in using the replace function, global replace or otherwise.

Also interesting was the replace function started our relatively quick, but it quickly got slower and slower and slower to the point where it began to crawl - my assumption here is that the overhead involved in appending 1 ID to the end of a variable list is getting more and more costly an operation as the size of the list increases.

In short - replace is a great technique for small found sets on local hosted solutions, but not a good solution for larger found sets over wan or some lan setups. I do love the outside the box thinking on using replace though!

[bruceR]

Daniel, I wonder if you could test a variation on the technique by using concatenate instead of the list function:

Set variable [ $temp; ""]

Replace Field Contents [ zYourGlobalField; Let( $temp =$temp & ¶ & contacts::pkContactID); "") No Dialog ]

Set variable [$$IDList; $temp]

[bruceR]

I've also found that "chunking" the replace operation into batches of 5000, then accumulating the chunks seems to speed things up a lot.

List_usingbatchops.pdf

[Daniel Wood]

hi Bruce,

I've just made that change and re-tested. Although now the speed at which the replace happens is consistent, it is still consistently slower than list.

While the replace is happening (on 95,000 records) I am monitoring the activity monitor network bandwidth and noticed something interesting.

Download speed: 250 kilobytes /sec (roughly capped around there it would seem)

Upload speed: 7kb/sec

However using the List function:

Download speed: 2.5 megabytes / sec

Upload speed: 70kb/sec

Using replace the network average for downstream/upstream is considerably slower than List. I can't explain why with certainty but if I had to assume I'd say replace has overhead in preparing groups of records to send and receive and perhaps there are more packets being sent or something, in any event the network traffic average speed is around 10x slower than the list function - list appears to just go hell for leather and pull down records as its sole job.

[dansmith65]

I've been wondering about the performance of different methods of gathering record ID's too. I recently found that doing a Go To Next Record based loop in Form view is the fastest looping method, and one of the fastest methods of all.

I've attached a sample file that gathers ID's in a bunch of different ways, and shows you the time per method. To match LaRetta's test, I created 6000 records, but to get an accurate result you need a sub-second timer function. If you don't want to use a plug-in that can do this, then I suggest creating 10x more records and using the Get( CurrentTime ) function.

gatherIDs.zip

[Daniel Wood]

hi dan, thanks for supplying that demo file.

A couple of things however with that file. First if running it not on a server then the results are reflective of the processing time of the various methods because all the records are already local on disk.

However put this on server and you get noticeably different results. Also running all tests one after the other is not a good indicator for lan/wan solutions because once the first test grabs the IDs, those records are downloaded and cached locally and subsequent tests will be faster than if they were run on a freshly opened solution.

So what I have done is run each test individually, closing and reopening the solution each time, here are the results:

Next record (form): 1.390

Next record (list): 3.562

Next record (table): 4.427

Getnthrecord (form): 3.073

Getnthrecord (list): 3.108

Getnthrecord (table): 3.574

Customlist CF: 3.541

Replace field: 1.108

List: 0.640

End result - list wins. GetNthRecord is probably not your best option nor a custom function probably due to memory/stack overheads. Next record (form) is pretty good but on lan/wan the overhead of looping and downloading individual records as it loops is proving costly. There is just something about the List function that makes it more efficient for bulk-retrieval of records which makes it more optimised than a loop.

[dansmith65]

Thanks for pointing out some of the pitfalls of that test file - I really should have mentioned those things in my post. Another possible issue with my test file is that the table only has a single field (and a global). I'd be curious to know how the results differ when there are other data fields in that table. And as LaRetta found, the results for the "Next record" method differ greatly in FM12 - here is little/no difference between the form/list/table variants of that method.

For a context-independent method, "Next record" (form) is surprisingly fast. What I mean by context-independent is that you could write a script that takes the name of a field as a parameter (using GetFieldName(), rather than hard-coding the field name, of course), and will return a list of all the values in the current found set for that field. While not the king of speed, it is pretty fast, and is the king of ease, since this one script could return all the values for any field, from any table, from any layout in your database.

[comment]

Previous discussion on this topic:

http://fmforums.com/forum/topic/61394-found-set/

See also:

http://fmforums.com/forum/topic/59929-relationship-based-upon-not-related/page__st__20#entry283634

---

BTW, i wonder why you're not including ExecueSQL() in the comparisons.

Edited September 10, 2012 by comment

[LaRetta]

Hi Daniel, of course List() won't work for found sets, right? Or do you use globals to narrow the results first? That's why the global excited me - working with found sets. And the focus is populating a variable or Copy All Records could be used. Thanks for contributing - I posted before testing all options because it so excited me, LOL, and your tests are invaluable.

Hi Dan, yep I know the tricks such as using form view instead of table, using null layout etc ... my focus was on comparing the speed of two techniques on writing to variable and I grabbed the closest test file. Thank you for the demo file, I'll check it out when I get back to office.

There have been some great posts on gathering IDs of a found set ... I want to review all those great discussions and re-test them all, including Agnes Barouh's calc. With 12 everything should be re-tested. I doubt we've discovered more than 20% of the possibilities in 12. What fun ahead!

UPDATE: my apology ... I had originally written Agnes Riley instead of Agnes Barouh.

[LaRetta]

LOL, speaking of ... :-)

This was for version 11, Michael. I converted to 12 only to observe any changes in behavior. But if we're looking to best technique now then why not ExecuteSQL()?

UPDATE: Yep, that's the threads I was looking for! :^)

Edited September 10, 2012 by LaRetta

[Daniel Wood]

ExecuteSQL seems to perform similar (and worse in a lot of cases) than list or standard loops (at least from what I have seen).

I should have pointed out LaRetta that yes I have a relationship to the records in question so I am able to use List, if it was based on a found set it might be a different story

Somewhat related, I thought I would mention that recently for grabbing IDs that are used for anything other than storing, that there is a huge performance increase to be had from using the Get(RecordID) function instead. Now, storage is a huge issue for this function because the internal record ID's are reset to 1 on a clone of a file - and there are complications when restoring backups and so on.

BUT, if your main goal of grabbing IDs is merely for short-term purposes, eg using in a relationship or searching - then using this instead is hugely faster.

If you setup an unstored calc in your table which references get(recordID) then when you obtain this calculation either via found set or via a relationship, FileMaker will NOT send you entire record contents, only the IDs - it is comparable to the speed of getting a value list which is built using the 'all values' option - not all record contents are sent.

So, in a solution I've built recently I have relationships setup to perform some quite complex searching whereby I needed to get the results back somehow for purposes like GTRR, showing totals breakdowns and so on.

Over WAN, it takes 1.2 seconds to return 95,000 internal record IDs, compared with 3 minutes for the primary key field - that's huge!

Your table can also contain a stored calc for get(recordID) if you need to setup relationships to your list of IDs to records.... but just remember it shouldn't be used as a means to store those IDs historically because they will eventually break...

[LaRetta]

If you setup an unstored calc in your table which references get(recordID) then when you obtain this calculation either via found set or via a relationship, FileMaker will NOT send you entire record contents, only the IDs - it is comparable to the speed of getting a value list which is built using the 'all values' option - not all record contents are sent.

Sorry but I cannot read further until I regain consciousness after reading this mind-blowing sentence. Now with FMGo, speed is even more critical. I'm off to test. THANK YOU. :drool:

[Daniel Wood]

np Another good use of using the Get(RecordID) instead would be for grabbing record IDs for the purposes of compiling virtual lists - of course subsequently displaying any related data via the virtual list is going to cause slowdown because the entire record will be pulled thru, but at least to generate the IDs for the VL will be fast.

[dansmith65]

Sorry but I cannot read further until I regain consciousness after reading this mind-blowing sentence. Now with FMGo, speed is even more critical. I'm off to test. THANK YOU.

Same here!

[Daniel Wood]

I should also clarify that 'all values' value list statement - the value list can only contain first field - once you use the 'show second field' option, FileMaker has to pull down all the records in the value list to render that second field, so make sure the value list uses first field only - I believe it can do this quicker because it can get away with just sending the index to generate the value list, but I am probably wrong on that

YOu can still use it to do a level of customised value list, for example if you want just records with a status of "Pending" you could need a stored calc on your table which was something like:

if ( Status = "Pending" ; PrimaryKey ; "" )

then base your value list on this calc field instead of primary key, all the blank records aren't going to be in the VL so you end up with a quick list of active primary keys.

[bruceR]

Here are several of my comments.

Building large lists by adding one list item at a time is inefficient.

(GFR = global field replace)

50,000 records, GFR, non-batched

Let ( $list = $list & ¶ & Contacts::contactID; "" )

85 seconds

50,000 records, GFR, batch and collect, append, as described in prior post:

Let ( [

N1 = Get( RecordNumber) ;

N2 = Div( N1; $chunksize ) ;

$z [ n2 ] = $z[n2] & ¶ & Contacts::contactID; ] ; "" )

5 seconds

50,000 records, GFR, write to individual repeating variables, do not collect:

Let ([

N = Get( RecordNumber);

$$globalVar[N] =Contacts::contactID ]

; "" )

3 seconds

Note that for virtual list techniques, there are many variations on the technique.

The original technique for the displayVariable field is a calc like this.

Where N is the number field.

displayVariable calc = GetValue( $$globalVar; N)

A much faster alternative is to use a repeating global variable:

displayVariable calc = $$globalVar[N]

Besides other benefits this allows for empty values to remain in their correct position in the list and allows items with embedded returns in the list.

If you use a $$global variable this means that you can end up with a very large list of $$ variables to dispose of. The technique can be used with script variables also, then you don't have the need to dispose of the list items.

[Dave Graham]

I read somewhere that prefixing a list is much faster than appending. Something to do with overhead in determining where the end of the list is, which gets bigger the longer the list is. Has anyone tested the replace approach with prefix vs. append?

[bruceR]

Very brief test with LAN, 20k records, marginal improvement for prepend vs append. 17 seconds append at end; 16 seconds prefix method. Vs. 3 seconds to populate $var[N].

[dansmith65]

I've been back at this again. I converted my test file to FileMaker 12 format, added sample data, and added a few new testing scripts. Here are my findings when using an iPad as a client and FM Pro Advanced as a server, closing the database after each test:

• “Copy” method is the fastest method to use for gathering id's for the found set
  
• When the id field is a UUID, processing time increases approx. 300% for looping AND Replace methods, but does not affect processing time of Copy, List, or SQL methods
  
• All “Go To Next Record” loop runs just as fast in form, list, or table view (in FileMaker 12)
  
• Pre-pending a variable instead of appending took slightly longer
  
• Running the test again without closing the database produced the same result as when opening a copy of the database stored on the iPad
  
• Adding 10 data fields decreased speed by approx 10%
  

Please note that the List and SQL tests do not act on the found set.

gatherIDs2.zip