Slow DELETE statement in MS SQL

Yesterday I blogged about the slow down issue we encountered in our database server the other night. Our initial solution was to re-index all affected tables but unfortunately it did not solve the problem. The second activity we conducted was to purge records from December 2005 and earlier but still the problem persist. After further analysis of the problem we found out that the slow down was due to the trigger that was firing every record insertion. The following code is the content of the trigger.

CREATE TRIGGER [Audit_Insert_Detail] ON [dbo].[gltrxdet] 
FOR INSERT
AS
declare @seq_id as numeric
declare @JulDate as numeric
declare @journal_num as varchar(20)
declare @Control_id as varchar(20)


set @JulDate = (select datediff(dd,'1/1/1753',getdate())+639906)
set @seq_id  = isnull((select max(Sequence_Control) from PERA_TECH..Main_Audit_Log where Activity_date = @Juldate),0)+1
set @Control_id = cast(@Juldate as varchar(10)) + '-' + cast(@seq_id as varchar(10))


begin
 Insert into PERA_TECH..main_audit_log
 select HOST_NAME(),system_user,'INSERT NEW RECORD', @JulDate,app_name(), @seq_id,
 @Control_id ,'GL',1
end

begin
 delete from PERA_TECH..gl_header_Audit where id_control = @Control_id
 insert into PERA_TECH..gl_header_Audit
 select * , @Control_id from gltrx where journal_ctrl_num in (select journal_ctrl_num from inserted)
 
 delete from PERA_TECH..gl_detail_Audit where id_control = @Control_id
 insert into PERA_TECH..gl_detail_Audit
 select * , @Control_id from inserted
end

After reviewing the code, we initial concluded that the SELECT on this statement is the root cause so we purged some data on "gltrx" table.

begin
 delete from PERA_TECH..gl_header_Audit where id_control = @Control_id
 insert into PERA_TECH..gl_header_Audit
 select * , @Control_id from gltrx where journal_ctrl_num in (select journal_ctrl_num from inserted)
 
 delete from PERA_TECH..gl_detail_Audit where id_control = @Control_id
 insert into PERA_TECH..gl_detail_Audit
 select * , @Control_id from inserted
end

Again the problem still persist. Because we are quiet sure that the script above is the root cause, we throughly inspected each table used in the script, specifically "PERA_TECH..gl_header_Audit", "PERA_TECH..gl_detail_Audit" and "gltrx". And it struck us to found out that "PERA_TECH..gl_header_Audit" and "PERA_TECH..gl_detail_Audit" are not indexed and contains millions of audit records. So we concluded that the root problem is the following statements (in bold).

begin
 delete from PERA_TECH..gl_header_Audit where id_control = @Control_id
 insert into PERA_TECH..gl_header_Audit
 select * , @Control_id from gltrx where journal_ctrl_num in (select journal_ctrl_num from inserted)
 
 delete from PERA_TECH..gl_detail_Audit where id_control = @Control_id
 insert into PERA_TECH..gl_detail_Audit
 select * , @Control_id from inserted
end

Our final solution is to purge from these tables and the problem was solved.

For More SQL Tips & Tricks, subscribe now.