3

I have query which has 40+ left outer joins. Query is not that slow but I'm interested that can I optimize this (procedure is most used one in db). I had idea that "a" table has type column which value basically says that there is data in c or d tables. So I thought that I add AND a.type LIKE 'type1' and AND a.type LIKE 'type2' to b and c tables join clause. But I don't see any improvements and i do not understand why. every table has pk(id), Query returns 1 row and 2000+ columns.

Query is like this :

SELECT 'text1,text2,text3' cola, a.id,a.type, a.data1 ..
, 'text12,text14...' as colb, b.data,b.data2 ..
, 'textc1,textc2...' as colc , c.data,c.data2 ...
-- 40+ tables with same logic
FROM a 
LEFT OUTER JOIN b on a.id = b.id
LEFT OUTER JOIN c on a.id = c.id
LEFT OUTER JOIN d on a.id = d.id
--repeat 40 times to different tables, always using a.id to join 
WHERE a.id = 2

I tried some thing like:

SELECT 'text1,text2,text3' cola, a.id,a.type, a.data1 ..
, 'text12,text14...' as colb, b.data,b.data2 ..
, 'textc1,textc2...' as colc , c.data,c.data2 ...
-- 40+ tables with same logic
FROM a 
LEFT OUTER JOIN b on (a.id = b.id and a.type LIKE 'typeb')
LEFT OUTER JOIN c on (a.id = c.id and a.type LIKE 'typec')
LEFT OUTER JOIN d on (a.id = d.id and a.type LIKE 'typed')
--repeat 48 times to different tables, always using a.id to join 
WHERE a.id = 2

If there is no id in table then it returns col(table) which is text and several nulls. Plan generated is clusteded index seek and nested loop for every join in query (50 times). Every clustered index seek takes 2% of overall execution time. exec time is 300ms.

QUESTION: Can this query run faster

Partial solution @ypercubeᵀᴹ recommended following test , which currently seems to be better solution :

SELECT * FROM a 
OUTER APPLY (SELECT * FROM b WHERE a.id = b.id) b 
OUTER APPLY (SELECT * FROM c WHERE a.id = c.id) c 
OUTER APPLY (SELECT * FROM d WHERE a.id = d.id) d 
-- repeat ~50 times 
WHERE a.id = 1000

SQL Sentry plan explorer gives results that OUTER APPLY was twice as fast than original version with LEFT JOIN when comparing Duration and CPU values. Estimated costs when all test queries were run in same time had exactly same cost. OUTER APPLY did 30% less reads and writes compared to LEFT OUTER JOIN (two versions, original and 'SELECT * FROM a LEFT OUTER JOIN b ON a.id =b.id etc..)

Adding "AND a.type = 'typeb'" to OUTER APPLY QUERY so that planner would not try seek table's index, did not work. Query with or without second test clause (added to each OUTER APPLY) had same plan and same performance.

OUTER APPLY version of plan is Clustered Index Seek on every table was followed with Compute scalar which was followed nested join loop like original execution plan was.

POSSIBLE ANSWER: Without changing query "logic" OUTER APPLY seems to be faster than LEFT OUTER JOIN when you join over 30 tables like this. Additional test in WHERE did not speed things up. Plan generation time when executing QUERY were more than execution time itself . So plan which checks id in every table index is best solution because generating new plan would take more time than it saves.

  • Comments are not for extended discussion; this conversation has been moved to chat. – Paul White Oct 20 '16 at 12:26
  • Do the tables used in the LEFT OUTER JOIN's have the same schema? Or at least maybe the columns you are selecting from those tables are all the same data type? It is kind of limiting but you may be able to use a CTE and UNION all the various tables together and then utilize a PIVOT. But before going down that road I want to make sure the situation allows for that specific use case. – Kirk Saunders Feb 28 at 21:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.