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

Question

Post reply

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

nitin.varshney

SSC Eights!

Points: 948
More actions
October 12, 2012 at 5:54 am

#265621

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.

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

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

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 1

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

-- Mahesh[/font]

MH-09-AM-8694

joeroshan SSChampion Points: 10377 More actions · Answer 2

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: 186127 More actions · Answer 3

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

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)

francislouispearson SSC Enthusiast Points: 198 More actions · Answer 5

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: 1004339 More actions · Answer 6

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)

francislouispearson SSC Enthusiast Points: 198 More actions · Answer 7

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.