SUBQUERIES OPTIMIZATION on SQL Server Views (1 Viewer)

Rx_

Nothing In Moderation
Local time
Today, 09:53
Joined
Oct 22, 2009
Messages
2,803
My test also indicate this is true. Suggest that the conclusion is correct. Connecting to a SQL Server View as a Linked Table is very efficient. My test shows that Linking the Result of a View was generally twice as fast as Joining Two SQL Server Table Views back on MS Access.

http://www.quest.com/whitepapers/sqltuningwp3.pdf
Look at Page 6 - The statement about "the join was faster in terms of both CPU and total elapsed time, " is correct, but the table had the wrong result pasted in the column is probably a typo. The conclusion is accurate.

Avoid SubQuery - use Joins on SQL Server
SUBQUERIES OPTIMIZATION
As a good rule of thumb try to replace all subqueries with joins. The optimizer may sometimes automatically flatten out subqueries and replace them with regular or outer joins. But it doesn’t always do a good job at that. Explicit joins give the optimizer more options to choose the order of tables and find the best possible plan. When you optimize a particular query investigate if getting rid of subqueries makes a difference.
Example
The following queries select the names of all user tables in the pubs database and the clustered index name for each table if one exists. If there is no clustered index, then table name still appears in the list with a dash in the clustered index column. Both queries return the same result set, but the first one uses a subquery, while the second employs an outer join. Compare the query plans produced by Microsoft SQL Server.

SUBQUERY SOLUTION JOIN SOLUTION
Code:
 SELECT st.stor_name AS 'Store', 
 ISNULL((SELECT SUM(bs.qty) 
 FROM big_sales AS bs 
 WHERE bs.stor_id = st.stor_id), 0) 
 AS 'Books Sold' 
FROM stores AS st 
WHERE st.stor_id IN 
 (SELECT DISTINCT stor_id 
 FROM big_sales)
SQL Server parse and compile time:
CPU time = 28 ms, elapsed time = 28 ms.
SQL Server Execution Times:
CPU time = 145 ms, elapsed time = 145 ms.
Table 'big_sales'. Scan count 14, logical reads
1884, physical reads 0, read-ahead reads 0.
Table 'stores'. Scan count 12, logical reads 24,
physical reads 0, read-ahead reads 0.

Code:
 SELECT st.stor_name AS 'Store', 
 SUM(bs.qty) AS 'Books Sold' 
FROM stores AS st 
JOIN big_sales AS bs 
 ON bs.stor_id = st.stor_id 
WHERE st.stor_id IN 
 (SELECT DISTINCT stor_id 
 FROM big_sales) 
GROUP BY st.stor_name

SQL Server parse and compile time:
CPU time = 50 ms, elapsed time = 54 ms.
SQL Server Execution Times:


Table 'big_sales'. Scan count 14, logical reads
966, physical reads 0, read-ahead reads 0.
Table 'stores'. Scan count 12, logical reads 24,
physical reads 0, read-ahead reads 0.

Without probing deeper, we see that the join was faster in terms of both CPU and total elapsed time, requiring almost half as many logical reads as the subquery solution.
 

Attachments

  • SubQuery-TSQL-replaceJoins.jpg
    SubQuery-TSQL-replaceJoins.jpg
    104 KB · Views: 295

Users who are viewing this thread

Top Bottom