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.
check database trigger for delete of that table
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...
check database trigger for delete of that table
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...
O - snap! :)
Ignore the secondary buffer user_ctl-2.user_obj, that was a copy and paste error.
Yep, checked for delete trigger none.......found replication trigger we were not expecting.....there in is the problem. Thx.