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

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Leave a Reply

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