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

  • nitin.varshney

    SSC Eights!

    Points: 948

    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.

  • Mahesh Bote

    SSCrazy Eights

    Points: 9107

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

    -- Mahesh[/font]

    MH-09-AM-8694

  • joeroshan

    SSChampion

    Points: 10377

    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]

  • ChrisM@Work

    SSC Guru

    Points: 186043

    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.

    [font="Arial"]β€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


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

  • Jeff Moden

    SSC Guru

    Points: 994284

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. πŸ˜‰

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

  • francislouispearson

    SSC Enthusiast

    Points: 198

    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.

  • Jeff Moden

    SSC Guru

    Points: 994284

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. πŸ˜‰

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

  • francislouispearson

    SSC Enthusiast

    Points: 198

    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 8 (of 8 total)

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