1 2 Previous Next 23 Replies Latest reply on Jun 4, 2017 2:26 PM by beverly

    Fastest way to add new record

    nexgen

      I'm currently parsing a csv file using scripts but it's taking too long time to import. (the csv files contains about 100,000 records)

       

      How can I improve the speed of import?

       

      The way I'm currently doing it is:

      * store whole csv data in a variable

      * loop

      * getvalue to get the current line

      * break the current line by replacing "," with ¶

      * new record request

      * set field (9 fields)

      * end loop

        • 1. Re: Fastest way to add new record
          erolst

          We did this last year to create a tax calculator, using the latest official data from the Swiss tax authorities.

           

          We found that the fastest way was to ...

           

          1. Import the file and let FM take care of record creation

          2. Use Replace Field Contents over the import set to distribute the field data

           

          This is a job especially suited for PSoS, if you happen to have a server ...

          1 of 1 people found this helpful
          • 2. Re: Fastest way to add new record
            nexgen

            I have got a shared filemaker server and the file needs to import in the server.

             

            My first approach was to use the filemaker import. But it is taking a lot of time.

             

            Now, what I'm trying to do is, upload the filmaker to normal web server and get the content of the file and parse from there. That way everything can be done in filemaker server without waiting for it to get completed.

             

            But since I'm manually parsing it, it is taking a long time. Is there anyway to import the file in server from a url. If that's possible then it will be a lot faster.

            • 3. Re: Fastest way to add new record
              CarstenLevin

              Hi Nexgen,

               

              Before going ahead, just two questions:

              • CSV
              • 100.000 records

              How many MB/GB of pure text?

              How complex,

              1. just pure data into one table or relations having to be established?
              2. each part well separated or needing to be further treated (like first/last name in one field in your source but having to go into different fields in your target?
              3. etc

               

              Considering your own approach (you tried using a variable, I would consider storing the text in a field and parse from there, perhaps using variables on the way.

               

              And combining with the good advice given by Erolst: PSoS ... when you have tested with a minimal set in the field then you continue with Perform Script on Server. Which should be veeeerryyyy fast. I just created 28 million records last night as a part of a performance test (tried both with PSoE and Server Schedule - same result).

               

              You probably already know, but with PSoS or Server Scheduled Script (SSS) remember to establish context and that startscript could bring you to the wrong harbour.

              dump.png

              • 4. Re: Fastest way to add new record
                monkeybreadsoftware

                For TSV I have a FM.InsertRecordTSV function in my MBS FileMaker Plugin.

                Maybe I could make you a CSV variant.

                 

                But anyway, I would try to use SQL Insert commands as an alternative.

                • 5. Re: Fastest way to add new record
                  CarstenLevin

                  Hi Christian,

                   

                  Whatever you do ... dont you agree with Erolst (and me) that it should be done on the server, thus eliminating slow network and other issues?

                   

                  Best regards

                   

                  Carsten

                  • 6. Re: Fastest way to add new record
                    CarstenLevin

                    Hi again,

                     

                    Another reason not to do 100.000 creations over the internet is the risk!

                     

                    You may have internet hickups, disruptions that will perhaps leave your database in an unknown state.

                     

                    Therefore always consider error checking/transactinal setup of the import - then it can be resumed from where it stopped and you do not risk having to clean up a terrible mess.

                     

                    Best regards


                    Carsten

                    • 7. Re: Fastest way to add new record
                      erolst

                      We found that importing the records - even from a local file - was veeery slow when auto-creation of a serial number was on; when we disabled that, the import itself was almost instantaneously for some 10,000 records.

                       

                      Calculating and distributing the field values was done simply with a number of Replace Field Contents - that was very fast!

                      • 8. Re: Fastest way to add new record
                        bigtom

                        Fastest way is doing it on FMS. As mentioned this is a safer way to do it as well.

                         

                        How long is too long? How fast do you need it?

                         

                        The import process is usually extremely fast, but I guess you had different results.

                         

                        There is a CSV ODBC driver I have seen but not sure how that works as I have not used it.

                         

                        if you can fit it all in a variable, do It.

                         

                        Using set field 9 times is slow. Auto enter variable calcs are seemingly instant. Breaking this into multriple simultaneous server side jobs to take advantage of multi threading increases the speed.

                         

                        Finally, the disk write speed and CPU speed matters when you start pushing this as fast as possible.

                         

                        Where re did you pick this job up? I swear I saw it floating around the web or one very similar at least.

                        • 9. Re: Fastest way to add new record
                          nexgen

                          How many MB/GB of pure text?

                          How complex,

                          1. just pure data into one table or relations having to be established?
                          2. each part well separated or needing to be further treated (like first/last name in one field in your source but having to go into different fields in your target?

                          * 20 mb of pure text

                          1. pure data into one table

                          2. each parts are well separated

                           

                          I am using PSOS to process but it is still taking time.

                          • 10. Re: Fastest way to add new record
                            nexgen

                            Christian Schmitz wrote:

                             

                            For TSV I have a FM.InsertRecordTSV function in my MBS FileMaker Plugin.

                            Maybe I could make you a CSV variant.

                             

                            But anyway, I would try to use SQL Insert commands as an alternative.

                            Christian,

                             

                            I am able to successfully save the csv file from url to filemaker server's temporary path and documents path.

                             

                            However, when I use that path filemaker says the path not found. Seems like my filemaker solution don't have access to that path or I'm doing something wrong.

                             

                            (I have made sure to convert the native path to filemaker path)

                             

                            If filemaker can access that path then it will solve my problem by just using import records function.

                            • 11. Re: Fastest way to add new record
                              nexgen

                              Carsten Levin wrote:

                               

                              Hi Christian,

                               

                              Whatever you do ... dont you agree with Erolst (and me) that it should be done on the server, thus eliminating slow network and other issues?

                               

                              Best regards

                               

                              Carsten

                              I'm already doing it on the server. But it's taking too much time to parse csv.

                              • 12. Re: Fastest way to add new record
                                nexgen

                                bigtom wrote:

                                 

                                Fastest way is doing it on FMS. As mentioned this is a safer way to do it as well.

                                 

                                How long is too long? How fast do you need it?

                                 

                                The import process is usually extremely fast, but I guess you had different results.

                                 

                                There is a CSV ODBC driver I have seen but not sure how that works as I have not used it.

                                 

                                if you can fit it all in a variable, do It.

                                 

                                Using set field 9 times is slow. Auto enter variable calcs are seemingly instant. Breaking this into multriple simultaneous server side jobs to take advantage of multi threading increases the speed.

                                 

                                Finally, the disk write speed and CPU speed matters when you start pushing this as fast as possible.

                                 

                                Where re did you pick this job up? I swear I saw it floating around the web or one very similar at least.

                                using PSOS it's creating 5 records every 2 seconds. At this speed it will take 27 hours to import 100,000 records.

                                • 13. Re: Fastest way to add new record
                                  siplus

                                  using PSOS it's creating 5 records every 2 seconds. At this speed it will take 27 hours to import 100,000 records.

                                   

                                  You might need to rethink your data structure. How many fields do you have in the destination database ? How many calcs, how many autoenters ?

                                   

                                  Do you go on a layout with no fields before importing ? Do you use Freeze window and allow user abort (off) ? And how about indexed fields, do you have a ton of them ?

                                   

                                  5 records every 2 seconds is a crime, should not happen.

                                  • 14. Re: Fastest way to add new record
                                    beverly

                                    I may import into a temp table (using scheduled script on server or PSoS). Then (server-side again), loop the temp table to "import" with Set Field. Since there can be many "matching" records to UPDATE, this only brings over changes and is so much faster than import (from client). The temp table is just raw data, no auto-enter or calculations or summaries. I believe that is why it is so  much faster (as well as performed ON server).

                                     

                                    Beverly

                                    1 2 Previous Next