Solved

which Query is more efficient: using UNIONs or using WHERE

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
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…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

932 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

12 Experts available now in Live!

Get 1:1 Help Now