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.

SQL server 2008 R2 query optimizer puzzle

We have two tables, both containing 9 million rows. 70.000 rows are different, the others are the same.

This is fast, 13 seconds,

select * from bigtable1
except select * from similar_bigtable2

This sorts the output and is also fast, 13 seconds as well,

select * into #q from bigtable1
except select * from similar_bigtable2
select * from #q order by sort_column

While this is enormously slow:

;with q as (
    select * from bigtable1
    except select * from similar_bigtable2
)
select * from q order by sort_column

And even a "trick" that I sometimes use to hint SQL Server that it needs to precalculate a certain part of the query before it moves on, doesn't work and results in slow query as well:

;with q as (
    select top 100 percent * from bigtable1
    except select * from similar_bigtable2
)
select * from q order by sort_column

Looking at the query plans the reason is not hard to find:

Query plan Query plan with ORDER BY

SQL Server places two sorts of 9 million rows before the hashmatch, while I would prefer it to have added only one sort of 70.000 rows after the hashmatch.

So the question: how can I instruct the query optimizer to do that?

share|improve this question

migrated from stackoverflow.com Jul 8 '12 at 9:17

This question came from our site for professional and enthusiast programmers.

3  
It does not sort before the hashmatch, it sorts and then does a merge-join (not a hash-join). Maybe there is a hint to force a hash-join (or prevent a merge-join)? –  Thilo Jul 6 '12 at 12:39
3  
It seems that the SQL Server query optimizer determined that sorting the data was beneficial so it could use the much faster Merge Join (which only works for sorted data) instead of the much slower Hash Match Join or Nested Loop Join .... –  marc_s Jul 6 '12 at 12:39
9  
Have you tried alternatives to EXCEPT (e.g. OUTER JOIN)? I realize the syntax is less convenient but you may be able to play with index/join hints better there (or you may not need to). The alternative you're using now (stuff into a #temp table first) is a last resort workaround but in some cases is the only way to force the optimizer to completely separate two parts of a query in a way that you want. –  Aaron Bertrand Jul 6 '12 at 12:46

3 Answers 3

The main difference between these two query plans is in fact in the difference of Hash Match and Merge Join. Hash Match is more efficient and as you can see the query runs faster in option 1 (not using CTE).

CTE is a great tool, but it seems to be not efficient in two cases,Complex Predicates or Non-Unique Parent/Child Key. In your case there is no unique key and SQL server has to sort the data sets first to be able to fulfill your requirement. Have a look at the link below which tells you more on this issue: http://blogs.msdn.com/b/sqlcat/archive/2011/04/28/optimize-recursive-cte-query.aspx

So it seems either you have to accept its slowness or rewrite the logic with WHILE loop which can be more efficient.

share|improve this answer

Try this, any better?

select * from
(
    select * from bigtable1
    except 
    select * from similar_bigtable2
) t
order by sort_column
share|improve this answer

This isn't an ideal solution but if you aren't able to structure the tsql to generate an efficient plan you could set a plan guide to force the plan you want. Doing this would mean that if a more efficient plan becomes available SQL won't consider it but it is an option.

share|improve this answer

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.