Solved

which Query is more efficient: using UNIONs or using WHERE

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to 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
Access sql to sql server express 10 32
Get the latest status 8 30
VBScript Write Column Headers 3 34
Sql Query 6 62
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…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

786 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