SET vs SELECT

Question

SET vs SELECT

Viewing 15 posts - 46 through 60 (of 82 total)

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

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

John Esraelo (5/8/2009)
1> code portability, because of the systems and software shelf-life and change in technology

I don't believe in the myth of code portability. Consider first that even the ANSI standards have been changing. Sure, they've been changing for the better, but they still change, they're kind of "blah", and not all RDBMS's have the ANSI standards fully or properly deployed.

That brings me to the next point... how many of you buy scientific calculators and then use only the "standard" functions like addition and subtraction? Probably not many. For me, it's the same with the SQL extensions whether it be in SQL Server or Oracle... the non-ANSI (and, therefore, non-portable) stuff is just too powerful to not use.

2> ease of reading the top-down designs and various different application codes.

I think that's absolutely spot on. I don't see how the use of either SET or SELECT will interfere with that but, as you said, the performance differences are so small that, unless deployed against millions of rows (like maybe in a UDF), it really shouldn't matter which you use although you should pick one (like you did) to standardize on.

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

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

RBarryYoung (5/8/2009)
*sigh* I'm so misunderstood. 🙁

Heh... it's the helmet, Barry... can't see your eyes. 😛

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

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

Paul White (5/8/2009)
John,
When assigning multiple variables from constants, SELECT is significantly quicker than separate SET statements.
Just adding that for completeness.
Paul

Heh... I agree, but where's the code to prove 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 4

Jeff Moden (5/8/2009)
Paul White (5/8/2009)
When assigning multiple variables from constants, SELECT is significantly quicker than separate SET statements.
Heh... I agree, but where's the code to prove that? 😉

And there I was, thinking I had gotten away with it.

Ok - the SELECT is 59% faster on this million-assignment test:

http://www.sqlmag.com/Article/ArticleID/94555/sql_server_94555.html

BTW - It is good to see growing acceptance of the idea that ANSI standards are kinda 'blah'. I couldn't agree more 🙂

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

John Esraelo-498130 SSCertifiable Points: 5914 More actions · Answer 5

have not we have had kicked this dead horse plenty..

I think we did..

:))

Cheers,
John Esraelo

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

Florian Reischl (5/8/2009)
I think I misunderstood your code and the intention. This is a really nice deal if you want to be sure that there is no other value. My thought was much more simple. To ensure that you get only one (the first value of a SELECT) I just would suggest a TOP. My mistake.

No worries - and stop apologizing! (Please do not apologize for apologizing)

Yep TOP will work, but might be hiding a subtle error - if the code relies on a single value existing, bad things might happen further down the track. If it is important which of the rows goes into the variable, an ORDER BY is required as well as the TOP as I'm sure you know.

The things I like about the ORDER BY with divide-by-zero idea:

It raises an error if more than one row is returned
It can be added to any assignment using copy and paste
It uses a windowed function in the order by clause only - cool!

Question: Am I the only one that finds the TOP syntax a bit strange-looking sometimes?

Paul

[font="Courier New"]

DECLARE@i INTEGER;

DECLARE@table TABLE (number INTEGER PRIMARY KEY);

INSERT@table SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3;

SELECTTOP (1) @i = number FROM @table ORDER BY number DESC;

SELECT@i

[/font]

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

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

John Esraelo (5/9/2009)
have not we have had kicked this dead horse plenty..
I think we did..
:))

Hey John,

Probably, yes.

If it's ok with you though, I'd like to carry on a while longer, as I am enjoying the 'banter'.

Let me know if that causes you a problem, and I will stop immediately, of course.

😛

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 8

Hi Paul

Paul White (5/9/2009)
Florian Reischl (5/8/2009)
I think I misunderstood your code and the intention. This is a really nice deal if you want to be sure that there is no other value. My thought was much more simple. To ensure that you get only one (the first value of a SELECT) I just would suggest a TOP. My mistake.
No worries - and stop apologizing! (Please do not apologize for apologizing)

Did not notice that I apologized for anything :-D. Maybe the last sentence sounds like... I wrote that I misunderstood you and that's correct.

Question: Am I the only one that finds the TOP syntax a bit strange-looking sometimes?

Yep, from my side. I like this syntax but it depends on the formatting.

Greets

Flo

John Esraelo-498130 SSCertifiable Points: 5914 More actions · Answer 9

hehe of course you can continue..

enjoy

:hehe:

Cheers,
John Esraelo

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

Florian Reischl (5/9/2009)
Question: Am I the only one that finds the TOP syntax a bit strange-looking sometimes?
Yep, from my side. I like this syntax but it depends on the formatting.

How about:

