Hi, I have a Stored Procedure(SP) in SQL Server 2000. The function of this SP is to delete outdated records from several tables based on some conditions. The SP has a series of "Delete" statements only. The execution of this SP happens only in Midnight, so as not to affect any database actvities during peak hours. The problem is that, this SP takes more than 20 minutes to accomplish its deletion. Can you suggest any ways of improving this procedure, so that the Lead time taken for deletion is reduced? Expecting your replies soon. Thanks Abilash
Does the underlying tables has clustered indexes? Post the code used to delete the rows and ensure to wrap them in transactions in order to takeup the sizes during the process. Satya SKJ Moderator http://www.SQL-Server-Performance.Com/forum This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
or is there any declarative Referential Integrity which hasn't been indexed appropriately? e.g. delete from a parent table checks that there are no children in a child table but the FK in the child table isn't indexed? Cheers Twan
The underlying tables doesn't have any Clustered Indexes. I shall make sure Indexes are created for the tables. "Post the code used to delete the rows and ensure to wrap them in transactions in order to takeup the sizes during the process" I dont understand the above statement. Could you be a little more precise? Thanks Abilash
Apologies if that has confused, post the code here that is used in the stored procedure and take help from this KBAhttp://support.microsoft.com/default.aspx?scid=kb;en-us;243589&sd=tech to troubleshoot slow running queries. Satya SKJ Moderator http://www.SQL-Server-Performance.Com/forum This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Here im posting the code for the SP below. /****** Object: Stored Procedure dbo.del_outdated_reports Script Date: 21/09/2005 11:16:25 ******/ CREATE proc dbo.del_outdated_reports -- This SP called as Midnight Delete Process as begin declare @ReportDateForDBUsers int, @ReportDateForNonDBUsers int, @DataElementDate int, @ReportDataForInvestReport int select @ReportDateForDBUsers = ConfigValue from Configuration where ConfigCode = 'ReportDurationForDBUsers' select @ReportDateForNonDBUsers = ConfigValue from Configuration where ConfigCode = 'ReportDurationForNonDBUsers' select @ReportDataForInvestReport = ConfigValue from Configuration where ConfigCode = 'ReportDurationForInv' select @DataElementDate = ConfigValue from Configuration where ConfigCode = 'DateElementDuration' begin tran -- Delete the Reports which are less than 7 days for DB and 1 for NonDB Users delete Report from Report r, ReportHeader rh, profile f where r.requestID = rh.requestID and f.dbid = rh.dbid and f.refid = rh.refid and (DATEDIFF(dd,rh.saveddate,getdate()) >= ((case when rh.reportkind = 'IN' then @ReportDataForInvestReport else(case when IshistAccessible = 1 then @ReportDateForDBUsers else @ReportDateForNonDBUsers end) end))) if @@error <> 0 begin rollback tran return end -- Delete the data elements for DB Disabled User delete DataElements from ReportHeader r, DataElements de,profile p where r.requestID = de.requestID and p.dbid = r.dbid and p.refid = r.refid and p.IshistAccessible = 0 and DATEDIFF(dd,r.saveddate,getdate()) >= (case when r.reportkind = 'IN' then @ReportDataForInvestReport else @ReportDateForNonDBUsers end) -- Vj - Should delete from DMI_dataelements only for non DB enabled users. For database enabled -- users data from DMI_dataelements needs to be deleted after 13 months delete DMI_dataelements from ReportHeader rh, profile f, DMI_dataelements dm where DATEDIFF(dd,rh.saveddate,getdate()) >= @ReportDateForNonDBUsers and f.dbid = rh.dbid and f.refid = rh.refid and dm.requestid = rh.requestid and f.IshistAccessible = 0 if @@error <> 0 begin rollback tran return end -- Delete the ReportHeader which are 1/7 days older and there is no entry in the data elements table. delete ReportHeader from ReportHeader rh, profile f where f.dbid = rh.dbid and f.refid = rh.refid and DATEDIFF(dd,rh.saveddate,getdate()) >= (((case when rh.reportkind = 'IN' then @ReportDataForInvestReport else(case when IshistAccessible = 1 then @ReportDateForDBUsers else @ReportDateForNonDBUsers end) end))) and ( not exists ( select 1 from DataElements de where de.requestid = rh.requestid ) and not exists ( select 1 from DMI_DataElements de where de.requestid = rh.requestid) ) if @@error <> 0 begin rollback tran return end -- Update the ReportHeader to indicate that the reports have been deleted for rows older than 7 days. update ReportHeader set reportexists = 0 from ReportHeader rh, profile p where rh.dbid = p.dbid and rh.refid = p.refid and p.IshistAccessible = 1 and DATEDIFF(dd,rh.saveddate,getdate()) >=(case when rh.reportkind = 'IN' then @ReportDataForInvestReport else @ReportDateForDBUsers end) and reportexists = 1 if @@error <> 0 begin rollback tran return end commit tran begin tran -- Delete the data elements for DB enabled User/all dataelements which are > than DataElementDate delete DataElements from ReportHeader r, DataElements de where r.requestID = de.requestID and DATEDIFF(mm,r.saveddate,getdate()) >= @DataElementDate if @@error <> 0 begin rollback tran return end -- Delete the DMI DataElements which are older than 13 months delete DMI_DataElements from ReportHeader r, DMI_DataElements de where r.requestID = de.requestID and DATEDIFF(mm,r.saveddate,getdate()) >= @DataElementDate if @@error <> 0 begin rollback tran return end -- Delete the ReportHeader which are older than 13 months. delete ReportHeader where DATEDIFF(mm,saveddate,getdate()) >= @DataElementDate if @@error <> 0 begin rollback tran return end commit tran begin tran --vj need to delete records from DMI_dataelements for those orders where dataexists =0 --in dmi_dataelements table and reportexists is 0 delete DMI_DataElements from ReportHeader r, DMI_DataElements de where r.requestID = de.requestID and r.reportexists = 0 and de.dataexists = 0 if @@error <> 0 begin rollback tran return end --vj Delete all entires in reportheader for wich there are no dataelements and also -- reportexists flag is 0 delete ReportHeader from ReportHeader rh where not exists ( select 1 from DataElements de where de.requestid = rh.requestid ) and not exists ( select 1 from DMI_DataElements de where de.requestid = rh.requestid) and rh.reportexists = 0 if @@error <> 0 begin rollback tran return end commit tran begin tran -- vj delete entries in dm_usr_entry_flds_val when the report has been deleted from the report header table delete dm_usr_entry_flds_val from dm_usr_entry_flds_val du where not exists (select 1 from reportheader rh where rh.requestid=du.requestid) if @@error <> 0 begin rollback tran return end commit tran end GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO Thanks Abilash Abilash. P
And one more doubt i have with respect to the above SP. Since we have many inserts to the tables on a single day the database internally will re-arrange the index(If Clustered Index is created on the tables) each time any inserts happens on the table. Whether this will have performance impact? Kinldy let me know how should i overcome this. Thanks Abilash Abilash. P
you can use the UPDATE STATISTICS to update the indexes which is fragmented. Pls check this link for more information about this: http://sql-server-performance.com/statistics.asp -Johnson
quote: delete Report from Report r, ReportHeader rh, profile f where r.requestID = rh.requestID and f.dbid = rh.dbid and f.refid = rh.refid and (DATEDIFF(dd,rh.saveddate,getdate()) >= ((case when rh.reportkind = 'IN' then @ReportDataForInvestReport else(case when IshistAccessible = 1 then @ReportDateForDBUsers else @ReportDateForNonDBUsers end) end)))DateDiff usage prevents effective use of index on savedDate if exists. Rewrite the query to use DateAdd function instead. The same applies for other deletes using dateDiff function. quote:you can use the UPDATE STATISTICS to update the indexes which is fragmented.No, he can't. quote:And one more doubt i have with respect to the above SP. Since we have many inserts to the tables on a single day the database internally will re-arrange the index(If Clustered Index is created on the tables) each time any inserts happens on the table. Whether this will have performance impact? Kinldy let me know how should i overcome this.Yes index will be updated and if wrong index is choosed to be clustered that can lead to frequent page splits and fragmentation. The best choice of clustered index in your scenario is identity column.