The query below, currently takes 7 seconds, but if I remove the Det2.RoomConflict and det2.PatientConflict from the output and where clause, it comes back in 0 seconds. I can leave them in the actual query and no performance issue, it's only when they are included in the output, even without the where clause. I use those fields to return only the valid record so I kinda of need them. Is there a better way?

Thanks
Kirk

DECLARE @iLength int

set @iLength=(Select defaultTime from dbo.coProc where procID=@tiProcID)

SELECT Top 1 det2.RoomID,det2.Roomname,det2.DeptID,cast(det2.StartTime as DateTime) as StartTime,
             cast(det2.EndTime as DateTime) as EndTime,det2.SlotOk,Det2.RoomConflict,Det2.PatientConflict
FROM
	(SELECT det.RoomID,det.RoomName,det.DeptID,det.StartTime,dateAdd(mi,det.DefaultTime,det.StartTime) as EndTime,0 as SlotOK,

	  (select count(meetingnumber) from dbo.meetings
		where (begintime between det.StartTime and dateadd(mi,@iLength,det.StartTime)
	        or     endtime   between det.StartTime and dateadd(mi,@iLength,det.StartTime)
	        	or det.StartTime between begintime and endtime
	        	or dateadd(mi,@ilength,det.StartTime) between begintime and endtime)
	        	and iscancelled=0 and roomid = det.RoomID ) as RoomConflict,
		(select count(meetingnumber) from dbo.meetings
			where (dateadd(mi,-1,begintime) between det.StartTime and dateadd(mi,@iLength,det.StartTime)
			or     endtime   between det.StartTime and dateadd(mi,@iLength,det.StartTime))
			and iscancelled=0 and patientID = @tiPatID) as PatientConflict
	FROM
		(SELECT distinct dbo.corooms.roomname, dbo.coproc.defaulttime, dbo.corooms.deptid, 
		                dbo.corooms.roomid, dbo.corooms.schedfrom, dbo.corooms.schedto,@DateTimeBegin+' '+StartTime as StartTime
		FROM   dbo.coproc INNER JOIN
		       	dbo.coProcRoomGrp ON dbo.coproc.procid = dbo.coProcRoomGrp.procID INNER JOIN
		 	dbo.coRoomToGroup ON dbo.coProcRoomGrp.coRoomGrpID = dbo.coRoomToGroup.coRoomGrpID INNER JOIN
		 	dbo.corooms ON dbo.coRoomToGroup.RoomID = dbo.corooms.roomid inner Join
			dbo.coProcStartTimes on coProc.ProcID=coProcStartTimes.ProcID
		WHERE  dbo.coproc.procid = @tiProcID and dbo.coRooms.Isactive=1 )det
	where det.StartTime > @ttEarlyStart)DET2
WHERE det2.RoomConflict=0 and PatientConflict=0
order by StartTime