SQL Server Indexing (Avoid a Clustered Index Scan on a join)

Question

I have a pair of tables with a master-details relationship defined. There is about 20k rows in the parent, and 120k+ in the child. This is the core relationship in our application and its performance is mission critical.

Its performing poorly :(

Running the following query is fast, and uses my indexes:

SELECT *
FROM Parent 
WHERE TransmissionDate BETWEEN '1-dec-2011' and '2-dec-2011'

And the execution plan shows all the expected lookups.

This query is slow though (about a minute to retrieve 1k rows!)

SELECT *
FROM Parent
LEFT OUTER JOIN child ON Child.ParentID = Parent.ID
WHERE TransmissionDate BETWEEN '1-dec-2011' AND '2-dec-2011'

I suspect I'm ignorant somewhere here with regards to the definition of good indexes. I have defined indexes on the Parent PK and a combined index on the Parent PK and date field, but it doesn't help this query.

Thanks in advance :)

EDIT (can't answer own question as I'm new!)

I deleted the indexes and recreated them and now everything is happy? Is it possible they were corrupt? I had already rebuilt them ...

Answer 1

Try adding an index to Child.ParentID

CREATE NONCLUSTERED INDEX IX_Child_ParentID ON Child (ParentID);

(This will make the LEFT JOIN between Parent and Child much more efficient. Without it, each Parent record requires a table scan of Child to find records with a matching ParentId.)