StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>The answer will of course be "it depends" but based on testing this end...</p> <p>Assuming </p> <ol> <li>1 million products</li> <li><code>product</code> has a clustered index on <code>product_id</code></li> <li>Most (if not all) products have corresponding information in the <code>product_code</code> table</li> <li>Ideal indexes present on <code>product_code</code> for both queries.</li> </ol> <p>The <code>PIVOT</code> version ideally needs an index <code>product_code(product_id, type) INCLUDE (code)</code> whereas the <code>JOIN</code> version ideally needs an index <code>product_code(type,product_id) INCLUDE (code)</code></p> <p>If these are in place giving the plans below</p> <p><img src="https://i.stack.imgur.com/f4GjW.jpg" alt="Plans"></p> <p>then the <code>JOIN</code> version is more efficient. </p> <p>In the case that <code>type 1</code> and <code>type 2</code> are the only <code>types</code> in the table then the <code>PIVOT</code> version slightly has the edge in terms of number of reads as it doesn't have to seek into <code>product_code</code> twice but that is more than outweighed by the additional overhead of the stream aggregate operator</p> <h3>PIVOT</h3> <pre><code>Table 'product_code'. Scan count 1, logical reads 10467 Table 'product'. Scan count 1, logical reads 4750 CPU time = 3297 ms, elapsed time = 3260 ms. </code></pre> <h3>JOIN</h3> <pre><code>Table 'product_code'. Scan count 2, logical reads 10471 Table 'product'. Scan count 1, logical reads 4750 CPU time = 1906 ms, elapsed time = 1866 ms. </code></pre> <p>If there are additional <code>type</code> records other than <code>1</code> and <code>2</code> the <code>JOIN</code> version will increase its advantage as it just does merge joins on the relevant sections of the <code>type,product_id</code> index whereas the <code>PIVOT</code> plan uses <code>product_id, type</code> and so would have to scan over the additional <code>type</code> rows that are intermingled with the <code>1</code> and <code>2</code> rows.</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