Databases
StackOverflow2013
Postsdbo
POWhich is faster a select sub-query or a left outer join in a paginated result set
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POWhich is faster a select sub-query or a left outer join in a paginated result set
    primarykey
    data
    text
    <p>When querying with MSSQL what is the most efficient way to grab a single column from a computed table and connect it to a result set.</p> <p>Tables: <code>mytable</code> = 20k rows index on <code>mytable.col1</code>, <code>othertable</code> = 30k rows, no index on <code>othertable.col1</code></p> <p><strong>Inline Query</strong> - Runs an query in the select statement</p> <pre><code>SELECT * FROM ( SELECT col1, col2, col3, col4 = (SELECT min(col5) FROM othertable o WHERE m.col1 = o.col1) row = ROW_NUMBER() OVER(ORDER BY somerow) FROM mytable m ) as paged WHERE row BETWEEN 1 AND 25 </code></pre> <p><strong>Join Query</strong> - Joins our table onto the computed table</p> <pre><code>SELECT * FROM ( SELECT col1, col2, col3, o2.col5again row = ROW_NUMBER() OVER(ORDER BY somerow) FROM mytable m JOIN (SELECT col1, min(col5) as col5again FROM othertable o GROUP BY col1) as o2 ON o2.col1 = m.col1 ) as paged WHERE row BETWEEN 1 AND 25 </code></pre> <p>My gut instinct was the <code>JOIN</code> was faster. Yet, upon testing the inline query would finish in an average of 7 seconds while the other query would take >30 seconds when executed in MSSQL studio. </p> <ol> <li>Is using an inline select query really the best way to inject the single column?</li> <li>Does the query optimized wait to run the inline <code>SELECT()</code> statement until after the results have been paged, would that explain the different in running time?</li> </ol> <p>FYI: In my specific example we added an index to <code>othertable.col1</code> and it reduced the query time to 0s, but this question focuses more on whether the JOIN versus SELECT() is better.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USNucleon
    1. USNucleon
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POWhich is faster a select sub-query or a left outer join in a paginated result set
      1. This table or related slice is empty.
      1. VTUpMod
    1. CODo these both compile? Looks like innermost query is missing `FROM`.
      singulars
      1. POWhich is faster a select sub-query or a left outer join in a paginated result set
      1. USThomasMcLeod
    2. COAdded the `FROM`, I haven't tested this exact query since I sanitized this down from the actual example I ran into so that it would making a cleaner question on SO.
      singulars
      1. POWhich is faster a select sub-query or a left outer join in a paginated result set
      1. USNucleon
    3. COThe best way to find out: run both queries with `set statistics time on` to view their duration. You can also run a trace to watch duration.
      singulars
      1. POWhich is faster a select sub-query or a left outer join in a paginated result set
      1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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