Solved

which Query is more efficient: using UNIONs or using WHERE

Posted on 2006-07-18
4
252 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
how to just get time from a date 6 33
SQL USE DATABASE VARIABLE 5 30
Get Next number from Stored Procedure 8 23
too many installs coming along with SQL 2016? 1 17
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

829 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