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