1 2 Previous Next 23 Replies Latest reply on Mar 3, 2016 11:21 AM by sfpx

    Ultra slow new record creation mystery

    Vincent_L

      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.

       

       

       


        • 1. Re: Ultra slow new record creation mystery
          Stephen Huston

          I have run a scripted record creation process that created tens of thousands of records in the time it took yours to do 360, so I suspect that the details of the script are important.

          • If the script is setting the ID via a complex calculation, that may be slow in calculating. What is the calc?
          • If you are add a commit record step after the Set Field, does it speed up or slow down the scripted creation process?
          • Are there any field validations which are part of field definitions? If so, they are having to execute as each record is created and committed.
          • Are there any script triggers anywhere on this layout?
          • 2. Re: Ultra slow new record creation mystery
            user10625

            Hi Vincent,

             

            I do a lot of work on a system with a huge number of T.O.s, and see similar issues, especially in tables that are "well-connected", nexii (sic?) of the system.  It even slows down record-creation using the well-beloved "create through a relationship" method.   It also slows down Replace-Field-contents operations, though, interestingly, only on the particular fields that are "connected" (i.e. usually ID fields and such.)

             

            The solution I use when I need really fast creation is as follows:

             

            1)  Create a utility table connected to nothing whatsoever, a shadow version of the real table you need to create records in.  (with the same fields, or a necessary subset thereof.)

             

            2)  Make a looping script that makes the records in that utility table (should be very fast.)

             

            3)  Then import the set of records you created into the real table. (For some reason, I have found imports to be quite fast, despite slowness elsewhere---especially in the cases where you are creating only a handful of records, it's almost instantaeneous)

             

            Hope that helps.

             

            Thanks,

            Christopher Bailey

            Baytaflow

            Boston, MA

            • 3. Re: Ultra slow new record creation mystery
              Vincent_L

              Script is as simple as it gets

               

              freeze

              go to blank layout

              loop

              new record

              set field (PK) to "whatever"

              end loop

              commit (here or before end loop same thing)

               

              no problem on the script, which works perfect in other tables

              no validation

              no script triggers

              • 4. Re: Ultra slow new record creation mystery
                Vincent_L

                Thanks Christopher,

                 

                I feel less lonly. In my opinion this shouldn't happen, it's not logical so it might be wise to consider this as a "bug"

                 

                Thansk for your alternative, but the point of my script (whoch is not relevant to discuss, is to be completly versatile, that's a kind of virtual table, and unforntunately imports matching are not scriptable).

                 

                Moreover, I think that may also explain some slow deletes and evens slow imports, and slow replaces as you noted.

                 

                So FMI should fix it

                • 5. Re: Ultra slow new record creation mystery
                  BruceHerbach

                  Over the years there have been a number of discussions about how large numbers of TOs slow down a database.  Frequently this comes up in discussions about Anchor Bouy performance.

                   

                  1400 does seem like a fairly large relationship graph and is probably contributing to your slowness issue.  I recently was doing a test for something else,  and setup a single table DB and created 20000 records as part of loop.  This took a little over a minute.

                   

                  Bruce

                  • 6. Re: Ultra slow new record creation mystery
                    Vincent_L

                    In my case, even though I've 1400 TO. Deleting all the TO's except one of this Table gives back normal speed. So It's not the weight of the 1400 but something in the screenshot's right column that has problem.

                    Moreover, If I only keep the n+2 relationship from that table, and delete all the other tables, keeping just about 1 hundred I cut the time in halfs, but that's still too slow to be considerer normal.

                     

                    I think I can explain the x2 speed up by the fact that probably one or more of the offending TO can have n+5 relationship. But anyway those shouldn't be evaluated accordting to my knowsledge of FMP and sense of logic (which of course are limited)

                    • 7. Re: Ultra slow new record creation mystery
                      BruceHerbach

                      Vincent,

                       

                      I'm not sure I follow what you are saying.

                       

                      "In my case, even though I've 1400 TO. Deleting all the TO's except one of this Table gives back normal speed"  Do you mean that reducing the graph to a single TO for this table returns the record creation to normal speed?  If so then the size of the graph and the amount of relationships this table is involved with does matter.

                       

                      I know it is very hard to go back and redesign this, but  I wonder if you could use ExecuteSQL() to get the values you need for some of the TOs you show in the screenshot?

                       

                      I find that using the new ExecuteSQL() function has allowed me to develop solutions with a much simpler graph.  I think overall this has improved performance on these databases.  As an example,  I'm working on one now that has a report with a series of distinct numeric values.  As in how many of X are there.  With out ExecuteSQL,  I would have to either go to 20 different layouts and do a find with get(foundcount) or setup a relatioship to 20 tables and a global field to filter what I want and get the 20 values I need for this report.

                       

                      Instead,  I use the ExecuteSQL function,  from the report layout.  This greatly simplifies the script and added 0 TOs to the graph.  I'm not an SQL expert,  but I have been finding  SeedCodes SQLExplorer and Beverly Voth's ExecuteSQL the missing manual to be incredibly helpful in developing the SQL I need. 

                       

                      Anyway I understand your frustration and difficulites with the slow response. 

                      Bruce

                      • 8. Re: Ultra slow new record creation mystery
                        Stephen Huston

                        As Bruce indicated, your comment about the regained speed if you clear out the huge TO count indicates the relationships are at issue.

                         

                        I am still curious about the script step you described as:

                              set field (PK) to "whatever"

                        Does that mean that you have tried multiple options for setting it -- simple serial numbers, complex calcs -- and it makes no difference? I would think a calc would cause some slowing, and a really complex calc, especially if it used related data from your huge graph, could have a major impact.

                         

                        Simplying the TO graph can be real chore, but most of the huge graph systems I've seen were built before portal filtering and executeSQL allowed a single TO to work in place of dozens or even hundreds of variants, so there's a good chance that some portals can be repointed and filtered, or calcs can be converted to SQL results without the need for special-purposed relationships. Everything will speed up and become much simpler to maintain down the road.

                         

                        Meanwhile, you have our sympathies.

                        • 9. Re: Ultra slow new record creation mystery
                          Vincent_L

                           

                          "In my case, even though I've 1400 TO. Deleting all the TO's except one of this Table gives back normal speed"  Do you meant that reducing the graph to a single TO for this table returns the record creation to normal speed?  If so then the size of the graph and the amount of relationships this table is involved with does matter.

                           

                          I meant, all the right colum TO (all the TO of THIS table, except one of that table). So There's still about 1390 TO's

                           

                          Of course the right column TO's are the problem. But what matters is that there's no reasons they would matter because as on bblank layout, without script trigger, validation, auto-enter there's no reason they should be avealuated. That means that Filemaker is doing some evaluation in our back, evaluations it shouldn't make at all. And that's what is shocking and the crux of that thread.

                           

                          I hear you redign, ExecuteSQL tehy're are all valid point. But solving my solution is not th ethread Goal. The Goal is to highlight a, bad, unexpected behaviour, or at leat trying to understand it. Because that may be a bug, which slows down the palttform.

                          • 10. Re: Ultra slow new record creation mystery
                            Vincent_L

                            Yes but that's not the point. I've tried to just put "1" inside. Same thing. So that's just a very simple loop, with completly mundane value.

                             

                            The fact that a lot of Relationship exists shouln't matter at all, because if you're on a standalone layout, without anything, nothing should be evaluated. So to me that's a bug we should not be exposed too.

                            • 11. Re: Ultra slow new record creation mystery
                              user10625

                              > I am still curious about the script step you described as:

                              >     set field (PK) to "whatever"

                              > Does that mean that you have tried multiple options for setting it -- simple serial

                              > numbers, complex calcs -- and it makes no difference? I would think a calc would cause

                              > some slowing, and a really complex calc, especially if it used related data from your huge

                              >  graph, could have a major impact.

                               

                               

                              You'd think that, but in my experience with this kind of thing . . no, that doesn't make much of a difference.   I have experimented with this . . especially with "Replace field contents", and the complexity of the calc seems to make little or no difference, whereas, the #1 issue seems to be 

                               

                              1)  Big graph

                              2)  The field you are setting  (or doing replace records on) is very "connected" on the graph . . which a PK is likely to be.  (Other fields on the same table . . unconnected to things, have no performance hit.)

                               

                               

                              I echo Vincent's puzzlement . . .  ideally, one would think that context would determine how much a huge graph would slow your life down.  So if you are on a layout with no unstored calcs and no portals--nothing that makes use of the graph, in other words, there should be no performance hit.  But alas ....

                               

                               

                              Chris Bailey

                              Baytaflow

                              Boston, MA

                              • 12. Re: Ultra slow new record creation mystery
                                Stephen Huston

                                You have indeed found what some would call a bug, and you can report bugs via FMI regular site.

                                 

                                I still cannot find a Bug Reporting link from this forum which seems strange, but there is one at:

                                 

                                http://forums.filemaker.com/hives/1eea103f05/summary

                                 

                                Whether of not FMI will consider your report significant is an unknown, but including what we suspect is causing the problem will be useful info for having it evaluated by an engineer.

                                • 13. Re: Ultra slow new record creation mystery
                                  BruceHerbach

                                  Vincent,

                                   

                                  Even if the layout is blank,  if it is on a TO that is connected to other TOs,  FileMaker still has to evaluate the relationships when you create the record.  So there is overhead. 

                                   

                                  If it needs to download a record for this,  I believe that it actually downloads 25 records.  Something I vaguely remember from an under the hood session at Devcon last summer. 

                                   

                                  Bruce

                                  • 14. Re: Ultra slow new record creation mystery
                                    Vincent_L

                                    I agree but of course there's no connection, no relationships at all from or to that TO on which the layout is based. Look at teh screenshot, that's the TO on the left

                                    and also, it occurs on a clone file, without any records !

                                     

                                     

                                    Even if the layout is blank,  if it is on a TO that is connected to other TOs,  FileMaker still has to evaluate the relationships when you create the record.  So there is overhead. 

                                    1 2 Previous Next