WHERE...IN vs JOIN

Question

WHERE...IN vs JOIN

Viewing 3 posts - 16 through 17 (of 17 total)

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

Jeff Moden SSC Guru Points: 1003913 More actions · Answer 1

Mad Myche (12/21/2010)
Thank you for the great information. I created the Tally Table (mine took 93,516 ms) and created that function. The performance gain was on the order of ~350%. I then trimmed the tally table down to 8000 and that gave me an extra 100% performance.
Running the 2 versions back to back placed 82% of the query cost on the Loop method, and the remaining 18% on the Tally method. This was consistent across parameters varying from 25 characters out to 7950.

93 seconds to create an 11000 row Tally Table? Are you sure the time you've captured was in "ms" and not "ns"? If the answer is "Yes", then there's something seriously wrong with your machine because on every machine I've created a Tally Table on, it's only taken several milliseconds. I can usually create a million row Tally Table in just a couple of seconds using the code I've provided.

Also, trimming the Tally Table down from any number to only 8000 should not make a difference in performance because it should be doing an Index Seek followed by a highly qualified internal scan. I'm not sure what's going on but these things should not be happening.

Anyway, a 350% improvement is nothing to sneeze at. Thanks for the feedback on this.

--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)

Mad Myche SSCommitted Points: 1593 More actions · Answer 2

The code you have in this thread is averaging 210ms. NS was not a DateDiff option in Sql2000 and it does not appear to have been in 2005 either according to MSDN

The tally-table was created using the code in that provided link, not the one from this thread.

Director of Transmogrification Services

Jeff Moden SSC Guru Points: 1003913 More actions · Answer 3

Mad Myche (12/22/2010)
The code you have in this thread is averaging 210ms. NS was not a DateDiff option in Sql2000 and it does not appear to have been in 2005 either according to MSDN
The tally-table was created using the code in that provided link, not the one from this thread.

My bad. I thought you were using SQL Profiler and when you read the actual profiler table, it is rated in NS.

Again, thank you very much for the feedback. I really appreciate it.

--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)