SQL Server Subquery – Tune your Subquery-2

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

set statistics io on
select a.NationalIDNumber,b.StartDate
 from  AdventureWorks2012.HumanResources.Employee as a,
AdventureWorks2012.HumanResources.EmployeeDepartmentHistory as b
,AdventureWorks2012.Purchasing.PurchaseOrderHeader as c
where a.BusinessEntityID=b.BusinessEntityID and
b.BusinessEntityID=c.EmployeeID 
 union
 select a.NationalIDNumber,b.StartDate
from  AdventureWorks2012.HumanResources.Employee as a,
AdventureWorks2012.HumanResources.EmployeeDepartmentHistory as b
,AdventureWorks2012.HumanResources.JobCandidate as c
where a.BusinessEntityID=b.BusinessEntityID and
b.BusinessEntityID=c.BusinessEntityID

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

set statistics io on
select a.NationalIDNumber,d.StartDate from
(select b.startdate,b.BusinessEntityID
from AdventureWorks2012.HumanResources.EmployeeDepartmentHistory as b
,AdventureWorks2012.Purchasing.PurchaseOrderHeader as c
where b.BusinessEntityID=c.EmployeeID 
union
select b.startdate,b.BusinessEntityID
from AdventureWorks2012.HumanResources.EmployeeDepartmentHistory as b
,AdventureWorks2012.HumanResources.JobCandidate as c
where b.BusinessEntityID=c.BusinessEntityID) as d ,AdventureWorks2012.HumanResources.Employee as a
where a.BusinessEntityID=d.BusinessEntityID

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

Regards

Akash Gautam

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

Follow me on Twitter |  Follow me on FaceBook