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

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

Leave a Reply

Your email address will not be published. Required fields are marked *