Sql-server – Will performance be better if I specify a filter multiple times

join;performancesql serversql-server-2008-r2

Do I gain more performance if I repeat the filter in the WHERE clause on both sides of an OUTER JOIN?

To clarify using code:

select * 
from #main_select
left outer join 
(
  select 
    main_id 
   ,sum(aggregated) as agg
  from #left_table 
  group by main_id
) as grouped 
on #main_select.id=grouped.main_id
where #main_select.id = 1

--same reuslts

select * 
from #main_select
left outer join 
(
  select 
    main_id 
   ,sum(aggregated) as agg
  from #left_table 
  where main_id=1 -- in this case I added where same as Left outer join
  group by main_id
) as grouped 
on #main_select.id=grouped.main_id
where #main_select.id = 1

Best Answer

In general there will be no difference. SQL is a declarative language that gets translated into an "execution plan" by the query optimizer. The goal of the optimizer is to provide the best execution plan the produces the results that were asked for in the query. That means that two logically equivalent queries should always produce the same execution plan.

So far the theory. In praxis however, there are way to many ways to build an execution plan for an even moderately complex query. (See for example my answer here: https://stackoverflow.com/questions/16974241/sql-server-join-selects-slower-than-join-select-with-local-table/16974641#16974641) That means that instead of aiming for the best plan, the optimizer can only aim for a good enough plan. The plans the optimizer is even considering are selected based on a set of rules and heuristics and are certainly influenced by the original query.

In SQL Server 2000 a lot of the performance optimization strategies were build around helping the optimizer to start with a better plan set by rewriting the query into another logically equivalent form. Since SQL 2005 the optimizer rarely needs that form of help anymore, but there are still a few queries out there that could benefit from a rewrite.

So to answer your question, if you observed that a query got faster by supplying that additional "hint" in the join condition, the above should give you an explanation. In general this is not the first thing to look at when trying to optimize a query.

Instead you should first make sure that your tables are properly indexed and all statistics are up to date.

Related Question