Help optimise stored procedure

[TLDuk]

I have a stored procedure on various Sql servers for deleting old data i`m no sql expert but I need some help optimized the query..


/****** Object: Stored Procedure dbo.sp_DeleteArchives Script Date: 30/07/04 10:20:02 ******/

CREATE PROCEDURE sp_DeleteArchivesDays
@days int
AS
declare @startdate datetime
declare @deletedate datetime
select @startdate = min(dateid) from tblactivity
select @deletedate = dateadd(day,@days,@startdate)
delete from tblactivity where dateid < @deletedate
select @startdate = min(dateid) from tblIndex
select @deletedate = dateadd(day,@days,@startdate)
delete from tblcallflow from _tblindex inner join tblcallflow on _tblindex.recordnum = tblcallflow.recordnum
where _tblindex.dateid < @deletedate
delete from _tblindex where dateid < @deletedate

The two following lines seem to take about 5 mins each to execute

select @startdate = min(dateid) from tblactivity

select @startdate = min(dateid) from tblIndex

Is there a better way to find date and time of the oldest record in the required table ?

 

[mrdenny]

Nope.

If the queries are taking to long to run, try indexing the tables. An index on the DateId column of each table should make the queries run much faster.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog