which is better to user the cross join or while loop ?

  • Hi

    Please tell me which is better for the performance impact ?

    1. Insert into #temp table Using the cross join with tables.

    2. Insert into #temp table using the while loop to repeat the same data no of times.

  • [font="Verdana"]You can enable the Actual Execution plan and verify yourself

    -- Mahesh[/font]

    MH-09-AM-8694

  • You should test it for your particular requirement and decide. Vary with your table and index structure and the resources available.

    ----------------------------------------------------------------------------------------------------------------------------------------------------
    Roshan Joe

    Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • nitin.varshney (10/12/2012)


    Hi

    Please tell me which is better for the performance impact ?

    1. Insert into #temp table Using the cross join with tables.

    2. Insert into #temp table using the while loop to repeat the same data no of times.

    Post the two alternatives you have.

    โ€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.โ€ - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • nitin.varshney (10/12/2012)


    Hi

    Please tell me which is better for the performance impact ?

    1. Insert into #temp table Using the cross join with tables.

    2. Insert into #temp table using the while loop to repeat the same data no of times.

    Even if we told you the correct answer, you shouldn't just take someone's word for something especially in the internet. Setup a test and test it!

    I'll also tell you that neither of the two options you have are, depending on what I think you're doing, the absolute fastest. If you were to post one of your solutions for the above so I can see what you're actually doing, then one of us can show you the fastest solution.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I can't imagine a loop would ever be better. In my personal situations where I have used a cross join, if your dealing with at least many thousands of records the increase in performance over a loop is quite drastic.

  • ChrisM@Work (10/12/2012)


    nitin.varshney (10/12/2012)


    Hi

    Please tell me which is better for the performance impact ?

    1. Insert into #temp table Using the cross join with tables.

    2. Insert into #temp table using the while loop to repeat the same data no of times.

    Post the two alternatives you have.

    Jeff Moden (10/12/2012)


    nitin.varshney (10/12/2012)


    Hi

    Please tell me which is better for the performance impact ?

    1. Insert into #temp table Using the cross join with tables.

    2. Insert into #temp table using the while loop to repeat the same data no of times.

    Even if we told you the correct answer, you shouldn't just take someone's word for something especially in the internet. Setup a test and test it!

    I'll also tell you that neither of the two options you have are, depending on what I think you're doing, the absolute fastest. If you were to post one of your solutions for the above so I can see what you're actually doing, then one of us can show you the fastest solution.

    Heh... apparently the OP either lost track of this thread a very long time ago or didn't actually have an interest. ๐Ÿ˜‰

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • no doubt. I added the response because it didn't look like anyone said this and I thought it was pertinent for someone researching the question at some point in the future.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply