1 2 Previous Next 20 Replies Latest reply on Sep 23, 2012 5:44 PM by comment

    How to best create lots of new records quickly?

    calexmac

      We are working on a solution that does some simulation in the medical field.

      The simulation element occasionally requires the creation of a large number of records. Sometimes half a million.

      Our challenge is to do this quickly.

      If we use a very simple script:

       

      loop

      exit if created the right number of records

      create a new record

      end loop

       

      ... it runs quickly at first, creating lots of records in the first few seconds of execution.

      Then after about 50k of records its creation rate starts to slow down and then slows right down after ~100k of new records, adding a new record every few seconds!

       

      The record only has one field, a text field, with no calculation or relationships, so it couldn't be simpler.

       

      On the other hand, if we export 100k of these newly created records (blank - with no data), and then import them and set the 'create a new record' flag, the import is really fast.

      We can build a table with a few million blank records very quickly (subtracting the time to manually do it).

      The manual export/import is not a viable process and was just a test to see if FMP12 had a systemic record creation problem (which it doesnt seem to have).

       

      So the ? is perhaps 2 questions:

       

      ?1 - what is FMP12 doing behind the scenes on record creation that we need to know about?

      ?2 - any ideas for progammatically creating lots of new records quickly?

       

      Thanks in advance

       

      C

        • 1. Re: How to best create lots of new records quickly?
          icook

          Have you tried running your loop script on a blank layout?

           

          Ian

          1 of 1 people found this helpful
          • 2. Re: How to best create lots of new records quickly?
            wsvp

            Justy a shot in the dark... You might try tinkering with the FM Memory Cache size...

            • 3. Re: How to best create lots of new records quickly?
              calexmac

              Great suggestion.

              Just created a blank layout.  Re-ran the script and it completed way more quickly.

              Created 1,000,000 blank records in 2m34s. (mac 2.8ghz intel core i7)

              Seems like this is absolutely part of the solution.

               

              Curiously, the import still works faster.  Exporting the 1million blank records and then reimported them, creating new records in the process.  The import completed in 37 seconds - 2minutes faster than the script.

               

              37 seconds is better than 2minutes and 34 seconds! So any suggestions on how to bring that 2 minutes 34 seconds down is appreciated.

               

              C

              • 4. Re: How to best create lots of new records quickly?
                calexmac

                Was already running a cache of 128MB.

                Bumped it up to the max of 256MB.  Then ran the record creation loop again (tho' this time I used the blank layout.)

                There was no change to the time taken when the cache was set to 128MB

                • 5. Re: How to best create lots of new records quickly?
                  Malcolm

                  We are working on a solution that does some simulation in the medical field.

                   

                  The simulation element occasionally requires the creation of a large number of records.  Sometimes half a million.

                   

                  Our challenge is to do this quickly.

                   

                  1. You could have a filemaker table which contains 500000 empty records and import them.

                   

                  or

                   

                  2. Have a "tabula rasa" with 500000 records which slurps in data using Bruce Robertson's Virtual List.

                   

                  malcolm

                  • 6. Re: How to best create lots of new records quickly?
                    comment

                    The fastest way to create new records is to import from a "bank" of blank records using repeating fields - with the import set to split repeating fields into individual records. Or at least it was the fastest several versions back - it might need re-checking. The credit goes to Ugo DiLuca.

                    • 7. Re: How to best create lots of new records quickly?
                      PeterWindle

                      I seem to recall Ray from Nightwing using a technique where you could create a relationship from a global in one table to another table (making sure that "Allow creation of related records") is checked. The global field then gets populated via set field in a loop. The records get created in the other table only once the record containing the global is committed.

                       

                      Not sure if this would work, not sure if it's any faster, but an interesting approach.

                       

                      Just another point, I know of someone else who had scripts creating multiple records and had te same slow down. Turned out that a "Clear Cache" within the loop made a massive difference.

                      • 8. Re: How to best create lots of new records quickly?
                        calexmac

                        Found this general approach to be the fastest.

                        Slightly fiddly to set up the 'bank' of blank records.

                        But worth the effort once its working.

                        Lots of possible ways of implementing the 'bank', so we will experiment with the best one for us.

                        • 9. Re: How to best create lots of new records quickly?
                          calexmac

                          BTW - tested this and Ray Cologon's approach takes much longer for bulk records.

                          • 10. Re: How to best create lots of new records quickly?
                            calexmac

                            TX to all for the advice.

                            Have tested all the suggested approaches and the winner by a long shot is to import from a 'bank' of null records.

                             

                            rgds

                            C

                            • 11. Re: How to best create lots of new records quickly?
                              comment

                              Keep in mind that a repeating field can have up to 32k repetitions. Using base-2, you could import any number of records up to 32,767 from a bank of 15 records only.

                              • 12. Re: How to best create lots of new records quickly?
                                calexmac

                                We like the 'bank' idea and it is certainly the fastest in our trials.

                                But we havent quite figured out how to capitalise on your idea of importing the repeating records.

                                We understand that the import can be configured to split repeating records up.

                                But what is the best way to create the 'bank'?  Is it a single record with a repeating field of 32k repetitions?

                                What did you have in mind?

                                • 13. Re: How to best create lots of new records quickly?
                                  comment

                                  If you have a repeating field of 32k non-empty repetitions, then each bank record, when imported, will generate 32k records in the target table. My idea is to use a repeating calculation field to control the number of non-empty repetitions in each record - so that you can quickly assemble a found set where the total number of non-empty repetitions equals the number of records you wish to create.

                                  • 14. Re: How to best create lots of new records quickly?

                                    Hi Michael,

                                     

                                    In testing 11 vs 12, here are a few comparisons in creating 160,000 new records using the two fastest techniques (AFAIK).  I ran each test 7 times and reopened the file between each test.  Test files available for anyone if requested.

                                     

                                    SnapShot.png

                                     

                                     

                                    Test #1:

                                     

                                    I used global number field (num) and global calc (number) with 32,000 repetitions and 5 records producing 160,000 new records.  Calculation used was:

                                     

                                    Case( Get ( CalculationRepetitionNumber ) ≤ Extend ( num ) ; Get ( CalculationRepetitionNumber ) )

                                     

                                    Importing into data table which contained uniqueID and num (number) field (not on layout) in form view, splitting reps.

                                     

                                    Test#2:

                                     

                                    I created 160,000 temp records in a new table with only a num field with auto-enter 1.

                                     

                                    Results are close but Test#1 wins in both version for adding and deleting.  :^)

                                     

                                    Ooops - it didn't keep my copy/paste.  Here is png of it

                                     

                                    Additionally not taken into account with Test#2, script would need to constrain to the number of records first and it also would have a greater footprint.

                                    1 2 Previous Next