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.

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
share|improve this question
    
It is not a good example as you join on a constant. However to answer your question, there will be no difference as long as your join condition is the same to where clause. –  Stoleg Jun 10 '13 at 12:31
add comment

3 Answers

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: http://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.

share|improve this answer
    
+1 especially the point about prioritizing optimization. This is the kind of thing you look at when you've exhausted all other avenues and the query is still exhibiting performance issues. –  Aaron Bertrand Jun 10 '13 at 13:23
add comment

It is possible that you will get different plans for these two queries, but if you do, it is probably because the text does not match and some other variable is different (set setting, default schema of the user calling, etc). It is possible that, depending on how many rows are on each side, the filter on the main table could happen first, vs. the expected behavior (the subquery evaluated first). For most cases I don't expect performance to be any different and I still think the plan shape will be the same, but I can't authoritatively state that it won't ever happen across all potential cases.

That said, even if the performance is the same, I would probably use the latter form, where you are explicitly eliminating rows you don't want as early as possible. Since T-SQL is declarative, you have no control over the order of operations, so making sure it is possible for SQL Server to eliminate rows as soon as possible, regardless of which path/order it chooses, means in the best cases you are better off and in the worst case you typed a few extra characters for nothing.

I haven't seen it in ages, so it may not exist anymore, but there used to be a potential gain in being overly redundant in join clauses. For example if you had this:

FROM dbo.t1 
JOIN dbo.t2 ON t1.id = t2.id
JOIN dbo.t3 ON t2.id = t3.id

The optimizer could benefit from additional hinting that you and I know are just true and obvious due to transitive properties. But in some cases the optimizer couldn't see it, and adding the obvious could yield a better plan:

FROM dbo.t1 
JOIN dbo.t2  ON t1.id = t2.id
JOIN dbo.t3  ON t2.id = t3.id
            AND t1.id = t3.id -- additional "help"
share|improve this answer
add comment

Given your example, I took the liberty of adding a few indexes and populating the temp tables with some data. The test code I used can be found below (Note: do not do this on a production system, as part of the code frees the entire plan cache):

if exists (select 1 from tempdb.sys.tables where name like '#main_select%')
    drop table #main_select;
create table #main_select
(id int, someValue varchar(10))

if exists (select 1 from tempdb.sys.tables where name like '#left_table%')
    drop table #left_table;
create table #left_table
(id int,main_id int, aggregated  int)

create clustered index IX_MainSelect
on #main_select(id);
go

create clustered index IX_LeftTable
on #left_table(id);
go

create index IX_LeftTableMainId
on #left_table(main_id, aggregated);
go

declare @i int;
set @i = 0;

while @i < 1000
begin
    insert into #main_select(id, someValue)
    values(@i, replicate('a', 10));

    insert into #left_table(id, main_id, aggregated)
    values(@i + 10, @i, @i * 10);

    set @i += 1;
end

dbcc freeproccache();
go

SELECT *
FROM #main_select
LEFT 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

dbcc freeproccache();
go

SELECT *
FROM #main_select
LEFT 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

Grabbing the post execution plans of both these runs, I get the following result:

enter image description here

As you can see here, the index seek on #left_table is identical, as SQL Server is seeing these as equivalent data retrieval operations. Both index seek operations have seek predicates that are the following:

Seek Keys[1]: Prefix: [tempdb].[dbo].[#left_table].main_id = Scalar Operator((1))

I will take the same stance as Aaron on this, though. In this case, the plans should be identical, but I can't say the same for other use-cases.

share|improve this answer
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.