Under Appreciated Features

Question

Under Appreciated Features

Viewing 8 posts - 61 through 67 (of 67 total)

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

Paul White SSC Guru Points: 150457 More actions · Answer 1

Gianluca Sartori (12/22/2010)
Well, indeed my post was a bit abrupt...I have to admit I am a bit biased: some weeks ago I had to rewrite a lot of queries that developers wrote with APPLY. After I explained them what it was and what could be achieved, they started to think that APPLY was the magic wand that could solve virtually any problem, as obviously it is not.

It seems like you are still quite emotional about it, yes 😛

I'm not trying to teach you how it works, I'm sure you know better than me. However, when the optimizer is unable to convert it into a plain JOIN, APPLY is implemented with Nested Loops, that is generally evil on a big input.

For sure, nested loops are usually a great choice where the inputs are small (though the tables may be large) and the inner side can seek on an index. This is not news, and neither is it particularly difficult to find examples of hash or merge join that are performance disasters.

Moreover, when the above conversion fails, the optimizer also fails evaluating a better JOIN/filter order. Things that make the conversion fail are quite common (TOP and UNION, for instance), so you have to be careful. When I code a statement, I also try different syntaxes to see which one performs better. It shouldn't make any difference, but it does.

More often than not, the rewritten syntax has subtle semantic differences from the original query, so you're actually asking a different question, although the results may be the same on a given data set. (Also, bear in mind that TOP is not a relational operator).

That said, it is true that the optimizer does not explore every equivalent pattern - just the ones that are efficient to implement, of benefit to a broad range of queries, and frequently seen 'in the wild'.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

spaghettidba SSC Guru Points: 105712 More actions · Answer 2

SQLkiwi (12/22/2010)
It seems like you are still quite emotional about it, yes 😛

You're probably right, Paul.

-- Gianluca Sartori

Nakul Vachhrajani SSChampion Points: 10279 More actions · Answer 3

Hello, Andy!

In the new year, I have spun off a new blog series based on the discussion that members of the community have been having in response to your editorial.

The blog series starts off from Thursday, January 06, 2011, and the parent article link will be: http://beyondrelational.com/blogs/nakul/archive/2011/01/06/underappreciated-features-of-microsoft-sql-server.aspx

As I publish the various child articles, I will be updating the parent article with the respective links.

I hope that you and members of the community find the series a useful and favourable outcome of the editorial.

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com

Follow me on
Twitter: @sqltwins

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

Nakul Vachhrajani (12/31/2010)
Hello, Andy!
In the new year, I have spun off a new blog series based on the discussion that members of the community have been having in response to your editorial.
The blog series starts off from Thursday, January 06, 2011, and the parent article link will be: http://beyondrelational.com/blogs/nakul/archive/2011/01/06/underappreciated-features-of-microsoft-sql-server.aspx
As I publish the various child articles, I will be updating the parent article with the respective links.
I hope that you and members of the community find the series a useful and favourable outcome of the editorial.

Ummm... why blog about what's already been written? Shoot... why blog at all? Why not publish your articles on this site? You already have the potential of 1.3 million readers and, I have to tell you, I typically don't look at blogs especially for articles that require so much personal opinion. 😉

--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)
Intro to Tally Tables and Functions

dmoldovan SSCertifiable Points: 6334 More actions · Answer 5

Service Broker, Extended Events, Event Notifications and the audit feature seem to be underappreciated. I do not know many people using them.

dmoldovan SSCertifiable Points: 6334 More actions · Answer 6

dmoldovan

SSCertifiable

Points: 6334

January 3, 2011 at 12:29 pm

#1268635

I've forgot about the "default trace"...

GSquared SSC Guru Points: 260824 More actions · Answer 7

dmoldovan (1/3/2011)
I've forgot about the "default trace"...

Definitely!

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

hardwaremister Mr or Mrs. 500 Points: 543 More actions · Answer 8

peterhe (12/17/2010)
In programing, window functions like ROW_NUMBER() is a real help.

AMEN!