Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

enter image description here

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

share|improve this question
    
What happens when you remove the BETWEEN and use CHE.ADDDATE >= @currentweekstart AND CHE.ADDDATE < @currentweekstart ? –  James Anderson May 23 at 10:35
    
What have you learned from your first 'improve performance' question on converting scans to seeks? It's all well and good asking, but we can't do your job for you if you're not learning. –  Mark Sinkinson May 23 at 10:48
    
I was reading yesterday about indexes and find this in slideshare: slideshare.net/myxplain/how-to-design-indexes-really-27839849 hope it helps –  Sal00m May 23 at 10:55
    
@MarkSinkinson am not asking you to do my job.i am trying to learn it.If you are interested to help me please help –  bala3569 May 23 at 11:00
2  
Then I don't believe that there's an issue with you having a scan on that table. You're not filtering, so there is no part of the index to 'seek'. –  Mark Sinkinson May 23 at 12:33

3 Answers 3

up vote 4 down vote accepted

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)
share|improve this answer
    
This will then remove that Clustered Index Scan as the table is no longer being hit. –  Mark Sinkinson May 23 at 12:39

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.

share|improve this answer

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 )
share|improve this answer
    
AddDate is in the ChildEvents table, not the ChildInformation one. –  ypercube May 23 at 13:11
    
For ChildEvents table it is enough if ChildID is the INCLUDE, doesn't need to be a key column (no filtering on it). –  Blaž Dakskobler May 23 at 13:18
    
Right, then you want to make sure that you have this index: CREATE INDEX CHILDEVENTS_IDX1 ON CHILDEVENTS ( TYPE, AddDate, CHILDID ) –  Gandolf989 May 23 at 13:48

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.