Solved

which Query is more efficient: using UNIONs or using WHERE

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

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Need Counts 11 43
Sql query 34 22
Test a query 23 19
Split Data in 1 column into 2 columns 8 21
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now