3

I have a query where I need to cross join a small table with 5 rows to a table with roughly 31k rows. The execution plan SQL Server came up with does index scans in both tables and joins them with Nested Loops (INNER JOIN). However, when checking the execution plan, I noticed that the scan on the smaller table produced 5 x 31k = 155k rows for the following step. Does that mean SQL Server is scanning the index of the smaller table 31k times?

Here is a small reproduction of the problem I'm having:

CREATE TABLE A (id INT PRIMARY KEY);

CREATE TABLE B (id INT PRIMARY KEY);

INSERT INTO A(id)
    VALUES (1), (2), (3), (4), (5);

INSERT INTO B(id)
    VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9)


SELECT * FROM A CROSS JOIN B

Here is the query live statistics of the SELECT command Here is the query live statistics of the <code>SELECT</code> command

My question is whether the index scan on the smaller table is performing multiple I/O operations, one for each row on the bigger table, as opposed to doing it only once and having its result being fed to the following step only once.

6

My question is whether the index scan on the smaller table is performing multiple I/O operations, one for each row on the bigger table, as opposed to doing it only once and having its result being fed to the following step only once.

Yes, for every row that comes out of A, you need to produce all of the rows for B. Since there are five rows in A, you make five trips to B.

Cross Joins can only be implemented with Nested Loops, and that's normal behavior for it.

4

Yes, a nested loop join will run the bottom (inner) input for each row in the outer input. enter image description here

Using your same examples data you can change the query to be a full outer join without a join condition and (on my machine) the result is a plan with a merge join.

SELECT * FROM A full outer  JOIN B
on 1=1

enter image description here

  • 1
    It is dubious that the merge join is an improvement though. It will be a many to many merge that continually replays the rows cached in a work table. – Martin Smith Feb 6 at 19:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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