Different manifestations of the same logic yield very different execution times Assume that the widgets_population table records every widget which has rolled off the assembly line, while the widgets_tested table is the subset that have been tested.

For every widget in the population, we want to determine the most recent test.

The two queries below show two different approaches to the solution.

However, the first query runs like lightning, whereas the second query seems to spin forever.  Why?  Is there something obviously inferior about the second query, or is this just one of those optimizer flukes?

We realize that having an inequality in the join predicate is not optimal because it can't take advantage of pre-ordering and merging, but it seems to us that the correlated subquery is just as taxing because it has to run the inner query for every row of the outer query.  So, to our minds, both queries should be equally inefficient.

SELECT   A.serial_ID,
         (SELECT MAX(B.serial_ID) FROM widgets_tested AS B 
             WHERE A.serial_ID &gt;= B.serial_ID) AS [ID of most recent test]
FROM     widgets_population AS A
;




SELECT   A.serial_ID,
         MAX(B.serial_ID) AS [ID of most recent test]
FROM     widgets_population AS A
         INNER JOIN
         widgets_tested AS B
         ON A.serial_ID &gt;= B.serial_ID
GROUP BY A.serial_ID
;




Note:  It may not be possible to give more information about the DDL statements of the relevant tables and execution plans because the widgets tables are just sanitized stand-ins for the original tables. We just wanted to ask the community if there is something obviously wacky or inefficient about the logical structure of the second query.
Since you are not allowed to share any more information on the queries such as sample data + table definition, these tests will be on own fabricated data and with or without indexes. Test data below.

YMMV.

The inner join + group by query

The 'bad' query puts everything in an index spool, and subsequentially executes a nested loop join on the exponential 8M rows



Whole plan



Index Spool properties.

This index spool creates over 8M records as a result of the &lt;= join. 
The nested loop operator over 8M rows into a stream aggregate will also decrease performance.

The derived table

The 'better' plan



The stream aggregate is used before the nested loop operator is used. Resulting in a much smaller join.
There is also a difference in stream agg operators, this one supports the MAX() function while the other one (above) supports the GROUP BY statement + The partialagg of MAX().

TL;DR

My guess is that the huge nested loops operator used in the plan of the inner join query factors in for the excessive time. 
The difference with the good plan being that the Stream aggregate happens before the nested loops operator.
Also as @ypercubeᵀᴹ mentioned, they are not logically similar.



Adding indexes

CREATE INDEX IX_widgets_population_serial_ID on widgets_population(serial_ID);
CREATE INDEX IX_widgets_tested_serial_ID on widgets_tested(serial_ID);


The 'good' query gets improves a lot after an index, using 15ms cpu time



The reason that this change is so drastic, is that the TOP operator is used now.



This top operator is getting the first value that matches the &lt;= condition by using the index's sort order, for each value in widgets_tested.

The 'bad' query, not so much



Executing the join before executing GROUP BY &amp; MAX(), where the MAX() is calculated before the join in the good plan.



Data used for this example

CREATE TABLE  widgets_population(serial_ID int)

CREATE TABLE  widgets_tested(serial_ID int)

SET NOCOUNT ON
DECLARE @I INT = 1;
WHILE @I  &lt;= 1000
BEGIN
INSERT INTO  widgets_population(serial_ID)
VALUES(@I)
INSERT INTO  widgets_tested(serial_ID)
VALUES(@I)
SET @I+=1
END

INSERT INTO  widgets_population(serial_ID)
select serial_ID+1000 from widgets_population

INSERT INTO  widgets_tested(serial_ID)
select serial_ID+1000 from widgets_tested

INSERT INTO  widgets_population(serial_ID)
select serial_ID+2000 from widgets_population

INSERT INTO  widgets_tested(serial_ID)
select serial_ID+2000 from widgets_tested