CODEWITHSUNDEEP
sql-server
Nathan
Nathan

Reputation: 216

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

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 ...

Upvotes: 8

Views: 11567

Answers (4)

Todd McDaniel
Todd McDaniel

Reputation: 11

Could this be an issue with outdated statistics? What kind of database maintenance do you perform on this database and how often? If you use Ola Hallengren's db maint scripts you can update statistics during your index optimizations process.

Upvotes: -1

Nathan
Nathan

Reputation: 216

Deleted and recreated the indexes and now they are being used.

Is it possible that they were corrupt? They weren't fragmented (was the first thing I checked...). In any case the problem was resolved ...

Upvotes: 3

Yaqub Ahmad
Yaqub Ahmad

Reputation: 27659

You should have below indexes:

1- Table Parent: Column1 PK-Of-PrimaryTable + Column2 TransmissionDate

2- Table Child: Column ParentId

Suggestions: 1- Do not use *, you should SELECT only the required columns.

2- Both the indexes should INCLUDE the columns which you need in your SELECT.

One question: why LEFT OUTER JOIN if all the childs must have a Parent?

Upvotes: 0

Chris Fulstow
Chris Fulstow

Reputation: 41872

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.)

Upvotes: 5

Related Questions