1 2 Previous Next 18 Replies Latest reply on May 28, 2016 10:25 AM by beverly

    Quickly deleting all records in a large table

    ChrisC_3

      Title

      Quickly deleting all records in a large table

      Post

      Hello,

      The problem may seem simple. I have an import script that has the job of first cleaning out a table and then importing a fresh set of records from another (ODBC SQL) database.

      The problem is that clearing out these tables with the Delete All Records script step takes an incredibly long time, longer than actually importing fresh records. Is there any way to prep my Filemaker Database (within the import script) to accelerate this process?

        • 1. Re: Quickly deleting all records in a large table
          philmodjunk

          No, but you might double check design details of this table. There are two reasons for extended delays when deleting large numbers of records from a table:

          a) A cascading delete may occur where related records in another table are also deleted
          b) A large (or several large) indexes may have to be updated as a result of the mass record deletion

          So it might be possible to remove some field indexing or to disable a cascading delete in order to get a faster record deleting. But of course, those very same features may be necessary to the correct function of your database.

          • 2. Re: Quickly deleting all records in a large table
            ChrisC_3

            Thanks Phil,

            Unfortunately this is a fresh database with no relations yet. I'm currently working on just the import mechanism. Removing indexing is out of the question as it would make the database borderline unusable when trying to work with data. The tables aren't all that large by database standards but we are talking several hundred thousand records in some cases. I'm starting to worry that Filemaker can't handle that kind of data well, and this may put a rather large project in jeopardy. Hopefully I'll find a workaround and solve this problem but any advice is appreciated.

            • 3. Re: Quickly deleting all records in a large table
              douglerner

              I'm having the same problem. I'm trying to delete 161,000 records so I can then upload a refreshed table of data. But the delete is taking incredibly long.

               

              After 45 minutes only 15,000 records have been deleted. At this rate it will take 8 hours to delete.

               

              doug

              • 4. Re: Quickly deleting all records in a large table
                ErikWegweiser

                I know this doesn't solve your current situation, but oddly enough, the ability to turbo-boost mass delete all records in a table is a new feature in FileMaker Pro 15.

                 

                Delays in deleting could be caused by several things, such as cascading deletes and removing index content. The latter may be addressable by reducing the number or content in indexed fields or turning off indexing for fields temporarily.

                 

                Are you able to perform this maintenance locally, or is the solution hosted during this process? Are there other users logged in at the same time?

                 

                Just some thoughts.

                1 of 1 people found this helpful
                • 5. Re: Quickly deleting all records in a large table
                  douglerner

                  ErikWegweiser wrote:

                   

                  I know this doesn't solve your current situation, but oddly enough, the ability to turbo-boost mass delete all records in a table is a new feature in FileMaker Pro 15.

                   

                  Delays in deleting could be caused by several things, such as cascading deletes and removing index content. The latter may be addressable by reducing the number or content in indexed fields or turning off indexing for fields temporarily.

                   

                  Are you able to perform this maintenance locally, or is the solution hosted during this process? Are there other users logged in at the same time?

                   

                  Just some thoughts.

                   

                  Maybe I should switch to Filemaker Pro 15 for that reason.

                   

                  I can do this maintenance locally. And I would have if I thought in advance it might help. Would it go faster locally?

                   

                  There are no other users logged in at the same time.

                   

                  Thanks for the info!

                   

                  doug

                  • 6. Re: Quickly deleting all records in a large table
                    beverly

                    Perform the script on the server (PSoS or scheduled script). I think the dialog is the problem, but what do I know?

                     

                    CAUTION: be very sure you have backup(s) and that the DELETE is using the correct layout, correct table, correct record(s) before deleting.

                     

                    beverly

                    • 7. Re: Quickly deleting all records in a large table
                      douglerner

                      I definitely took a complete backup and saved it online and offline.

                       

                      Do you think doing the delete completely offline, locally on my computer would make it run faster?

                       

                      Thanks,

                       

                      doug

                      • 8. Re: Quickly deleting all records in a large table
                        beverly

                        no. ON server has been the fasted as I have found. By PSoS or scheduled task (available by timer or immediately via the Console). This is true for a lot of heavy-data-mangling (er managing). Import, Export, Delete, parsing in loops, etc. I don't have metrics, but years of experience in data mangling.

                        FMServer is 'headless' (no display) and there is no screen to redraw (even with a Freeze Window or layout with no fields). It just seems to take forever to do locally (standalone or hosted) and minutes when on server and performed there.

                        beverly

                        • 9. Re: Quickly deleting all records in a large table
                          douglerner

                          Actually, I tested this yesterday.

                           

                          1. On server (FMHHost) as is - it looked like it would take about 8 hours to finish, so I quit.

                           

                          2. Offline with the indices turned off for the fields - about 30 seconds.

                           

                          3. Taking another copy and trying it without modifying the index settings - 5 to 10 minutes.

                           

                          So it seems to make a huge different between online and offline. I don't know why.

                           

                          I should mention this is a delete script somebody wrote for me. But I believe it is just a UI into the "delete all records" feature.

                           

                          doug

                          1 of 1 people found this helpful
                          • 10. Re: Quickly deleting all records in a large table
                            beverly

                            OK, I can't address your hosting. "on server" - does that mean you are triggering the event from your view into the database (as hosted) or does that mean you actually went to the FMServer admin Console (control panel, basically) and ran a scheduled script? Because you use an external host, you may not have access to the Console.

                            Your metrics are great!

                            beverly

                            • 11. Re: Quickly deleting all records in a large table
                              douglerner

                              I'm using a shared server instance of FM 14 at FMPHost. I'm triggering the event from my local FM 14 Pro Advanced client.

                               

                              doug

                              • 12. Re: Quickly deleting all records in a large table
                                beverly

                                Then that is why it was slower. You have to deal with data going back and forth between the client and server, the neighborhood kid watching YouTube and your ISP down-stepping your bandwidth, and screen redraws.

                                PSoS is not a step available until v 13.0, so for some the need to access the FMServer Console to run the scrip is the answer. But your hosting company may not allow that.

                                 

                                And then there is the new functionality in FM15: Truncate Table

                                I haven't tested to compare PSoS (Delete All) and Truncate Table

                                 

                                beverly

                                • 13. Re: Quickly deleting all records in a large table
                                  douglerner

                                  I know I don't have a speed problem. I have 2 Gbps up and down over fiber optic here. But what I don't know is what data needs to go back and forth between the client and server for this. I'm guessing a lot if it was that slow.

                                   

                                  doug

                                  • 14. Re: Quickly deleting all records in a large table
                                    RalphLearmont

                                    ChrisC_3 wrote:

                                     

                                    Thanks Phil,

                                    Unfortunately this is a fresh database with no relations yet. I'm currently working on just the import mechanism. Removing indexing is out of the question as it would make the database borderline unusable when trying to work with data. The tables aren't all that large by database standards but we are talking several hundred thousand records in some cases. I'm starting to worry that Filemaker can't handle that kind of data well, and this may put a rather large project in jeopardy. Hopefully I'll find a workaround and solve this problem but any advice is appreciated.

                                    Another approach which might work depending on the number of records concerned, depends on whether the number of records to delete is reasonably close to the number to import - assuming you can do a record count or use get(TotalRecordCount) function on the source and target table.  You wrote that several hundred thousand records can be involved...  Instead of "adding" records when importing, you can "update existing records".  This saves the time of deleting that number of records.  If there are MORE records to import, you don't have to delete any beforehand - just set the option to add data as new records.  If there are less records to import, omit the difference and delete just those.  All of this might not be worth the trouble if records involved are greatly different in quantity.

                                     

                                    1 2 Previous Next