StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You can move your query to an <a href="http://technet.microsoft.com/en-us/library/ms175156%28v=sql.105%29.aspx" rel="nofollow">APPLY</a>, this ensures that it is only executed once.</p> <p>I think your example would become something like:</p> <pre><code>FROM tblSticker WITH ( NOLOCK ) INNER JOIN tblDetail WITH ( NOLOCK ) ON tblSticker.intStore = tblDetail.intStore AND tblSticker.intStickerNo = tblDetail.intStickerNo AND tblSticker.dtmStickerDate = tblDetail.dtmStickerDate LEFT OUTER JOIN tblsubteam ON tblDetail.intStore = tblSubTeam.intStore AND SUBSTRING ( tblDetail.strMiscText , 12 , 4 ) = tblSubTeam.strSubTeam INNER JOIN tblStore st ON st.intStore=tblDetail.intStore OUTER APPLY ( SELECT DeCostFactor = CAST(decCostFactor AS DECIMAL(9,4)) FROM tblsubteam WITH (NOLOCK) WHERE intstore = st.intStore AND strsubteam = SUBSTRING(tblDetail.strMiscText,12,4) ) dcf </code></pre> <p>Then instead of your subquery you can simply refer to <code>dcf.DeCostFactor</code>.</p> <p>As a generic test I have used the following, that simply executes a correlated subquery 4 times, compared to a single APPLY</p> <pre><code>SET STATISTICS IO ON; WITH T AS ( SELECT A = Number, B = Number + 1 FROM Master..spt_values WHERE Type = 'P' ) SELECT T.A, T.B, SubQuery = (SELECT T2.B FROM T T2 WHERE T2.A = T.B), SubQuery2 = (SELECT T2.B FROM T T2 WHERE T2.A = T.B), SubQuery3 = (SELECT T2.B FROM T T2 WHERE T2.A = T.B), SubQuery4 = (SELECT T2.B FROM T T2 WHERE T2.A = T.B) FROM T; -- USING OUTER APPLY WITH T AS ( SELECT A = Number, B = Number + 1 FROM Master..spt_values WHERE Type = 'P' ) SELECT T.A, T.B, SubQuery = SubQuery.B, SubQuery2 = SubQuery.B, SubQuery3 = SubQuery.B, SubQuery4 = SubQuery.B FROM T OUTER APPLY ( SELECT T2.B FROM T T2 WHERE T2.A = T.B ) SubQuery; </code></pre> <p>THE IO Statistics speak for themselves:</p> <p><strong>Multiple Subqueries</strong></p> <pre><code>Table 'spt_values'. Scan count 8193, logical reads 24625, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. </code></pre> <p><strong>OUTER APPLY</strong></p> <pre><code>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 'spt_values'. Scan count 2, logical reads 18, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. </code></pre> <hr> <p><strong>EDIT</strong></p> <p>I don't think you need the subqueries at all, you have already joined to the table <code>tblSubteam</code> on the same criteria:</p> <pre><code>LEFT OUTER JOIN tblsubteam ON tblDetail.intStore = tblSubTeam.intStore AND SUBSTRING ( tblDetail.strMiscText , 12 , 4 ) = tblSubTeam.strSubTeam </code></pre> <p>Why can't you just use <code>tblsubteam.DeCostFactor</code> instead of the subquery?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload