Alex Rivera | Logout

How order of joins affect performance of a query

Asked 2011-10-19T10:39:29.240
10

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])
sql sql-server linq join
Edit
Report

1 Answer

9

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:

answered 2011-10-19T10:48:27.097

Your Answer