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])
1 Answer. The order doesn't matter for INNER joins. As long as you change your selects from SELECT * to SELECT a.
No. That's basic optimisation for the database; the optimizer will decide what is the best strategy to join the tables, regardless of the order in which they appear in the from clause.
No, it doesn't matter. SQL is declarative, not procedural, so the order shouldn't matter.
When the main table (first non-const one in the execution plan) has a restrictive condition (WHERE id = ?) and the corresponding ON condition is on a NULL value, the "right" table is not joined --- this is when LEFT JOIN is faster.
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:
See some other SO questions:
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With