AnsweredAssumed Answered

Terribly Slow New Record with set fields Creation

Question asked by Vincent_L on Jun 27, 2018
Latest reply on Oct 10, 2018 by jormond

Hi,

 

I have terribly slow record creation on some of my tables, while using the New record script step inside a loop (on a blank form view layout, with freeze window step).

Only 250 records take a huge 50s to be created. That's very slow, especially as there's only 2 short fields sets (10 char text, one number field).

 

I immediately though about complex calc and relationship issue.

So I singled-out the TOs of the layout used for the creation to make sure that it's not linked to anything.

I removed all calculations fields, or auto-enters

I even deleted all the external data source

No script triggers, nor ontimer scripts

I deleted all records, so the issue is also there with no records at all !

 

Still same slowness.

 

As there's nothing (no liked relationship, no calcs, even no field displayed on the blank form view layout), that can trigger any relationship, there's no reason whatsoever for that slowness.

 

I've attached a mini solution which is a huge simplification of my real solution. I only kept the 2 used tables. Deleted all external datasources.

 

There's 3 files :

SlowNewRecordsImports.fmp12 : main file to be used, 4 timing script in the script menu

250.txt : a file used to test import speed

SeparateFile.fmp12 : that runs the same script as the main file, but from a separate file, so script works from an almost TO free environment.

 

Those files bench the 4 known methods to create records :

 

1. New records script step

2. Imports

3. Relationship

4. Insert Into with BaseElement plug-in using FMP SQL

 

Test              Original File          Separate File

1  New                50,819s              30,659s

2  Import              0,022s                 0,022s

3  Relat.               0,102s                 0,155s

4  Insert               27,62s                 29,031s

 

Test 1 difference is puzzling. I know this is the devcon trick mentioned before. But that trick as logical explanation when the main TO is linked to other TO. This is not the case here as in main table, the main TO is singled-out.

 

Test 2 huge speed advantage, vs Test 1, is proof that New records is doing some huge overhead for just nothing. Imports (especially with auto-enter on, as this is the case), do resolve calculation like new record does. So there's no practical advantage, or justification to the new record step, that gives exactly the same results.

 

Don't get psyched but the main file busy relationship. Yes its huge and ugly, but keep in mind that there's no logical reason for the new record script step to trigger the relationships, since there's no calcs, no fields on layout, not even relationship link from the main layout TO.

 

Also keep in mind that, import (auto-enter on) that does the same job for record creation does it almost instantaneously. So there's no reason for the new record scrip step's overhead, it doesn't provide any usable advantage.

 

Don't fret about the broken TOs (the one on the right that have no files), those are resulting from my external data source deletion. In my actual solution with them functioning, the speed issue is the same. So it's not the fact that they are broken that causes the issue.

 

This issue raises several concerns :

- Why is new record script step triggering not linked TOs, with no logical trigger (while imports/relation create does the job just fine). That overhead is useless.

- Why broken TO get evaluated (cause deleting them speeds the script to 6 seconds), they should be.

- Why is the separate file faster, as there's no logically triggered relationship in the main file

 

You may think that this is an extreme, unpractical case. But it's very real world :

 

The "bridge" table is my products table, the "Sélection" is a selection of products. the broken relationships are products inventory in our x stores (so x To)

 

So basically if the unlinked, non logically triggered relationship, would create overhead.

That would mean that each time I select a product (adding it's record in "Sélections" table), filemaker checks for it's availability in my 10 stores (even though there's no TO / or field on the layout that uses them) ?

That wouldn't make any sense. What would have the store availability to do with a selection ?

 

Moreover, even if I just had 10 relationship for my stores (rather than the 781 displayed).

That would means that my record creation could be 10 times slower than if I just had 1 store (sure that would be milliseconds here, but they add up quickly). That doesn't make sense.

 

Many people have lots of relationship (filemaker's way encourage, and sometimes forces this), many people do use loops to create records (especially newbies, that are a main target of FMI), so I think that issue is critical.

 

Moreover, that unexpected slowness, could have many more performance implications in many areas yet to be found.

 

This happens in 16 and 17 (presumably also older versions), on Mac and PCs.

Outcomes