Geek Answers Handbook

SQL Server Indexing (Avoid Clustered Index Scans When Joining)

I have a couple of tables with specific master data relationships. In the parent block contains about 20 thousand lines, and in the daughter case - 120 to +. This is the main relationship in our application, and its performance is critical.

Its execution is bad: (

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 displays all the expected requests.

This query is slow (after about a minute to get 1k lines!)

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

I suspect that I am somewhere a stranger regarding the definition of good indexes. I defined the indices on the parent PC and the combined index on the parent PC and the date field, but this does not help this query.

Thanks in advance:)

EDIT (can't answer my own question since I'm a beginner!)

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

+7
source share
3 answers

Indexes are deleted and recreated, and now they are used.

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

+2
source

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, for each parent record, you need to scan the Child table to look for records with the corresponding ParentId.)

+5
source

You should have below indexes:

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

2- Child: Column ParentId Table

Suggestions: 1- Do not use *, you should select only the necessary columns.

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

One question: why LEFT OUTER JOIN, if all children must have a parent?

0
source

More articles:


All Articles