Ron writes "I have a SQL server 2000 database (8.00.818(SP3)) running on Windows 2000 server and a stored procedure that depending on the parameters entered can take a very long time to complete.The routine finds slots in a booking system and is called:exec PROC_FIND_SLOTS @StartDate = 'Dec 02 2004 04:00:00:000PM', @EndDate = 'Jan 10 2005 04:00:00:000PM', @DriverCategoryID = 4, @ExtraTimeTest = 0, @TestCentreID1 = 34, @TestCentreID2 = 0, @TestCentreID3 = 0, @MatchExact = 1, @ExtendedDrivingTest = 0When this is run and profiled to show the execution plan it generates 1080864 rows.The first execution tree is below Execution Tree--------------Sort(DISTINCT ORDER BY:([DiarySlot].[StartTime] ASC, [DiarySlot].[DiarySlotNo] ASC, [DiarySlot].[DiaryListNo] ASC, [Expr1013] ASC, [TestCentre].[CentreName] ASC, [Expr1015] ASC)) |--Compute Scalar(DEFINE:([Expr1013]=datename(weekday, Convert([DiarySlot].[StartTime])), [Expr1015]=[dbo].[FindSlotStart]([DiarySlot].[StartTime], [DiarySlot].[DiaryListNo]))) |--Filter(WHERE:([AppointmentSlot].[DiarySlotNo]=NULL)) |--Nested Loops(Left Outer Join, OUTER REFERENCES:([DiarySlot].[DiarySlotNo])) |--Nested Loops(Inner Join, OUTER REFERENCES:([TestCentreSkillType].[TestCentre_ID])) | |--Nested Loops(Inner Join, OUTER REFERENCES:([DiarySlot].[DiarySlotNo], [DiarySlot].[DiaryListNo], [DiaryList].[TestCentreSkillType_ID], [DriverTestCategory].[SkillTypeNo], [DriverTestCategory].[TestDuration])) | | |--Nested Loops(Inner Join, OUTER REFERENCES:([DiarySlot].[DiaryListNo])) | | | |--Nested Loops(Inner Join, WHERE:([dbo].[CalcSlotLength]([DiarySlot].[DiarySlotNo], [DiarySlot].[DiaryListNo], 0)>=[DriverTestCategory].[TestDuration])) | | | | |--Clustered Index Seek(OBJECT:([DVTABSP].[dbo].[DriverTestCategory].[PK_DriverTestCategory]), SEEK:([DriverTestCategory].[ID]=[@DriverCategoryID]) ORDERED FORWARD) | | | | |--Filter(WHERE:((([dbo].[IsStartofSlot]([DiarySlot].[DiarySlotNo], [DiarySlot].[DiaryListNo])=0 AND ([DiarySlot].[StatusNo]=[@StatusRange] OR [DiarySlot].[StatusNo]=0)) AND Convert([DiarySlot].[Available])=1) AND Convert([DiarySlot].[Locked])=0)) | | | | |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([DVTABSP].[dbo].[DiarySlot])) | | | | |--Filter(WHERE:(STARTUP EXPR(Convert([@ExtendedDrivingTest])=1 OR Convert([@ExtendedDrivingTest])=0))) | | | | |--Filter(WHERE:(((Convert([@ExtendedDrivingTest])=1 AND Convert([DiarySlot].[StartTime])<[@PremiumTime]) AND datename(weekday, Convert([DiarySlot].[StartTime]))<>'Saturday') OR Convert([@ExtendedDrivingTest])=0)) | | | | |--Index Seek(OBJECT:([DVTABSP].[dbo].[DiarySlot].[IX_DiarySlot_StartTime]), SEEK:([DiarySlot].[StartTime] >= [@StartDate] AND [DiarySlot].[StartTime] <= [@EndDate]) ORDERED FORWARD) | | | |--Clustered Index Seek(OBJECT:([DVTABSP].[dbo].[DiaryList].[PK_DiaryList]), SEEK:([DiaryList].[DiaryListNo]=[DiarySlot].[DiaryListNo]), WHERE:([DiaryList].[TestGradeMask]<>0) ORDERED FORWARD) | | |--Filter(WHERE:([DriverTestCategory].[SkillTypeNo]=[TestCentreSkillType].[SkillTypeNo] OR (Convert([DriverTestCategory].[SkillTypeNo])+100=Convert([TestCentreSkillType].[SkillTypeNo]) AND [DriverTestCategory].[TestDuration]=[dbo].[CalcSlotLength]([DiarySlot].[DiarySlotNo], [DiarySlot].[DiaryListNo], 0)))) | | |--Clustered Index Seek(OBJECT:([DVTABSP].[dbo].[TestCentreSkillType].[PK_TestCentreSkillType]), SEEK:([TestCentreSkillType].[ID]=[DiaryList].[TestCentreSkillType_ID]) ORDERED FORWARD) | |--Clustered Index Seek(OBJECT:([DVTABS