I'm hoping someone can give me some insight and help. The function below is called from within a stored procedure query. That query typically can return have between 30-100 rows. This function gets called on each row, and dogs the who process down. This function works, but I can't figure out why it takes forever. The meetings table contains a total of about 350K rows. Overall performance of the application is great, so I think it is pretty well indexed. It seems to me like this function is looking at all the rows in meetings records. With this function, the stored procedure takes about 13 seconds, excluding this function, it returns in 1 second (but has invalid rows).
The purpose is given the parameters, determine if this is already a record who's begintime and/or endtime exists within the range of the @StartTime and @EndTime for the RoomID given.
CREATE FUNCTION ctFun_RoomConflict (@RoomID int, @iLength int, @StartTime datetime )
RETURNS int as
BEGIN
Declare @returnValue int
declare @EndTime datetime
set @EndTime = dateadd(mi,@iLength,@StartTime)
select @returnValue=
(select count(meetingnumber) from dbo.meetings
where (@StartTime between begintime and endtime
or @EndTime between begintime and endtime)
and iscancelled=0 and roomid = @RoomID)
return (@returnValue)
END
Thanks for any idea's.
Kirk