The code in the stored procedure below normally return a result set to the user in about 1 second. But it seems like after some time (1 - 2 months) the time goes up to 10-12 seconds. I open the stored procedure and add a blank line, or remove one, save it, and it runs fast again. I'm sure this recompiles it and ?stores it in the cache? again? I guess I'm wondering if there is a way within my code that I can ensure it gets recompiled every so often, like when I detect that it took over a certain number of seconds. This SP gets called maybe 100-200 times a day. As a side, if somebody knows a better way to code this SP I'd be willing to listen to suggestions.

Thanks in advance

Kirk

CREATE PROCEDURE ct_lu_SurgeonList_WithConflictSearch
@tiDept int,
@ttStart datetime

as
set nocount on

declare @ttQueryEnd datetime
set @ttQueryEnd=convert(varchar(10),@ttStart,101)+' 23:59:59'


SELECT  dbo.coper.lastname, 
	dbo.coper.firstname, 
	dbo.coper.coperid, 
  (SELECT Top 1 dbo.corooms.roomname 
           FROM  dbo.meetings with  INNER JOIN 
                 dbo.meetproc with   ON dbo.meetings.meetingnumber = dbo.meetproc.meetingnumber INNER JOIN 
                 dbo.corooms with  ON dbo.meetings.roomid = dbo.corooms.roomid  
	   where @ttstart between meetings.BeginTime and meetings.EndTime
  	   AND meetproc.coperid=coPer.coPerID and meetings.isblock=0 and meetings.iscancelled=0) as RoomName,
	isnull(dbo.coper.isResident,0) as isResident, 
	isnull(dbo.coper.isSuspended,0) as isSuspended,
	0 as Numcancelled,
	0 as NumSched,
	isnull((Select specdesc from dbo.coSpec with (nolock) where coSpec.specID=coPer.SpecID and coSpec.Isactive=1),' ') as specdesc
FROM    dbo.coper INNER JOIN
        dbo.coperdept ON dbo.coper.coperid = dbo.coperdept.coperid
where coPerDept.deptID=@tiDept
and coPer.isactive=1
and coPer.perTypeID=1
order by coper.lastname,coPer.firstname
GO