[font="Courier New"]SELECT DISTINCT TOP (1) @i = number FROM @table ORDER BY number DESC;[/font]

...that makes by brain hurt. :laugh:

Distinct Top 1 ??? What?

I also like TOP - I just find it looks odd in some queries.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 11

Paul White (5/9/2009)
Florian Reischl (5/9/2009)
Question: Am I the only one that finds the TOP syntax a bit strange-looking sometimes?
Yep, from my side. I like this syntax but it depends on the formatting.
How about:
[font="Courier New"]SELECT DISTINCT TOP (1) @i = number FROM @table ORDER BY number DESC;[/font]
...that makes by brain hurt. :laugh:
Distinct Top 1 ??? What?
I also like TOP - I just find it looks odd in some queries.
Paul

Paul, you forgot something!

[font="Courier New"]SELECT DISTINCT TOP (1) @i = number FROM @table GROUP BY number ORDER BY number DESC;[/font]

😀

Greets

Flo

PS: For the other thing please have a look to "The Thread"

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

Paul White (5/9/2009)
Jeff Moden (5/8/2009)
Paul White (5/8/2009)
When assigning multiple variables from constants, SELECT is significantly quicker than separate SET statements.
Heh... I agree, but where's the code to prove that? 😉
And there I was, thinking I had gotten away with it.
Ok - the SELECT is 59% faster on this million-assignment test:
http://www.sqlmag.com/Article/ArticleID/94555/sql_server_94555.html

Heh... that's not much better. The author is still missing the code that supposedly proves it all. I wouldn't even take my word for something like that without code that proves it.

And, anyone that uses an explicit loop on something like this is only concerned with GUI performance and not batch or report performance. :hehe:

BTW - It is good to see growing acceptance of the idea that ANSI standards are kinda 'blah'. I couldn't agree more 🙂

Man, I agree with that. Now, if we could just get people over the idea of try for code portability on the backend. 😀

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

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

John Esraelo (5/9/2009)
have not we have had kicked this dead horse plenty..
I think we did..
:))

The bones of these types of dead animals are interesting... its especially interesting to see how many people think they know how the animal works just by kicking 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)

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 14

Jeff Moden (5/9/2009)
BTW - It is good to see growing acceptance of the idea that ANSI standards are kinda 'blah'. I couldn't agree more 🙂
Man, I agree with that. Now, if we could just get people over the idea of try for code portability on the backend. 😀

Do you work at my company? Never saw you guys. Do you have pseudonyms? They even try to keep base libraries portable... We are not allowed to use LINQ because it is not available in Java. :crazy:

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

Florian Reischl (5/9/2009)
Jeff Moden (5/9/2009)
BTW - It is good to see growing acceptance of the idea that ANSI standards are kinda 'blah'. I couldn't agree more 🙂
Man, I agree with that. Now, if we could just get people over the idea of try for code portability on the backend. 😀
Do you work at my company? Never saw you guys. Do you have pseudonyms? They even try to keep base libraries portable... We are not allowed to use LINQ because it is not available in Java. :crazy:

Heh... I can't tell if you're agreeing with us or not. I won't speak for Paul but, to clarify my position... I'm definitely NOT a believer in the myth of portable code (in so far as SQL goes) or using only ANSI code. "Get people over the idea" means that I want to try to stop people from believing that they should forsake the power available in any given database engine just to try to make portable code.

I do, however, believe in encapsulation and I believe THAT makes things "more portable". Probably a bad example, but let's take the humble concatenation function as an example. As you know, SQL Server will allow for a variable to be "overlayed" in a single SELECT... Oracle will not. If the DB folks have done their job correctly, there will be one function each for SQL Server and Oracle in the library and each will use the "best" methods available in the given database engine for both performance and scalability. The front end folks should only have to worry about calling the function, not how it works.

On the other hand, trying to make such a concatenation function work in both SQL Server and Oracle using only ANSI code might be possible, but it's going to be a lot slower on one than on the other. With that in mind, some would say that the concatenation function should actually be a part of the GUI code and, depending on the circumstances, I might agree. But, what if there is no GUI? What if this is for file processing? 😉

So far as something like not using LINQ because it doesn't work with Java goes, that's a pretty important decision for the company one way or another. One might argue that the company has settled on Java as a programming standard and that it's easier to find developers that know just Java than it is to find developers who know both Java and LINQ. There must be a dozen or more major languages and hundreds of minor "tool" languages to write GUI's and other types of non-database-centric code with. To prevent the proverbial "tower of Babel", some companies pick a standard and stick with it just so they know they can find replacement developers that can come up to speed quickly. It's not always the best short term solution, but it usually works out in the long run.

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