2 Replies Latest reply on Aug 12, 2017 3:01 PM by DavidKamar

    FM 16 Adv - Unexpected Delete Failure

    synergy46

      I have been developing a membership application for along while.  (it has been a constant learning curve). 

       

      Today I discovered one of my 'Join' tables has over 600,000  records.  (It came to my attention when a sort was performed and the dialog popped up saying it was sorting that many). So, I open the layout and sure enough, somewhere along the line my programming 'goofs' had duplicated the existing 63 record sample application to over 600,000. No problem.  I'll just write a script that will go through the join table and delete those records that have duplicate Key values.

       

      Wrong.  As I step through my code it seems to work, then it gets into a section about 15 records down and the DELETE RECORD line of the code stops working.  The script is going to the DELETE RECORD line; it just fails to delete it. Ideas?

       

      XcriptDelete.png

       

      XTableJoin.png

        • 1. Re: FM 16 Adv - Unexpected Delete Failure
          synergy46

          It turns out that I was failing to recognize that the Go To Record Next command was not  necessary when a deleted record was successful.  Everything moved up on it's own.

           

          So I produced this simple little script that works:

          XScript2.png

          But, it is so sloooooooooooooow.  Watching paint dry is faster.

          What can I do to speed up the loop?

           

          Thanks

          • 2. Re: FM 16 Adv - Unexpected Delete Failure
            DavidKamar

            When doing a large amount of looping deletes like this it's better to 'flag' the records you want to delete, rather than delete in the loop.

            Create a new field "MarkedForDeletion", which you set  to True when you detect a duplicate.

            Then after your loop, just do a Find for all the "MarkForDeletion = True" records, and Delete All.

             

            When you delete records one by one, as you are doing, the record indexing needs to be updated with each delete. Deleting all records with a single Delete will only redo the indexing once.

             

            As another minor speed improvement, replace AND logic with Case where possible:

            If $KV = #NextKV and $KM=NextKM and not isEmpty($KV) and not isEmpty($KM)

            This can be made more efficient via a Case statement:

            Case(

                 isEmpty( $KM ) ; 0 ;

                 isEmpty( $KV ) ; 0 ;

                 $KV = #NextKV and $KM=NextKM ; 1 ;

                 0

            )

             

            This means if $KM or $KV is empty, we don't need to do the slower text comparison step.

            Concatenated "AND" statements do NOT exit out when a False is reached. Everything is calculated and the overall statement is then evaluated as True/False.