Jeff Moden

Forum Replies Created

Viewing 15 posts - 45,871 through 45,885 (of 59,095 total)

RE: The "Numbers" or "Tally" Table: What it is and how it replaces a loop.

Very cool and great explanation of the problem you were trying to solve. Thanks a huge amount for taking the time to post it, Julie! 🙂
--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)

January 16, 2009 at 12:27 pm

#928442
RE: Which SQL would run faster?

GilaMonster (1/16/2009)
Jeff Moden (1/16/2009)
I'm trying to be as nice as pie... I don't understand why people post code and ask which will be faster... that's pretty lazy...
Cause it's easier...
--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)

January 16, 2009 at 12:22 pm

#928437
RE: Which SQL would run faster?

leonp (1/16/2009)
ok ok ..... spare me the barrage, smart, sweet people ..... you were right .... 1st and 2nd were quicker than 3rd and in fact 1st was quicker...
--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)

January 16, 2009 at 12:17 pm

#928428
RE: Which SQL would run faster?

leonp (1/16/2009)
alrite ... first thanks for 'all the guidance' ....
second .. while i admit to being a little lazy, I do not have access to the database itself that...
--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)

January 16, 2009 at 12:13 pm

#928425
RE: Hidden RBAR: Triangular Joins

magarity kerns (8/15/2008)
By the most amazing coincidence, we have just discovered a developer's query is running out of tempdb space because of the following as his join clause:
where
a.acct_nbr >...
--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)

January 16, 2009 at 12:02 pm

#928413
RE: Hidden RBAR: Triangular Joins

leon mckay (1/16/2009)
Jeff Moden (1/16/2009)
The article was actually first published on 12/6/2007 and it actually says that on the SSC home page where the article was listed. Why they...
--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)

January 16, 2009 at 11:55 am

#928405
RE: Hidden RBAR: Triangular Joins

Dan Guzman (1/16/2009)
I'm confused. The article states it was published today 1/16/2009, yet this discussion was started 12/6/2007! Even the first 9 pages of responses are from 2007....
--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)

January 16, 2009 at 10:55 am

#928354
RE: Hidden RBAR: Triangular Joins

sknox (1/16/2009)
Back to the subject, this is a good article. It explains to me exactly why a few queries I've written take so long to run, and I look forward...
--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)

January 16, 2009 at 10:52 am

#928348
RE: Hidden RBAR: Triangular Joins

sfrazier (1/16/2009)
OK I am a simplistic TSQL programmer - I appreciated the article. I am working on some code in a payroll extract project and have run squarely into...
--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)

January 16, 2009 at 10:49 am

#928345
RE: Hidden RBAR: Triangular Joins

TheSQLGuru (1/16/2009)
Jeff:
1) For your follow-on articles, when you demonstrate the 'tsql trick' solution, please make sure you caveat the hell out of it about how and why you can get...
--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)

January 16, 2009 at 10:42 am

#928340
RE: Update Performance

One additional thought... people always seem to fall into the trap of saying "this will never be for more than 1 row". You don't actually know how someone else...
--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)

January 16, 2009 at 10:18 am

#928326
RE: Update Performance

rob jones (1/16/2009)
Currently i have 'set nocount on'.
Will i have to remove that in the "Update, and if @@Rowcount = 0, then insert. " method so it returns a rowcount?
If...
--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)

January 16, 2009 at 10:14 am

#928318
RE: The "Numbers" or "Tally" Table: What it is and how it replaces a loop.

Outstanding. Thanks for taking the time to post your great feedback, Julie... it would be very interesting to see your code along with brief explanation if that's possible and...
--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)

January 16, 2009 at 10:12 am

#928315
RE: Top N sub query workaround - Does it exist?

krypto69 (1/16/2009)
Hi everyone, thanks again for all your help.
I tried Ramesh's suggestion first, but to my surprise, it was only slightly quicker.
Was then going to try Chris Morris's suggestion but...
--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)

January 16, 2009 at 10:02 am

#928306
RE: Top N sub query workaround - Does it exist?

GilaMonster (1/16/2009)
Jeff Moden (1/16/2009)
GilaMonster (1/16/2009)
If the comparison with the outer query is an equality, the subquery won't be processed row by row.
I pretty sure it will with an ORDER BY...
Order...
--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)

January 16, 2009 at 8:55 am

#928240

Viewing 15 posts - 45,871 through 45,885 (of 59,095 total)