Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Old Forums
 CLOSED - General SQL Server
 slow query performance problem in SQL 2000

Author  Topic 

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-12-08 : 08:10:46
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 = 0

When 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

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-12-08 : 10:26:25
posting DDL of the tables involved + the code of PROC_FIND_SLOTS would be a major help.
Go to Top of Page

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2004-12-08 : 10:50:25
Its probably taking forever because it generates 1080864 rows.

Dustin Michaels
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-12-08 : 10:56:11
rule of thumb is: minimize the number of rows at the rightmost part of execution plan...

Go with the flow & have fun! Else fight the flow
Go to Top of Page
   

- Advertisement -