I have following select query, and I need to improve the performance of this query. Can anyone suggest me how to convert the clustered index scan to index seek?
DECLARE @now DateTime;
DECLARE @currentweekstart DateTime;
DECLARE @currentweekend DateTime;
SET @now = getDate();
SET @currentweekstart = DATEADD(
DD,
-(DATEPART(DW, @now - 7) - 1),
@now - 7);
SET @currentweekend = DATEADD(
DD,
7 - (DATEPART(DW, @now - 7)),
@now - 7);
SELECT CHINFO.CHILDID,COUNT(*) AS CURRENTWEEKPOTTYBREAKS
FROM BKA.CHILDINFORMATION CHINFO
JOIN BKA.CHILDEVENTS CHE
ON CHE.CHILDID = CHINFO.CHILDID
WHERE TYPE ='POTTYBREAK'
AND CHE.ADDDATE BETWEEN @currentweekstart AND @currentweekend
GROUP BY CHINFO.CHILDID
Primary Keys:
TableName --> ColumnName
CHILDINFORMATION-->ChildId
CHILDEVENTS-->EventId
Foreign Keys:
CHILDEVENTS-->Here ChildId is the foreign key reference of CHILDINFORMATION table
NonClustered Indexes:
CHILDINFORMATION-->No nonclustered indexes
ChildEvents-->IX_ChildEvents_Type-->Type include EventId
ChildEvents-->IX_ChildEvents_Type_AddDate-->Type,AddDate
ChildEvents-->IX_ChildEvents_Adddate_Type-->Adddate,Type include ChildId,EventId
CHE.ADDDATE >= @currentweekstart AND CHE.ADDDATE < @currentweekstart
? – James Anderson May 23 '14 at 10:35