How to improve this query performance by indexing properly?

Question

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

Answer 1

Since you have a foreign key from CHILDEVENTS to CHILDINFORMATION and you don't select any column from the child information table, you can skip that table. One less join will certainly not hurt:

SELECT CHE.CHILDID, COUNT(*) AS CURRENTWEEKPOTTYBREAKS        
FROM  BKA.CHILDEVENTS AS CHE           
WHERE CHE.TYPE ='POTTYBREAK'        
  AND CHE.ADDDATE BETWEEN @currentweekstart AND @currentweekend         
GROUP BY CHE.CHILDID ;

As for indexing, the existing index on (Adddate,Type) INCLUDE (ChildId, EventId) does not look very good for this query. I would try replacing it (in fact removing both those AddDate-Type indexes) with an index on:

(Type, AddDate) INCLUDE (ChildId)

Answer 2

Best you can achieve with this exact query and CHILDINFORMATION is a (nonclustered) index scan, if you add a nonclustered index on ChildId in the CHILDINFORMATION table. It's more efficient than a clustered index scan. See this and this . It's going to be a scan, since you're not filtering, as Mark Sinkinson mentioned.

But, as ypercube said in his answer, you can even skip the join.

Answer 3

I am going to assume that the event type is in the child events table, hence the join is needed. I noticed that a large part of the cost is doing a sort. You can avoid sorts by having the right columns in an index in the right order. Here are two indexes that you should probably have. Fair warning, I am an Oracle DBA, not a SQL Server DBA. So the syntax might not be correct. Also notice that all of the columns that you need are in the indexes, hence SQL Server won't have to read both the index and the table. this is how I would do it in an Oracle database, it should work for SQL Server as well. But test and you will know.

Counting potty breaks, sounds like a George Orwell book? ;-)

CREATE INDEX CHILDEVENTS_IDX1      ON CHILDEVENTS ( TYPE, CHILDID )
CREATE UNIQUE INDEX CHILDINFORMATION_IDX1 ON CHILDID     ( ADDDATE, CHILDID )