Edit Question - KnowledgeHub

Title Body

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])

Tags (comma-separated) Cancel