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 a table with just over a million rows. These rows can have a parent record in the same table which is found by joining to itself on 6 different columns (i.e. there is no single ParentID column). Every child has exactly 1 parent based on these joins, and every record is either a parent or a child record (i.e. there are no grandparent records).

SELECT  *
FROM    TheTable AS ChildRecords
JOIN    TheTable AS ParentRecords
ON      ChildRecords.Column1 = ParentRecords.Column1
AND     ChildRecords.Column2 = ParentRecords.Column2
AND     ChildRecords.Column3 = ParentRecords.Column3
AND     ChildRecords.Column4 = ParentRecords.Column4
AND     ChildRecords.Column5 = ParentRecords.Column5
AND     ChildRecords.Column10 = ParentRecords.Column6

Note column 10 joins to column 6, but this column does not find a unique parent by itself - there could be multiple "parents" with column10 = column6.

This normally works fine, however if we have it as part of a bigger query, SQL Server often tries to resolve this join first before it resolves the others. This is particularly true when it is in a CTE or joining with a CTE. It's often the first join that happens in the query plan. This often results in tens of thousands of joins before it is then filtered down to the 100 or so records that I'm interested in. When this happens the query takes minutes to run.

I've noticed that I can affect the query plan by making it a left join. This makes sense because if it's a left join then SQL Server doesn't know that every child has 1 parent so it always has to find the child records first.

SELECT  *
FROM    TheTable AS ChildRecords
LEFT JOIN TheTable AS ParentRecords
ON      ChildRecords.Column1 = ParentRecords.Column1
AND     ChildRecords.Column2 = ParentRecords.Column2
AND     ChildRecords.Column3 = ParentRecords.Column3
AND     ChildRecords.Column4 = ParentRecords.Column4
AND     ChildRecords.Column5 = ParentRecords.Column5
AND     ChildRecords.Column10 = ParentRecords.Column6

When it runs the query this way it reduces the time from minutes to < 2 seconds.

Because each child has a parent, the left join gives the same result as the inner join, however it feels wrong - it should be an inner join.

I have checked that the indexes are set up properly on this table and I've tried adding and editing the ones that we have but it doesn't change the query plan. The time seems to be because it is querying to get every child/parent combo before it limits it down to just the ones I want.

Can I force SQL Server to run the joins in the order I specified, rather than letting it try to re-order the query?

share|improve this question
    
Is there a Foreign Key constraint from the Table to itself (using these compounds of 6 columns)? Are there indexes (on these compounds)? –  ypercube Jun 28 '13 at 6:04
    
there isn't a FK, but there is an index. –  Greg Jun 28 '13 at 6:08
    
Not two indexes? One on columns (1,2,3,4,5,6) and one on (1,2,3,4,5,10)? –  ypercube Jun 28 '13 at 6:10
    
My suggestion is to leave the LEFT JOIN there. It gives the optimizer some additional info (that it should not care if a child has no parent) that it cannot know otherwise. –  ypercube Jun 28 '13 at 6:17
    
@ypercube there are a number of indexes on that table, both of those you metnion are there. I do care that a child has a parent. The data that I want means I should do an inner join. I can do a left join and assume that each item will match, but I'm just guessing. An inner join would be preferable. –  Greg Jun 28 '13 at 6:30
show 1 more comment

1 Answer

up vote 3 down vote accepted

It seems that I can specify OPTION (FORCE ORDER) at the end of the query and that will make the joins happen in the right order. There are lots of people warning not to do this because it stops SQL from optimising my query so I deffinately will be using it sparingly (and watching those queries closely).

share|improve this answer
4  
I've used FORCE ORDER in the past and I'll likely use it again in the future. Yes it comes with a warning and yes you should put the same warning in any procedure or query where you use it. Much like any other trick/hint/obscure optimisation, cargo cult application tends to follow if you don't. The argument against this approach (and it's a valid/good one) is that if the data distribution changes in favour of an alternative plan you're screwed. Keep that in mind. –  Mark Storey-Smith Jun 28 '13 at 1:07
1  
I've used FORCE ORDER to get a plan when the Optimiser would give up and say 'sorry'! Yes the query was an abomination, but forcing the order shrank the search space so much that the optimiser could produce something useful. The option has its uses. –  John Alan Jun 28 '13 at 13:45
add comment

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.