Solved

which Query is more efficient: using UNIONs or using WHERE

Posted on 2006-07-18
4
253 Views
Last Modified: 2008-01-09
hi experts,
I have a query QUERY 1. I had a post regarding this query but since there was no reply maybe because I submitted a stupid question I am submitting a totally different Q now. I realized that QUERY 1 could be changed to QUERY 2. Mainly QUERY 1 consists of a number of UNIONS to generate a result set. QUERY 2 produces the same result set but with a heftier FROM and uses the WHERE to restrict this result set. I would like your opinion on which query you feel is more effiecient. I tried to compare logical reads: but seem to be very similar: in 1 there are 24 logical reads for WorkTable (some temporary table) while in 2 there are 25 logical reads in CategoryText table. I tried to time the query but with no obvious results. The estimated execution plan for 1 is more complex, but it is more complex for me to understand too.
By the way these queries are only a sample, the true QUERY 1 would contain some 5 UINIONS.
10x a lot.

===================================QUERY 1
declare @start_time datetime
select @start_time =getdate()
SELECT
                   dbo.SwitchInfo.ID, dbo.SwitchType.SwitchType, dbo.Manufacturer.Manufacturer, dbo.Customer.Customer, dbo.SwitchInfo.ReportNo, dbo.SwitchInfo.CarModel
FROM         dbo.SwitchInfo INNER JOIN
                    dbo.SwitchType ON dbo.SwitchInfo.SwitchTypeID = dbo.SwitchType.ID INNER JOIN
                  dbo.Manufacturer ON dbo.SwitchInfo.ManufacturerID = dbo.Manufacturer.ID INNER JOIN
              dbo.Customer ON dbo.SwitchInfo.CustomerID = dbo.Customer.ID INNER JOIN

            (SELECT  dbo.ReportClosingMounting.ReportID AS JoinCondition
                                    FROM dbo.ReportClosingMounting  WHERE  dbo.ReportClosingMounting.CloseMountID=2 or  dbo.ReportClosingMounting.CloseMountID=2


            union select dbo.CategoryText.ReportID AS JoinCondition
                                    FROM dbo.CategoryText INNER JOIN dbo.Category ON dbo.CategoryText.CategoryID = dbo.Category.ID
                                    WHERE (dbo.CategoryText.CategoryID = 3) or CategoryText.Text LIKE '%a%'


UNION SELECT dbo.SwitchInfo.ID AS JoinCondition
                                    FROM dbo.SwitchInfo
                                    WHERE dbo.SwitchInfo.SwitchTypeID =1




-- BOM
union select dbo.BOM.ReportID As JoinCondition
                                    FROM dbo.BOM
                                    WHERE dbo.BOM.MaterialID =1 or dbo.BOM.PartDesc LIKE '%x%'
)
X ON dbo.SwitchInfo.ID = X.JoinCondition


select 'elapsed time, sec' =datediff(second, @start_time,getdate())
go
-----------------------------------------------------------RESULTS
SQL Server parse and compile time:
   CPU time = 20 ms, elapsed time = 23 ms.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

(4 row(s) affected)

Table 'Worktable'. Scan count 5, logical reads 24, physical reads 0, read-ahead reads 0.
Table 'BOM'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0.
Table 'SwitchInfo'. Scan count 2, logical reads 4, physical reads 0, read-ahead reads 0.
Table 'Category'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0.
Table 'CategoryText'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0.
Table 'ReportClosingMounting'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0.
Table 'SwitchType'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0.
Table 'Manufacturer'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0.
Table 'Customer'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

(1 row(s) affected)


SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

declare @start_time datetime
select @start_time =getdate()

SELECT DISTINCT
                      dbo.SwitchInfo.ID, dbo.SwitchInfo.ReportNo, dbo.SwitchInfo.CarModel, dbo.BOM.ReportID, dbo.BOM.PartDesc,
                      dbo.ReportClosingMounting.ReportID AS Expr1, dbo.Customer.Customer, dbo.SwitchType.SwitchType, dbo.Manufacturer.Manufacturer
FROM         dbo.BOM INNER JOIN
                      dbo.SwitchInfo ON dbo.BOM.ReportID = dbo.SwitchInfo.ID INNER JOIN
                      dbo.CategoryText ON dbo.SwitchInfo.ID = dbo.CategoryText.ReportID INNER JOIN
                      dbo.ReportClosingMounting ON dbo.SwitchInfo.ID = dbo.ReportClosingMounting.ReportID INNER JOIN
                      dbo.Customer ON dbo.SwitchInfo.CustomerID = dbo.Customer.ID INNER JOIN
                      dbo.SwitchType ON dbo.SwitchInfo.SwitchTypeID = dbo.SwitchType.ID INNER JOIN
                      dbo.Manufacturer ON dbo.SwitchInfo.ManufacturerID = dbo.Manufacturer.ID
WHERE     (dbo.SwitchInfo.SwitchTypeID = 1) AND (dbo.BOM.MaterialID = 1) AND (dbo.BOM.PartDesc LIKE '%x%') AND (dbo.CategoryText.CategoryID = 3) AND
                      (dbo.CategoryText.Text LIKE '%a%') AND (dbo.ReportClosingMounting.CloseMountID = 1) OR
                      (dbo.ReportClosingMounting.CloseMountID = 2)

select 'elapsed time, sec' =datediff(second, @start_time,getdate())
go
---------------------------------------RESULTS----------------------
SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

(1 row(s) affected)

Table 'CategoryText'. Scan count 5, logical reads 25, physical reads 0, read-ahead reads 0.
Table 'SwitchType'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0.
Table 'Manufacturer'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0.
Table 'SwitchInfo'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
Table 'BOM'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0.
Table 'ReportClosingMounting'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0.
Table 'Customer'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

(1 row(s) affected)


SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

0
Comment
Question by:stephen_rota
4 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17128560
This depends alot on some factors... but usually, the WHERE .. OR should be better, as in the worst case, it will
however, sql might be able in some circumstances to detect and rewrite the UNION into the WHERE OR (resp vice-versa)...
0
 
LVL 12

Expert Comment

by:ill
ID: 17128563
It's better to use the second query. Worktable in first one must be created in temporary database ( you do not see these writes in result). Queries with UNION are also harder to rewrite.
0
 
LVL 6

Accepted Solution

by:
ksbhat earned 500 total points
ID: 17128682
Apparently, avoiding union joins is better on query performance.
This document gives a good idea of how union joins work...  www2.sas.com/proceedings/sugi29/064-29.pdf
0
 

Author Comment

by:stephen_rota
ID: 17151996
got your message, i implemented the WHERE version. As a good friend of mine noted (lordmagic) it is cleaner.  angelIII I saw your profile: WOW. 10x a lot guys!
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question