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

1_SQL_Server_Tune_your_Subquery2

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

2_SQL_Server_Tune_your_Subquery2

Screenshot for 2nd query

3_SQL_Server_Tune_your_Subquery2

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

 

Regards

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