AnsweredAssumed Answered

bulk delete records from one table based on fields from another table

Question asked by sivagurS on Jun 15, 2017
Latest reply on Jun 15, 2017 by philmodjunk

Hi all,

 

I am working on a project where the user only works on offline file at all times.

this means I have 2 way sync to do.

1. Import updated transaction data from the Server periodically to the offline tables.

2. Update the Server table based on changes made in the offline file.

 

1.  The first Part : Server to Offline tables

  I have a separate Sync table, that holds the last sync time-stamp.

When the user opens the app, I check for difference between current time-stamp and sync time-stamp and if its more than an hour and if the user has connection to the server,

  • I run a script to grab just the modified data since the last sync. In this transaction data-set, I have flag DELETE, to get all the records that have been deleted since the last sync.
  • Then I Loop through this transaction data-set, go to my offline data table and delete all matching records.
  • I also delete those records with DELETE flag On in the Transaction table.
  • Then I do a bulk import of all records from Transaction data-set to the local offline table
  • Update Sync table with this recent time-stamp

Logically it works alright, but my issue is the time it takes to do all this.

My offline table has about 200,000 records and my transaction data-set has about 2000-6000 records.

So to loop through 200,000 records and then bulk Import about 6000 takes forever.

 

I was wondering if there is anyway

I can do a bulk delete of those records in offline table, that are in the Transaction data-set.

Delete those records with DELETE flag On in the Transaction table.

Import  all records from Transaction data-set to the local offline table. (Importing 6000 records in iPhone doesn't really take much longer, it's the looping through records that makes it slow.)

 

If not what would the best possible approach to get this update done.

My app runs only on FileMaker Go and hence all of the load happens in the iOS device and not the FM server.

Outcomes