0

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 >= 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 >= 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.

7
  • 2
    Upload the query plans to brentozar.com/pastetheplan and link them here. – Tony Hinkle Feb 15 '19 at 20:15
  • 1
    Without more info, the question is too broad, unanswerable. Datatypes, table structure, available indexes, table sizes, server (memory/disk) settings, all play role in the execution plans chosen and the response time. – ypercubeᵀᴹ Feb 15 '19 at 20:29
  • 1
    Plus the two queries are not logically equivalent (only similar), so any comparison has very little value. They would be equivalent if the join was a LEFT join. – ypercubeᵀᴹ Feb 15 '19 at 20:32
  • 2
    The question is basically "I have a red car that runs slower than a green one. Why?" We have to see the engine, the tyres and many other elements to know why that happens. While Ferraris are fast (and usually red), not all red cars are Ferraris. – ypercubeᵀᴹ Feb 15 '19 at 20:36
  • 2
    The question is not bad by the way. It's just missing essential information. Please edit and we can reopen (and see the advice above, about posting execution plans at the brentozar site). – ypercubeᵀᴹ Feb 15 '19 at 20:41
4

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

enter image description here

Whole plan

enter image description here

Index Spool properties.

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

The derived table

The 'better' plan

enter image description here

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

enter image description here

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

enter image description here

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

The 'bad' query, not so much

enter image description here

Executing the join before executing GROUP BY & 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  <= 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
2
  • — Thank you, this is exactly what we needed. – UnLogicGuys Feb 19 '19 at 16:14
  • @UnLogicGuys no problem man, I might have made some mistakes in certain parts so don't hold me to it ;). Glad it helped – Randi Vertongen Feb 19 '19 at 17:15

Not the answer you're looking for? Browse other questions tagged or ask your own question.