Suggestions needed for SQL Server 2000

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

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

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.

Is this the same Abi from that helped me with windows vista?? How weird would that be?

Sheldon