August 10, 2009 at 10:42 am
Andy DBA (8/10/2009)
GilaMonster (8/10/2009)
The equivalent query using exists is this:
SELECT * FROM employee WHERE NOT EXISTS (SELECT 1 FROM emp_detail where employee.emp_no = emp_detail.emp_no)That will probably run much the same speed as the IN (haven't tested)
I have some vague memory from long ago that older SQL Server versions (or perhaps it was other database engines?) performed an inefficient loop when the WHERE NOT IN technique was used. For that reason, I've gotten in the habit of using a LEFT JOIN query to find rows in one table with no match in another.
So just out of curiosity, I compared estimated execution plans of the WHERE NOT EXISTS, WHERE NOT IN, and LEFT JOIN techniques using tables in the ADVENTUREWORKS database (see below). The first two (WHERE NOT EXISTS and WHERE NOT IN) had identical plans, but my LEFT JOIN shows an added "filter" cost of 3% for the "WHERE column IS NULL. I did not do any benchmarking to see if the 3% was simply buried elsewhere in the other plans.
So my question is: Does anyone have strong recommendations (other than readability:-D) regarding LEFT JOIN versus either of the other two? Are there any circumstances in SQL Server where it might outperform them? Does it really take a 3% hit for the filter? (OK, 3 questions)
NOT IN:
SELECT * FROM Sales.Customer WHERE CustomerID NOT IN (SELECT CustomerID from Sales.CustomerAddress)
NOT EXISTS:
SELECT * FROM Sales.Customer C WHERE NOT EXISTS (SELECT 1 FROM Sales.CustomerAddress CA where C.CustomerID = CA.CustomerID)
LEFT JOIN:
SELECT * FROM Sales.Customer C LEFT JOIN Sales.CustomerAddress CA ON C.CustomerID = CA.CustomerID WHERE CA.CustomerID IS NULL
Andy - I think the left join and filtering nulls will generally outperform the other two - indicing will have some impact as well as table size
August 10, 2009 at 10:45 am
Tim Walker (8/10/2009)
Lynn Pettis (8/10/2009)
oli (8/10/2009)
Paul White (8/10/2009)
I deeply begrudge the minimum one star.Gail's first post expresses it best, and in milder
languageterms than I might have used. I would encourage everyone who reads the article to read it too.This article really needed some serious peer review before being submitted. It wouldn't be so bad if it didn't attempt to sound so authoritative! Please Arup, get some feedback from some of the hugely knowledgeable people on here before publishing - poor advice and dodgy 'tips' will do nothing to enhance your standing or that of SSC, sad to say.
/rant
Paul
Couldn't agree more. I thought SS Central had better editorial standards than this.
It really is up to the authors to seek out peer reviews of their articles before publication. Steve chooses to publish submitted articles with little interference, preferring that the authors voice be heard.
Lynn (and Kit):
The problem with your view is that with 8 pages of mostly adverse comments, nearly all of which I agree with, this article still has a four star rating as of now.
Therefore anybody who reads it next week, next month, or next year will think it's an authorititive article because it is highly rated. It gets highly rated because most people on the site now are not as expert as you are and will rate the article as good, because they believe its contents, PROBABLY partially because they think it is endorsed by SSC.
I think as the site gets bigger, and is read by more and more people, publishing inaccurate advice will become an increasing problem.
Plus I feel a bit sorry for the author, who presumably thought he was writing a good article and didn't set out to mislead. Remember, half of it is right!
It's a very public way to be told what you don't know on here ...
Tim
Thing is, it isn't my view. That is Steve's view and as the Editor of SSC his is the one that counts. I also support him in this area. Let the author have his voice. My first article on SSC was submitted with no review. It did okay. My second article I had Jeff review it first as it was based on his initial work. It too did fine. The next two articles I wrote I had published on sswug.org. I had them both reviewed prior to submission. I wasn't happy with the expousure they got there and hopefully more will be able to read them in the near future here on SSC.
The best thing authors can do is learn from what they have done in the past. Writing isn't easy, it takes time and effort. You only get better by doing and accepting constructive critism along the way. Some of the comments here are worthless, as they really don't help the author learn. Other comments are meant to help the author so that the next article is better.
August 10, 2009 at 10:47 am
I found many of the items in this article (as already divulged by Gail), to be somewhat inaccurately founded. That said, with proper documentation and good peer review, this article has some information that may be of use to better performing procs. My concerns are that people need to take the tips, and then research them before treating them as authoritative.
I was greatly disappointed in the order that the list is presented and that no mention of testing queries against your dev environment, or that the execution plans must be evaluated.
I wouldn't rate this article as a 5, and certainly not a 1. I would rate this maybe a 2 or 3 though.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 10, 2009 at 10:55 am
Lynn Pettis (8/10/2009)
Tim Walker (8/10/2009)
Lynn Pettis (8/10/2009)
oli (8/10/2009)
Paul White (8/10/2009)
I deeply begrudge the minimum one star.Gail's first post expresses it best, and in milder
languageterms than I might have used. I would encourage everyone who reads the article to read it too.This article really needed some serious peer review before being submitted. It wouldn't be so bad if it didn't attempt to sound so authoritative! Please Arup, get some feedback from some of the hugely knowledgeable people on here before publishing - poor advice and dodgy 'tips' will do nothing to enhance your standing or that of SSC, sad to say.
/rant
Paul
Couldn't agree more. I thought SS Central had better editorial standards than this.
It really is up to the authors to seek out peer reviews of their articles before publication. Steve chooses to publish submitted articles with little interference, preferring that the authors voice be heard.
Lynn (and Kit):
The problem with your view is that with 8 pages of mostly adverse comments, nearly all of which I agree with, this article still has a four star rating as of now.
Therefore anybody who reads it next week, next month, or next year will think it's an authorititive article because it is highly rated. It gets highly rated because most people on the site now are not as expert as you are and will rate the article as good, because they believe its contents, PROBABLY partially because they think it is endorsed by SSC.
I think as the site gets bigger, and is read by more and more people, publishing inaccurate advice will become an increasing problem.
Plus I feel a bit sorry for the author, who presumably thought he was writing a good article and didn't set out to mislead. Remember, half of it is right!
It's a very public way to be told what you don't know on here ...
Tim
Thing is, it isn't my view. That is Steve's view and as the Editor of SSC his is the one that counts. I also support him in this area. Let the author have his voice. My first article on SSC was submitted with no review. It did okay. My second article I had Jeff review it first as it was based on his initial work. It too did fine. The next two articles I wrote I had published on sswug.org. I had them both reviewed prior to submission. I wasn't happy with the expousure they got there and hopefully more will be able to read them in the near future here on SSC.
The best thing authors can do is learn from what they have done in the past. Writing isn't easy, it takes time and effort. You only get better by doing and accepting constructive critism along the way. Some of the comments here are worthless, as they really don't help the author learn. Other comments are meant to help the author so that the next article is better.
Why are articles published on this site, are they for teaching the author how to write or are they supposed to be useful articles? There should be a big warning at the start of each article stating the article could have be written by a total novice, has not been reviewed and could be total incorrect rubbish.
August 10, 2009 at 11:00 am
Tim Walker (8/10/2009)
The problem with your view is that with 8 pages of mostly adverse comments, nearly all of which I agree with, this article still has a four star rating as of now.
Therefore anybody who reads it next week, next month, or next year will think it's an authorititive article because it is highly rated. It gets highly rated because most people on the site now are not as expert as you are and will rate the article as good, because they believe its contents, PROBABLY partially because they think it is endorsed by SSC.
I think as the site gets bigger, and is read by more and more people, publishing inaccurate advice will become an increasing problem.
I agree with Tim that many readers will assume endorsement by SSC and might not read any of the comments or even be aware of their presence. I also think SSC is judged based on what articles are selected for publication. I also feel the prestige of being published here is proportional to the quality of other published articles.
I doubt SSC has the necessary resources to verify every statement made in every article that's published, but presumably articles are at least checked for the obvious such as appropriate content so there's a wide grey area here. It sounds like Steve is leaning more towards less review.
I've been an enthusiastic fan of SSC for many years and maybe I've learned enough from it to become more discerning, but it seems to me that there's been a downward trend in the accuracy and/or value of the articles selected for publication here. I now tend to look forward more to the follow-up discussion than I do to many of the articles.
I sincerely hope the many brilliant commenters out there don't get so exasperated that they give up on this site. I also hope that the many brilliant authors out there aren't reluctant to submit articles for fear of being lambasted or concern over being trivialised by association. If SSC leans a little towards more review, those things are less likely to happen.
August 10, 2009 at 11:24 am
> It really is up to the authors to seek out peer reviews of their
> articles before publication. The editor chooses to publish submitted
> articles with little interference, preferring that the authors voice be heard.
> You only get better by doing and accepting constructive critism
> along the way. Some of the comments here are worthless, as they
> really don't help the author learn.
An author purporting to be an expert should learn on his own time, not mine. Additionally the editor should require a peer review prior to publishing if the desire is for this website to be respected. Providing "little interference" and "preferring that the author's voice be heard" without regard for correctness leads to a chaotic and unrespected site.
my 2cents
August 10, 2009 at 12:07 pm
jacroberts (8/10/2009)
ronmoses (8/10/2009)
10. WHERE clauses - In a WHERE clause, the various operators used directly affect how fast a query can run. Here are the conditional operators used in the WHERE clause, ordered by their performance.
=, >, =, <=, , !=, !>, !' be much further up the performance list to '!<' as they are both mean exactly the same thing.
Actually, they mean almost the same thing. 2 ! 2 either. (Okay, now I'm being overly conscious of petty details.)
- 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
August 10, 2009 at 12:08 pm
gregory.anderson (8/10/2009)
Here's a question about one of the items. You mentioned that writing "master.dbo.proc_name" is better than just "proc_name", well, what about writing "master..proc_name"? My company does not specify the schema name so it's always "dbo". Is doing the ".." just a shortcut/default to "dbo"?Thanks
Yes.
- 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
August 10, 2009 at 12:11 pm
What about NO LOCK in the Select?
August 10, 2009 at 12:14 pm
yadavmanoj (8/10/2009)
What about NO LOCK in the Select?
Only if you like your data dirty.
See - http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 10, 2009 at 12:25 pm
This was an interesting article, but I must respectfully disagree on a few points. I apologize if I overlap somewhat with Gail Shaw and other commentors.
2. SQL-92 -
As a general rule I recommend using the standard syntax when it is available, but some of the enhancements in T-SQL are useful. Also, recent versions of SQL Server have moved passed SQL-92 to newer ANSI standards.
3. Variables - Use as few as possible variables. It frees spaces in cache.
Considering the whole script/procedure has to be loaded into memory anyway and the advantages to readability and maintainability to using variables in the script/procedure, I think they will almost always win out over 'magic constants' in code.
5. Fully Qualified Names -
Schema qualifying names will bring this benefit. There is rarely a need to database qualify the name. In fact, this can cause complications if you want to reuse code between databases (common if you perhaps have to segregate data between multiple clients but have the same or nearly the same schema for each client, and it also comes up for certain general purpose utility scripts).
11. More WHERE clause hints -
The fewer the conditions you can place the better, but function calls are often worse since they make it difficult to use indexes. Also, in your example remember that depending on your collation setting, SQL is frequently case insensitive so you would not need either of those options.
14. Avoid using cursors -
I agree with this, but I would try very hard to find a set based solution in place of either cursors or the for loop recommended in the article.
18. Try to use table variables instead of Temporary Tables -
In general, I agree. Table variables are normally to be preferred, but there are some cases where the Temp Table is a better choice. Complex constraints and indexes can be applied to a Temp Table but not a table variable. (Of course, if you want complex constraints and indexes it may be better to just create actual tables, but I have seen some unusual cases where my entire development team agreed that a Temp Table with an Index was the overall best choice.) Also, temp tables are easier to deal with in conjunction with dynamic sql than table variables (again, dynamic sql should be used cautiously, but there are some cases where it is truly the best choice. The Curse and Blessing of Dynamic SQL is worth reading on this point.)
I certainly appreciate you providing this article and starting the conversation.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
August 10, 2009 at 12:35 pm
jonwolds (8/10/2009)
Some points in this article no longer apply, some are mis-leading and some are just incorrect. Please read with caution.
Absolutely agreed. Very dangerous article espesially for newbees. Better not to be publish something like this with tonns of mistakes and misleading info.
August 10, 2009 at 12:38 pm
steve_melchert (8/10/2009)
> It really is up to the authors to seek out peer reviews of their> articles before publication. The editor chooses to publish submitted
> articles with little interference, preferring that the authors voice be heard.
> You only get better by doing and accepting constructive critism
> along the way. Some of the comments here are worthless, as they
> really don't help the author learn.
An author purporting to be an expert should learn on his own time, not mine. Additionally the editor should require a peer review prior to publishing if the desire is for this website to be respected. Providing "little interference" and "preferring that the author's voice be heard" without regard for correctness leads to a chaotic and unrespected site.
my 2cents
You do have a point. But I have to say that I'd rather they get published and discussion gets the chance to point out the flaws.
It's very difficult to root out false data that may be common if it's never aired. This is an easy way to accomplish that.
As for the editorial policy of the site, it's hard to argue with success. This is one of the most successful SQL sites anywhere. Obviously, it has something going for it.
I think the idea is more that the articles are meant to spark discussion than that they are meant to be authoritative. This is, after all, a community, not a magazine.
Also, I have to say that I've seen plenty of absolute idiocy come out of highly peer-reviewed academic papers. Ones that had been given the blessing of some of the top people in various fields. Good stuff too, but plenty of just plain trash at the same time. And following the most recommended editorial policies still gives us news media that publish the most heinous mess of messed up articles and misunderstood data ever seen by man. So, no amount of review will ever be a guarantee of accuracy.
- 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
August 10, 2009 at 12:43 pm
jacroberts (8/10/2009)
Why are articles published on this site, are they for teaching the author how to write or are they supposed to be useful articles? There should be a big warning at the start of each article stating the article could have be written by a total novice, has not been reviewed and could be total incorrect rubbish.
Steve has time and again answered that question and to both parts of it the answer is "yes." Numerous times he has stated he doesn't want to make it difficult for anyone to write an article. And yes, even the ones that are "horrible" are useful. If you happen to use these "horrible" practices, by reading the discussion board, you just might learn a "better" way to do something. And I use quotes around "horrible" and "better" because as Jeff Moden will often say, the best solution oftens depends on "it depends..."
And I have say, harsh though it might be, anyone who isn't smart enough to go "Gee, perhaps after reading this awesome article I should read the comments..." to then learn that perhaps parts of it aren't quite as awesome as he or she thought, shouldn't be in the business. As for the high rating it's gotten, that would be a clue for me to hit the discussion board as well to see if there were some other nuggets of information to be gotten.
Anyone who frequents the forums and reads even a few articles and their respective threads will get the idea to check out the discussion thread after reading the article. Sometimes I will check out the discussion thread before I read the article to see if the article is worth reading.
It is easy for those who are old hats to not understand the viewpoint of the newbie. There are times when the old hat just wants to go and throw up his or her hands in disgust and shout "How the -bleep- can you NOT know THAT?" But, then they remember that once they didn't know and go about the gentle task of educating the person about the errors of their ways. That is one of the things I love about this community and the way it is run.
The article and the thread was an education for me (and I probably fall into the category of "I know enough to be dangerous" about SQL, but luckily I know that so I'm very careful). Gail and a few others pointed out the errors of the article and asked for further information on a few points, like the one with CONVERT being deprecated in the future. Those who merely said "This is a bad article" without pointing out what made it bad haven't helped. Those who agreed with the good points of this article have also helped.
And, I betcha this is the last time this author writes and submits a technical article (to either SSC or any other publisher) without getting a technical review first (or without at least adding some additional data about what testing was done to prove his points). The article was well-written (grammar-wise), it just needs, as Gail and a few others have pointed out, a bit of review. I definitely hope the writer learned something, I certainly did.
SSC, as a community, includes everyone from the n00bs to the newbies to the "I know enough to be dangerous" to the "I know enough to not create a lot of trouble" to the "I know enough to get you out of the trouble you've gotten yourself into." The articles can help everyone learn a bit more. I respect Steve for keeping that door open as everyone needs to start somewhere.
Besides, SSC doesn't bill itself as the site where "all the best articles are that will answer every question and be totally accurate every time." It bills itself as a "Microsoft SQL Server Community of DBAs, developers and SQL Server users."
-- Kit
August 10, 2009 at 12:43 pm
steve_melchert (8/10/2009)
> It really is up to the authors to seek out peer reviews of their> articles before publication. The editor chooses to publish submitted
> articles with little interference, preferring that the authors voice be heard.
> You only get better by doing and accepting constructive critism
> along the way. Some of the comments here are worthless, as they
> really don't help the author learn.
An author purporting to be an expert should learn on his own time, not mine. Additionally the editor should require a peer review prior to publishing if the desire is for this website to be respected. Providing "little interference" and "preferring that the author's voice be heard" without regard for correctness leads to a chaotic and unrespected site.
my 2cents
I'm sorry, but I just reread the article, and no where does the author purport to be an expert.
Viewing 15 posts - 76 through 90 (of 244 total)
You must be logged in to reply to this topic. Login to reply