Cross join yields execution plan that reads one of the tables multiple times












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.






sql-server







share|improve this question

























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

    – Luís Gabriel de Andrade
    7 hours ago


















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.






sql-server







share|improve this question

























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

    – Luís Gabriel de Andrade
    7 hours ago
















3












3








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.






sql-server







share|improve this question
















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.






sql-server




sql-server






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited 6 hours ago









Erik Darling

21.5k1264104




21.5k1264104










asked 7 hours ago









Luís Gabriel de AndradeLuís Gabriel de Andrade

1026




1026













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

    – Luís Gabriel de Andrade
    7 hours ago





















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

    – Luís Gabriel de Andrade
    7 hours ago



















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.

– Luís Gabriel de Andrade
7 hours ago







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.

– Luís Gabriel de Andrade
7 hours ago












2 Answers
2






active

oldest

votes


















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.






share|improve this answer

































    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






    share|improve this answer



















    • 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
      7 hours ago











    Your Answer








    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "182"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: false,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: null,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














    draft saved

    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f229070%2fcross-join-yields-execution-plan-that-reads-one-of-the-tables-multiple-times%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    2 Answers
    2






    active

    oldest

    votes








    2 Answers
    2






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    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.






    share|improve this answer






























      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.






      share|improve this answer




























        6












        6








        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.






        share|improve this answer
















        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.







        share|improve this answer














        share|improve this answer



        share|improve this answer








        edited 6 hours ago









        jadarnel27

        4,96411736




        4,96411736










        answered 7 hours ago









        Erik DarlingErik Darling

        21.5k1264104




        21.5k1264104

























            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






            share|improve this answer



















            • 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
              7 hours ago
















            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






            share|improve this answer



















            • 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
              7 hours ago














            4












            4








            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






            share|improve this answer













            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







            share|improve this answer












            share|improve this answer



            share|improve this answer










            answered 7 hours ago









            Bob KlimesBob Klimes

            2,1401022




            2,1401022








            • 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
              7 hours ago














            • 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
              7 hours ago








            1




            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
            7 hours ago





            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
            7 hours ago


















            draft saved

            draft discarded




















































            Thanks for contributing an answer to Database Administrators Stack Exchange!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid …



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f229070%2fcross-join-yields-execution-plan-that-reads-one-of-the-tables-multiple-times%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            -

            Popular posts from this blog

            what is the purpose of having a “thru cal” on RF PCB?

            Image
            3 1 $begingroup$ I often find a thru cal on RF PCB like picture below. What is the purpose of having this thru cal thing on the board? One purpose I can think of is to test whether the designed transmission line is truly 50 ohms over the frequency of interest. I did some research online and there are some people saying that the purpose of this thru cal is for "through-reflect-line" (TRL) testing. But I was not convinced with this argument since TRL technically needs other two lines (reflect and line). Can anyone explain this to me from their experience? pcb rf share | improve this question asked 2 hours ago ...
            Read more

            What does Gandalf whisper to the Moth on the Orthanc in Isengard?

            Image
            2 After the duel between Saruman and Gandalf in which Gandalf is bested, he is sent to stay on the pinnacle of Orthanc. Gandalf then whispers a message to a moth that happens to appear. I know that the message involves the calling of an eagle, for that becomes obvious ten minutes later. However, what exactly does Gandalf say to the Moth in the Elvish tongue? tolkiens-legendarium the-lord-of-the-rings the-fellowship-of-the-ring share | improve this question edited 2 hours ago Mat Cauthon 17k 4 84 134 asked 4 hours ago...
            Read more

            magento2 creating a lot of catalogrule_product_temp tables

            Image
            0 Magento2.2.5 based multisite (multi store view)seems to be creating a lot of temporary tables such as catalogrule_product__temp9a310576 catalogrule_product__temp998786f4 etc... However I am not sure why these tables are being created they all seem to be empty? database magento2.2.2 share | improve this question asked Dec 14 '18 at 19:55 Jayreis Jayreis 173 7 31 add a comment  |  ...
            Read more