Mentor

Question

Mentor

Viewing 10 posts - 46 through 54 (of 54 total)

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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

scott.pletcher (6/15/2010)
Jeff, you can over-engineer, I'll give you that. Huge amounts of wasted I/O to no purpose. But you feel good about it, and obviously for you that's all that counts.

Scott, Joe Celko may be one of the best SQL pro (and have whatever number of gold medals or eggs). However his "characteristic for good SQL programming: do as much work as you can in ONE statement, so the optimizer can have as much information as possible." is bluntly wrong! There are plenty of cases where number of I/O can be reduced by splitting the statement and introducing temp or work tables, especially it would be quite applicable for working with large datasets.

I worked with SQL datawarehouse database (one of MS case-study projects) which was designed to store upto 7TB of data (processing data and growing upto 4GB daily!). I can tell you for sure, we had one of the best MS SQL guys to advise us about the advanced tecniques of query optimisation and quite often, breaking single query and introducing work table for intermidiate results did significantly reduce processing time (mostly due to reducing number of I/O's).

You can still enjoy your challenges to get everything into one line select/insert/update/delete, but you cannot claim that is the best practice even if some known men told you so in some of his article (even God makes mistakes - why he didn't stop after creating just animals? :-D). From practical experience, every SQL pro knows, that nothing in SQL is "always do like this", instead it is "ALWAYS THINK how to do this best in this particular case AND TEST IT !"

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

scott.pletcher (6/15/2010)
Jeff, you can over-engineer, I'll give you that. Huge amounts of wasted I/O to no purpose. But you feel good about it, and obviously for you that's all that counts.

This gratuitous willy-waving is becoming tiresome. Please desist.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

scott.pletcher (6/15/2010)
Jeff, you can over-engineer, I'll give you that. Huge amounts of wasted I/O to no purpose. But you feel good about it, and obviously for you that's all that counts.

Scott, SSC is first of all a community of individuals that want to share their thoughts and their knowledge in a professional way. Being professional involves also being able to avoid insulting other members or starting a silly fight on a public forum.

To become an MVP you must have contributed to the community a lot.

I guess you did that elsewhere, since you only have 64 points on SSC at the time I'm writing.

Since you're showing up your inability to adhere to the bare minimum rules of politeness, why don't you just keep on posting on the sites where you're coming from and leave us alone? I'm sure we can live without the arrogant attitude you kept so far.

You could also consider changing your attitude. That would really be a good deal for us, but first of all for you.

-- Gianluca Sartori

scott.pletcher SSCertifiable Points: 5958 More actions · Answer 4

So, even if the original requirements include the following:

1) use one statement only, without subquery(ies)

2) this is not a design q; it is not intended to be a design q; it was not asked as a design q.

You think it's ok to ridicule the one stating the q?

For the record, as I stated before, this was indeed a real situation.

Yes, I suppose I could have told the woman: well, I can't write one quick statement like you've asked because I need to completely modify everything you've done before, even though you want a quick answer now for an immediate reason. The dbms wasn't even SQL Server.

Scott Pletcher, SQL Server MVP 2008-2010

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

The dbms wasn't even SQL Server.

So it is relevant here how? Respectfully, you should just let this go ... you're making a really poor impression. I've tried to listen impartially, but you seem to want to prolong the argument long after everyone else has turned their backs on it. To be the last one standing around talking doesn't mean you are a winner. Or maybe it does[/url].

By the way, there are quite a few MVPs who frequent SSC. I've had the pleasure to meet a few of them personally. None of the others feel compelled to advertise that fact in their signature line. Perhaps they know that those who feel compelled to demand respect are usually the least deserving of it, or perhaps they just have enough self-confidence and humility to not need it.

Statements get challenged around here. (I've taken enough lumps to know.) But it isn't a personal thing. Just knowledgeable people disagreeing and sometimes learning from each other. If what you are saying makes sense, some people will listen, but you may have already lost your audience in this particular forum.

(Stepping down off portable soapbox, packing it up, and taking it to the house.)

__________________________________________________

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

tfifield SSCrazy Eights Points: 9655 More actions · Answer 6

I completely agree with Jeff on this one. Divide and conquer techniques should be part of any T-SQL curriculum. I do it all the time in stored procedures and it greatly improves performance.

About a year ago I tried an experiment with running totals in the AdventureWorks database. I tried using an OUTER APPLY (triangular join) to get the running total of sales for each customer by sales order. I created a DB view and it was, of course, 1 statement. If I just did a few customers it seemed to work just fine. When I did the whole table it didn't start returning records for 30 seconds and still kept on running for more than 5 minutes - I had to kill it.

With Jeff's quirky update, which is a divide and conquer type technique the way I use it, the whole thing took just under 2 seconds.

Putting as much SQL as possible into single statements is only a guideline to give the optimizer more data to work with - in practice it doesn't actually work very well. I always try things both ways (big monster complex queries vs. several much simpler steps) and usually divide and conquer wins out.

Todd Fifield

LutzM SSC Guru Points: 107049 More actions · Answer 7

Unfortunately, the original question got out of focus in most of the current posts.

So, may I kindly ask to focus on the subject?

Whoever is willing to continue the "challenge discussion" or any other semi- or even unrelated discussion: please do so in a separate thread. Currently, there's the impression of a hijacked thread.

Thank you.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Ben Teraberry SSChampion Points: 11845 More actions · Answer 8

IMO, best way to get a good SQL mentor is by way of a job at the right company. The challenge is finding that right company and then beating out the other candidates to land the job.

The problem with trying to find someone on the net is that as a junior SQL person you're fairly likely to get sucked in by a pretender who talks a big game and makes up credentials that they don't have. You're better off keeping a more objective view and watch the dialog, test things and figure out yourself what the truth really is.

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

David Webb-CDS SSCoach Points: 17398 More actions · Answer 9

There are a lot of very good mentors here and most of them, when they have the time, go out of their way to ensure that the solutions they provide, or hints they drop, lead to an opportunity to learn.

The posting here don't really have to be killer problems. It's fine to just say "I had this problem at work today. Isolved it this way. Is there a better way?"

The collective brain power here is far greater than you'd have access to by just following one excellent DBA around.

I've learned far more here than I would have ever picked from any one person or any one job.

The other resource I'd recommend, if you learn more effectively with human interaction, is your local user group. Lots of smart, experienced people tend to show up at those meetings.

And then again, I might be wrong ...
David Webb

Tony++ SSCarpal Tunnel Points: 4421 More actions · Answer 10

One thing on a mentor, it's great to find an SQL Server pro as a mentor, but I'd say that it doesn't have to be strictly SQL Server - take experience where you can find it. One of my greatest mentors was/is a Sybase DBA - the focus becomes good ideas & design & architecture, rather than the details of the platform. The platform can evolve to cover up bad designs, but good design lasts longer.

In turn I've mentored non-DBA programmers at work -- giving them a perspective from the DB side only helps them write better code (and, in selfish interest, gives me better SQL being submitted!:-D )