Performance Issue

The following query is sent by six clients about every 30 seconds on a 30 MB database. The 1.8Ghz server w/1GB of ram is getting killed. Anyone have any suggestions? Thanks!

(@TeeTimeDate datetime,@CourseId int)SELECT ReservedCellCount *100.0/ FullCellCount, TeeTimeDate
FROM tblCourseTimes_v8
WHERE TeeTimeDate BETWEEN @TeeTimeDate AND DATEADD(dd, 6,@TeeTimeDate)AND CourseId=@CourseId
AND FullCellCount != 0 AND ReservedCellCount!=0 AND ParentCourseTimeId is null

what are the indexes on tblCourseTimes_v8?

should probably have a clustered index on: CourseId, TeeTimeDate

Make sure you have the right indexes and check the I/O stats and execution plan for optimization.

I will be interested to add a coputed column for "ReservedCellCount *100.0/ FullCellCount" instead of computing for every execution to improve this query.

Check BOL for (persistent) computed coulumns..


Mohammed U.

Try this, add also make sure that you have the required indexes:


DECLARE @TeeTimeDate datetime
DECLARE @TeeTimeDate_to datetime --- New variable
DECLARE @CourseId int

SET @TeeTimeDate = GetDate() -- You can ignore this

SET @TeeTimeDate_to = DATEADD(dd, 6,@TeeTimeDate) -- New

SELECT ReservedCellCount * 100.0/ FullCellCount,
TeeTimeDate
FROM tblCourseTimes_v8
WHERE TeeTimeDate BETWEEN @TeeTimeDate AND @TeeTimeDate_To --Modified
AND CourseId=@CourseId
AND FullCellCount > 0 --Modified
AND ReservedCellCount > 0 --Modified
AND ParentCourseTimeId is null