Here goes my 2nd part of Tune your SQL Server subquery

Well,if my query is having UNION operator,we normally replace it by UNION ALL to improve performance

According to itzik-ben gan

UNION ALL returns one resultset with all rows from both inputs. UNION returns one result set with the distinct rows from both inputs.

I will show here some more methods to tune UNION instead of writing UNION ALL

My demo query is

I am using concept of Join factorization to tune above query,so modied query is

As we can see here 1st query containsTableEmployee onboth upperandlowerpart ofUnion,andit isa common

factor betweenthese two parts ,so i took common factor outside the brackets ..simple mathematics anddid UNIONofuncommon factors

Cost of modified query is


Also while observing the overall execution plan of both the queries 2nd query contains less operators as compared to 1st query and table Employee is getting used in 2 places  as compared to 2nd query .Here are the screenshots

Screenshot for 1st query


Screenshot for 2nd query


Ill update you some more query tuning tips in near future ,till then keep reading



Akash Gautam

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook