Long time to delete a record?

Posted by Blake Stanford on 06-Feb-2018 06:15

I have some code that deletes some records in a transaction, for some reason one delete statement is taking 60 seconds to complete.  I believe this is something recent and I'm at a loss for what is going on.  Any tips on where to start looking?  I'm checking the lock table, all records are being exclusive-locked by the user doing the delete, there is no transaction active before the for first user_ctl,  there are no lock waits, the delete of user_ctl takes 60 seconds to complete.  I can delete it through a procedure editor with no delay.  Below is the code:

MESSAGE "BSTAN DEBUG - ip-user_id: " ip-user_id SKIP
"Transaction active:" TRANSACTION VIEW-AS ALERT-BOX.  

FOR FIRST user_ctl WHERE
                     user_ctl.user_or_group_id = ip-user_id
                     EXCLUSIVE-LOCK:

  FOR EACH ASC_product_user EXCLUSIVE-LOCK
        WHERE ASC_product_user.USER_obj = user_ctl-2.USER_obj:

     DELETE ASC_product_user.
  END.

  FOR FIRST gsm_user EXCLUSIVE-LOCK
       WHERE gsm_user.USER_obj = user_ctl.USER_obj:
     DELETE gsm_user.
  END.

  MESSAGE "BSTAN DEBUG 5" VIEW-AS ALERT-BOX.
  etime(true).

  DELETE user_ctl.

  MESSAGE "BSTAN DEBUG 6 - Time for delete:" ETIME VIEW-AS ALERT-BOX.
END.

Posted by marian.edu on 06-Feb-2018 06:20

check database trigger for delete of that table


Marian Edu

Acorn IT 
+40 740 036 212

Posted by PatrickOReilly on 06-Feb-2018 06:24

Blake,

that looks like Dynamics code and table names. Yes?

Dynamics implemented a number of cascading deletes, or else delete validations to prevent orphan data, in DB triggers.  Sometimes the foreign keys on the related tables are not indexed (!!!), and that results in these very slow deletes.  Go have a look at the delete trigger's code for the user_ctl table.

Hope that heps...

All Replies

Posted by marian.edu on 06-Feb-2018 06:20

check database trigger for delete of that table


Marian Edu

Acorn IT 
+40 740 036 212

Posted by PatrickOReilly on 06-Feb-2018 06:24

Blake,

that looks like Dynamics code and table names. Yes?

Dynamics implemented a number of cascading deletes, or else delete validations to prevent orphan data, in DB triggers.  Sometimes the foreign keys on the related tables are not indexed (!!!), and that results in these very slow deletes.  Go have a look at the delete trigger's code for the user_ctl table.

Hope that heps...

Posted by PatrickOReilly on 06-Feb-2018 06:28

O - snap! :)

Posted by Blake Stanford on 06-Feb-2018 06:28

Ignore the secondary buffer user_ctl-2.user_obj, that was a copy and paste error.

Posted by Blake Stanford on 06-Feb-2018 07:15

Yep, checked for delete trigger none.......found replication trigger we were not expecting.....there in is the problem. Thx.

This thread is closed