"Select Top 10, then Join Tables", instead of "Select Top 10 from Joined Tables"

Question

I have inherited a stored procedure which performs joins across eight tables, some of which contain hundreds of thousands of rows, then selects the top ten entries from the result of that join.

I have enough information at the start of the procedure to select those ten rows from a single table, and then perform those joins on those ten rows, rather than on hundreds of thousands of intermediate rows.

How do I select those top ten rows and then only do joins on those ten rows, instead of performing joins all of the thousands of rows in the table?

Answer 1

I should try:

SELECT * FROM
    (SELECT TOP 10 * FROM your_table
     ORDER BY your_condition) p
INNER JOIN second_table t
    ON p.field = t.field

Answer 2

1. The optimizer may not be able to perform the top 10 first if you have inner joins, since it can't be sure that the inner joins won't exclude rows later on. It would be a bug if it selected 10 rows from the main table, and then only returned 7 rows at the end because of a join. Using Marco's rewrite may gain you performance for this reason since you're expressly stating that it's safe to limit the rows before the joins.

2. If you're query is sufficiently complicated, the query plan optimizer may run out of time finding a good plan. It's only given a few hundred milliseconds, and with even a few joins there are probably thousands of different ways it can execute the query (different join orders, etc). If this is the case, you'll benefit from storing the first 10 rows in a temp table first, and then using that later like this:

   select top 10 *
   into #MainResults
   from MyTable
   order by your_condition;
   
   
   select *
   from #MainResults r
   join othertable t
     on t.whatever = r.whatever;
   

   I've seen cases where this second approach has made a HUGE difference.