Faster Records deletion, possibly by deferring their index deletion and related deletes optimization

Idea created by Vincent_L on Jun 18, 2018
    Active
    Score6
    • ThierryGuemboura
    • bigtom
    • Vincent_L
    • TobiasLiebhartKoschierSE
    • Markus Schneider
    • DavidThorp

    Hi,

    Records deletion is way to slow in filemaker. It's specially the case with indexed fields.

    It has been proven that deletion is much slower when there's index.

    Fields indexes are pretty much necessary in filemaker, so it turns out that most field are indexed, so all records when being deleted suffer that index clearing.

     

    But contrary to indexing on import, where the generated index has a value (user able to search thanks to this), the index of deleted records have no value.

    The user doesn't care about that index deletion, it's only there for file maintenance.

    So why impacting the user with something that has no value for him, immediately and synchronously, forcing him (or scripts) to wait for that cleaning ?

     

    So I suggest that rather than deleting the indexes at the same time of the record deletion, Filemaker would just delete the records, give back the user/script the hand back, and then let another process delete the orphaned indexes later / in the background / when iddle.

    Moreover that orphaned index deletion would be much faster since rather than doing it o,ne by one, it would be done in bulk, in one go : just delete all orphaned indexes

     

     

    Added june 26 :

     

    Another ers slowness cause are related deletes. Nowadays it seems that deletion process records one by one, each time looking for the potential related deletes. So if you delete 400k records, it does 400k related delete checks if there's one related delete.  It could be ways faster, if, as FileMaker knows perfectly the structure of the relationships, FileMaker would search all th related records to delete in one search query, mark them for deletion in one go, then delete the master records and then corresponding marked related records in one go. This marking could be the recordid of the master record. So this would work even if the deletion is interrupted, because filemaker would only delete the related records whose maste recordIDs are missing. This would be a 3 steps bulk process rather than 400k individual ones. Tremendously faster.

     

    P.S : I know about truncate table, but here I'm talking about a subset of records not all the records.