Vincent_L

Ultra slow new record creation mystery

Discussion created by Vincent_L on Mar 18, 2014
Latest reply on Mar 3, 2016 by sfpx

Hi,

 

DISCLAIMER : The purpose is not to help me about my solution (I've other options), or discuss about best way to create records (I've other options), it's trying to explain why a strange behaviour occurs.

 

I made a script that creates a bunch of records via looping new record script step.

(I know this is not the most efficient method, but for that scenario, this is the most versatile.)

 

This script targets a particular table, of course on a blank form layout with a window freeze. To create 360 records and set the primary key of that table it takes 1 minutes 7 seconds. That's an awfull lot, especially since thousands of records needs to be created. This is totally anormal since the only field it sets, the primary key is just a 11 characters text and if I create a new database, doing the excat same thing will take less than 1 second. So the problems is in that table.

 

- There's no auto enter fields involved

- It takes teh same time on a clone file without any records

- The blank layout TO is not involved in any relationship

- I created a new TO of that table and new blank layout, same slowness

- There's no layout scripts.

- I did try to repair the files, and alos converted it to 13, same slowness

- No external datasource, local standalone FMP11 file

 

Since there's no fields on that blank layout, no relationship for that TO, no network (it's local standalone), no scripts I can't see why the slowness, because normally nothing should be evaluated, especially on a clone file without any records.

 

Howewer That Table has a dozen of TO (not linked to the blank layout TO), involved in some other relatiosnhip. If I delete all those other TO, then the record creation flies at normal speed, les than 1 seconds.

 

Pure record creation "new record" without the set field for the primary key is instantaneous, only if I set the PK (didn't tried with other field) the slowness occus.

Of course the other TO use that PK, but again, I don't see any reasoon why they should be evaluated at all since the blank layout TO is not involved in anything

 

So of course, even if I can't see why they would matter I tried to narrow the offending other TO by deleting thos TO one by one and time the results.

Some TO deletion produced 0 speed gains, while some did a bit, but not a day and night difference. But in the end I managed to narrow down 6 TO. But the amount of speed gain they each provides varies depending the oder of deletion. So very hard to find the real issue.

 

 

That table and it's TO lives in a big file with a 1400 relations, so I created a new file with just one TO to that table, so the big file wouldn't be really opened (just in teh background to serve the TO). Running the script in that new file lead to a 2 times speed boost going from 67 seconds to about 33s. But that's still not the 1 second I'm looking for. So that's still slow.

 

This defies a lot of my FMP Knowlegde :

 

- a blank layout with dedicated TO linked to nothing, shouldn't evaluate anything and so the record creation shoukd be instant. Why would it be burdened by something out of it's contest.

- how come a blank file with a TO to a table of a big file could yield better speed than the main big file oepened alos on a equivalent blank layout with similar TO. Speed should be same, or better in the native big file, otherwise that would mean that we should all create blank file pointing to simple TO of other files to create records, maybe delete, maybe import.

 

 

So can someone see a pattern he encountered here, do you think that's as weird as it seems to me, did you encountered very slow reacord creation.

 

 

 


Attachments

Outcomes