Which is more efficient or does it matter?

Question

Which is more efficient or does it matter?

Viewing 13 posts - 16 through 27 (of 27 total)

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

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 1

I think my issue with doing it the "interesting" way as opposed to using JOINs is that, while this time, the performance and plan is likely to be identical, far too often, it might not be. The JOIN methodology is going to be more consistently right than the other. It's the consistency I like.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

Bob Hovious (4/11/2009)
Hey... who are you callin' an oxymoron!? :unsure:

Heh... so you know that guy, too, huh? 😀

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

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 3

What a maroon... what a gulli-bull...

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 4

I'm late here, but I'll chime in with a vote for joins and readability.

Paul White SSC Guru Points: 150467 More actions · Answer 5

Hey everyone and Happy Chocolate Egg Day!

Even later...my 2c:

1. Voting for JOIN too, for all the good reasons already given

2. If the first 3 characters of the zip are to be used in this way on more than an ad-hoc basis, it might be sensible to break that data out into a separate column. Functions on columns are often an indication that there is a 'hidden' column

3. LIKE does indeed allow a seek instead of the scan necessary when LEFT is used, but that doesn't mean that that is the best possible solution (not that anyone said it was). Personally, I try to avoid merge interval operators where I can, and the odd report of problems using GetRangeThroughConvert (for example) also nags at me.

So yeah, in the example given, it seems that there is a hidden column which may need exposing and indexing, depending on the wider circumstances.

/Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 6

For what it's worth, JOIN is the standard we use too, for both readability and reliability.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

Paul White (4/11/2009)
2. If the first 3 characters of the zip are to be used in this way on more than an ad-hoc basis, it might be sensible to break that data out into a separate column. Functions on columns are often an indication that there is a 'hidden' column

Absolutely a great idea. A persisted computed indexed column would do very nicely here.

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

Paul White SSC Guru Points: 150467 More actions · Answer 8

Jeff Moden (4/11/2009)
...A persisted computed indexed column would do very nicely here.

Hey Jeff,

It would, but an ordinary column would be my first choice, mainly because persisted computed columns are so sensitive to SET options. Don't get me started on that 😉

/Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 9

Paul White (4/11/2009)
Jeff Moden (4/11/2009)
...A persisted computed indexed column would do very nicely here.
Hey Jeff,
It would, but an ordinary column would be my first choice, mainly because persisted computed columns are so sensitive to SET options. Don't get me started on that 😉
/Paul

Considering that zip codes don't change much, I'd probably have to agree with 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)

Paul White SSC Guru Points: 150467 More actions · Answer 10

Jeff,

You are so right to point out the data-consistency advantage of the computed column. I guess I could add a constraint or something to simulate that benefit, but it makes me wince slightly.

Y'know I would love nothing better than to use persisted columns, indexed views et al a lot more than I do - but there's just too much associated pain!!! 🙂

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 11

Paul White (4/12/2009)
Jeff,
You are so right to point out the data-consistency advantage of the computed column. I guess I could add a constraint or something to simulate that benefit, but it makes me wince slightly.
Y'know I would love nothing better than to use persisted columns, indexed views et al a lot more than I do - but there's just too much associated pain!!! 🙂
Paul

I'm right there with you on the pain associated with Indexed Views.

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

Vijaya Kadiyala SSCrazy Eights Points: 9119 More actions · Answer 12

It looks to me by reading all the different post, people are leaning towards to the JOIN..

Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers

kim.talley SSC-Addicted Points: 407 More actions · Answer 13

Wow - I was out of the office for a few days and this post went crazy!

Thanks for all the input. For now, I'll stick with the join syntax. I will also look into the possibility of adding a column for the 3-digit zip to eliminate the LEFT functionality.

Thanks again to everyone!

Kim