8

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

Improve this question
3
  • Did you mis-type the first statement? The between appears to be missing the first 2011' Commented Dec 2, 2011 at 3:28
  • Hi Adam - yes I did. Have cleaned up the SQL, shoudl be clearer now :) Commented Dec 2, 2011 at 3:30
  • Also removed the 'with' clause from the first, was leftover from some attempts to work out what was going on ... Commented Dec 2, 2011 at 3:32

4 Answers 4

Reset to default
5

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

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

3

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

Improve this answer

1 Comment

Then it sounds like your statistics were out of date
0

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?

Improve this answer

1 Comment

The indexes you mention are defined, profiler shows a clustered index scan on the id of the parent table. We don't use select *, this is just a copy-paste of some sql being using for testing. Similarly the left outer is an artefact of flailing about trying to make it faster - the production view has defined output columns and uses an inner join. For some reason query analyzer is showing sql server reverting to an index scan on the parent ID, with the params as a predicate (as though its ignoring the date+id index altogether).
-1

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.

Improve this answer

1 Comment

this should be a comment, it doesn't answer the OP's question.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.