How order of joins affect performance of a query

Question

I'm experiencing big differences in timeperformance in my query, and it seems the order of which the joins (inner and left outer) occur in the query makes all the difference. Are there some "ground rules" in what order joins should be in?

Both of them are part of a bigger query. The difference between them is that the left join is placed last in the faster query.

Slow query: (> 10 minutes)

SELECT [t0].[Ref], [t1].[Key], [t1].[Name],  
    (CASE 
        WHEN [t3].[test] IS NULL THEN CONVERT(NVarChar(250),@p0)
        ELSE CONVERT(NVarChar(250),[t3].[Key])
     END) AS [value], 
    (CASE 
        WHEN 0 = 1 THEN CONVERT(NVarChar(250),@p1)
        ELSE CONVERT(NVarChar(250),[t4].[Key])
     END) AS [value2]

FROM [dbo].[tblA] AS [t0]
INNER JOIN [dbo].[tblB] AS [t1] ON [t0].[RefB] = [t1].[Ref]

LEFT OUTER JOIN (
    SELECT 1 AS [test], [t2].[Ref], [t2].[Key]
    FROM [dbo].[tblC] AS [t2]
    ) AS [t3] ON [t0].[RefC] = ([t3].[Ref])

INNER JOIN [dbo].[tblD] AS [t4] ON [t0].[RefD] = ([t4].[Ref])

Faster query: (~ 30 seconds)

SELECT [t0].[Ref], [t1].[Key], [t1].[Name],  
    (CASE 
        WHEN [t3].[test] IS NULL THEN CONVERT(NVarChar(250),@p0)
        ELSE CONVERT(NVarChar(250),[t3].[Key])
     END) AS [value], 
    (CASE 
        WHEN 0 = 1 THEN CONVERT(NVarChar(250),@p1)
        ELSE CONVERT(NVarChar(250),[t4].[Key])
     END) AS [value2]

FROM [dbo].[tblA] AS [t0]
INNER JOIN [dbo].[tblB] AS [t1] ON [t0].[RefB] = [t1].[Ref]

INNER JOIN [dbo].[tblD] AS [t4] ON [t0].[RefD] = ([t4].[Ref])

LEFT OUTER JOIN (
    SELECT 1 AS [test], [t2].[Ref], [t2].[Key]
    FROM [dbo].[tblC] AS [t2]
    ) AS [t3] ON [t0].[RefC] = ([t3].[Ref])

Answer 1

Generally INNER JOIN order won't matter because inner joins are commutative and associative. In both cases, you still have t0 inner join t4 so should make no difference.

Re-phrasing that, SQL is declarative: you say "what you want", not "how". The optimiser works the "how" and will re-order JOINs as needed, looking as WHEREs etc too in practice.

In complex queries, a cost based query optimiser won't exhaust all permutation so it could matter occasionally.

So, I'd check for these:

• You said these are part of a bigger query, so this section matters less because the whole query matters.
• Complexity can be hidden using views too if any of the tables are actually views
• Is this repeatable, no matter what order code runs in?
• What are the query plan differences?

See some other SO questions:

• how to best organize the Inner Joins in (select) statement
• SQL Server 2005 - Order of Inner Joins

Answer 2

If u have more than 2 tables it is important to order table joins. It can make big differences. First table should get a leading hint. First table is that object with most selective rows. For example: If u have a member table with 1.000.000 people and you only want to select female gender and it is first table, so you only join 500.000 records to next table. If this table is at the end of join order (maybe table 4,5 or 6) then each record (worst case 1.000.000) will be joined. This includes inner and outer joins.

The Rule: Start with most selective table, then join next logical most selective table.

Converting functions and beautifying should do last. Sometimes it is better to bundle the shole SQL in brackets and use expressions and functions in outer select statements.

Answer 3

In the case of left join it impact a lot the performance. i was having a problem in a select query that was like that :

select distinct count(p0_.id) over ()        as col_0_0_,
       p0_.id                       as col_1_0_,
       p0_.lp           as col_2_0_,
       0
                                          as col_3_0_,
       max(coalesce(i6_.cft, i7_.rfo,
                    ''))                  as col_4_0_,
       p0_.pdv              as col_5_0_,
       (s8_.qer)
                                          as col_6_0_,
       cf1_.ests as col_7_0_
from Produit p0_
         left outer join CF cf1_ on p0_.fk_cf = cf1_.id
         left outer join CA c2_ on cf1_.fk_ca = c2_.id
         left outer join ml mt on c2_.fk_m = mt.id
         left outer join sk s8_ on p0_.id = s8_.fk_p
         left outer join rf r5_ on
        rp4_.fk_r = r5_.id
         left outer join
     in i6_ on r5_.fk_ireftc = i6_.id
         left outer join r_p_r rp4_ on p0_.id = rp4_.fk_p
         left outer join
     ir i7_ on r5_.fk_if = i7_.id
      left outer join re_p_g gc9_ on p0_.id = gc9_.fk_p
         left outer join gc g10_ on gc9_.fk_g = g10_.id
where
  and (p0_.lC is null or p0_.lS = 'E')
  and g10_.id is null or g10_.id
  and r5_.fk_i is null
group by col_1_0_, col_2_0_, col_3_0_, col_5_0_, col_6_0_, col_7_0_
order by col_2_0_ asc, p0_.id
limit 10;

the query takes 13 to 15 seconde to execute, when i change the order its takes 1 to 2 seconde.

select distinct count(p0_.id) over ()        as col_0_0_,
       p0_.id                       as col_1_0_,
       p0_.lp           as col_2_0_,
       0
                                          as col_3_0_,
       max(coalesce(i6_.cft, i7_.rfo,
                    ''))                  as col_4_0_,
       p0_.pdv              as col_5_0_,
       (s8_.qer)
                                          as col_6_0_,
       cf1_.ests as col_7_0_
from Produit p0_
         left outer join CF cf1_ on p0_.fk_cf = cf1_.id
         left outer join sk s8_ on p0_.id = s8_.fk_p
         left outer join r_p_r rp4_ on p0_.id = rp4_.fk_p
         left outer join re_p_g gc9_ on p0_.id = gc9_.fk_p
         left outer join CA c2_ on cf1_.fk_ca = c2_.id
         left outer join ml mt on c2_.fk_m = mt.id
         left outer join rf r5_ on
        rp4_.fk_r = r5_.id
         left outer join
     in i6_ on r5_.fk_ireftc = i6_.id
         left outer join
     ir i7_ on r5_.fk_if = i7_.id
         left outer join gc g10_ on gc9_.fk_g = g10_.id
where
  and (p0_.lC is null or p0_.lS = 'E')
  and(g10_.id is null
  and r5_.fk_i is null
group by col_1_0_, col_2_0_, col_3_0_, col_5_0_, col_6_0_, col_7_0_
order by col_2_0_ asc, p0_.id
limit 10;

in my case i change the order in case when i load a table i use all the join that use this table in the join that follow and not to load it in another block. like in my p0_ table i made all the left join in the first 4 lines not like in the first code.

PS: to test my perf in postgre i use this website: http://tatiyants.com/pev/#/plans/new

Answer 4

At least in SQLite, I found out that it makes a huge difference. Actually it didn't need to be a very complex query for the difference to show itself. My JOIN statements were inside an embedded clause however.

Basically, you should start with the most specific limitations first, as Christian has pointed out.