Performance of conditional join

I have a question about perf of the following query:

DECLARE @detail_level INT = 1,
        @DETAIL_1     INT = 1,
        @DETAIL_2     INT = 2,
        @DETAIL_3     INT = 4

SELECT mtbl.*,
       CASE
         WHEN @detail_level & @DETAIL_1 <> 0 THEN tbl_1.value
         ELSE NULL
       END,
       CASE
         WHEN @detail_level & @DETAIL_2 <> 0 THEN tbl_2.value
         ELSE NULL
       END,
       CASE
         WHEN @detail_level & @DETAIL_3 <> 0 THEN tbl_3.value
         ELSE NULL
       END
FROM   mtbl
       LEFT OUTER JOIN tbl_1
         ON @detail_level & @DETAIL_1 <> 0
            AND mtbl.KEY = tbl_1.KEY
       LEFT OUTER JOIN tbl_2
         ON @detail_level & @DETAIL_2 <> 0
            AND mtbl.KEY = tbl_2.KEY
       LEFT OUTER JOIN tbl_3
         ON @detail_level & @DETAIL_3 <> 0
            AND mtbl.KEY = tbl_3.KEY
WHERE  mtbl.KEY = @something 

Will the query engine optimize the query by using the detail level filter to avoid unnecessary table join and unnecessary table row access for those detail table columns?

If yes, will query engine even not acquire index locks for those unnecessary table?

I captured the query plan in SQL Server 2008 and index search operations still existed even detail level did not match. But the query with less details did (~ 30%) faster than the one with full details (data volumn ~ 500,000).

Answers


You would need to check the execution plan to be sure. When I create the following example tables

CREATE TABLE tbl_1([KEY] INT PRIMARY KEY, value INT)
CREATE TABLE tbl_2([KEY] INT PRIMARY KEY, value INT)
CREATE TABLE tbl_3([KEY] INT PRIMARY KEY, value INT)
CREATE TABLE mtbl([KEY] INT PRIMARY KEY, value INT)

INSERT INTO mtbl VALUES(1,1),(2,2) 
INSERT INTO tbl_1 VALUES(1,1),(2,2)
INSERT INTO tbl_2 VALUES(1,1),(2,2)
INSERT INTO tbl_3 VALUES(1,1),(2,2)

your query gives the execution plan

Whilst this appears to show operations against all four tables of equal costs, this is somewhat misleading. Only two of them are actually accessed. (Costs shown even in actual execution plans are just estimated costs.)

After running SET STATISTICS IO ON;, your example query returns

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_1'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'mtbl'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

As you can see, neither tbl_2 nor tbl_3 feature in the results. Looking at the properties of the index seek on tbl_3 shows that this operator was never executed (Number of Executions = 0)

This is because the outer join is underneath a filter with a startup predicate and only called when that evaluates to true.

Regarding your question about locking, no row or page locks are taken against tbl_2 or tbl_3 but IS locks are still taken out against the object. This can be seen by running

DBCC TRACEON(1200,3604,-1)
/*Your query*/ 
DBCC TRACEOFF(1200,3604,-1)

IS locks are not compatible with schema modification, bulk update or exclusive locks on the object.


Need Your Help

How to handle “--” in the shell script arguments?

bash shell zsh sh

This question has 3 parts, and each alone is easy, but combined together is not trivial (at least for me) :)

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.