August 11, 2009 at 8:46 am
Hi All,
I don't post a lot but I do read almost all of the articles posted here. When I first started out doing SQL, I had some of these same misconceptions about how things worked and even my own ideas on how they "work". I ran tests, read lots of articles, and asked many more experienced co-workers and found out a lot of what many of you experts out there already know, never assume anything on your own with out asking questions first and always assume what you know could be wrong.
I do think that this article even with its many flaws is a very good example of how as a group of experts can correct what people believe is true to what is actually true. I have to say that some of these myths do come from schooling, I remember 9+ years ago when I was in school that the instructors perpetuated the cursor / while loop myth and the temp table myths. Also #14 is a myth, from my testing of Cursors vs While loops... 2K5/2K8 SQL static cursors kick the hell out of while loops, but then in turn get there butts kicked by set based operations.
Thanks for the info and I vote for updating the article with the combined efforts posted here.
Adam
(Been in IT for 8 years, wrangled SQL for 6 now...)
August 11, 2009 at 8:58 am
I have contacted the author to ask them to comment here (after reading all comments).
August 11, 2009 at 9:26 am
Lynn Pettis (8/11/2009)
mtassin (8/11/2009)
Lynn Pettis (8/10/2009)
mtassin (8/10/2009)
I'm sure she wasn't hinting at you. I am pretty sure I have an idea or two who she was thinking about as she wrote this particular post.
I'm glad to think so. The guys around here tend to think highly of me when it comes to SQL... but then I get to come here and read what those I look up to post and I realize that even after 12 years of this stuff... there's much more to learn 🙂
Heh... I have to agree with you. This site has taught me quite a lot in the last four years.
Heh... by the way, Lynn... I love your new avatar... it's exactly the same as the old one except you have pink skin this time. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
August 11, 2009 at 9:54 am
Publishing the article as is (with advice that will lead beginners astray) is OK. Promoting it as the headline article in the daily email is somewhat of an editorial blunder in my book.
I have found that SQL Server does a pretty good job of selecting a good execution plan if I:
1: Write queries with sets in mind,
2: Have the proper indexes on the tables,
3: Avoid constructions that doesn't allow use of those indexes (be careful with the use of functions and expressions in WHERE clauses, but it is not always bad),
4: Avoid stored procedures with parameter based logic that confuses the optimizer: I had an example where another developer had written an SP that performed three similar but different functions (with different execution plans). Every (!) time the SP was executed, the optimizer chose the wrong plan. I split it into three separate SPs, and had the application determine which one to execute, which took the procedure from occasionally timing out after (extended timeout) of 45 seconds, to sub-second response.
5: Handling large datasets (10 million rows and up) may require rethinking some things. While selecting a few rows from a multi-million row table using an index with good selectivity is vastly different from performing mass operations.
(Since Oracle has been mentioned... :->) The two biggest differences between Oracle and SQL Server:
a) With Oracle, you have more tools and features to tweak performance
b) With Oracle, you have to know and use those tools to get consistent performance
Tore Bostrup
August 11, 2009 at 10:33 am
Jeff Moden (8/11/2009)
Heh... by the way, Lynn... I love your new avatar... it's exactly the same as the old one except you have pink skin this time. 😛
Yours is pretty spiffy too... makes me wish I had like Graphix skills.. I'd find a way to get the flag flapping in mine... :Whistling:
August 11, 2009 at 3:15 pm
Lynn Pettis (8/11/2009)
One correction to the above regarding table variables
Thanks Lynn! All these years I have used OUTPUT to send rows to a 'real' tbale or a table variable, it never occurred to me that the 'real' table could also be a 'real' temporary table. I must have read the BOL OUTPUT entry an hundred times! How odd - thanks so much for the enlightenment 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
August 11, 2009 at 3:47 pm
Wow! Who needs a peer review or editorial review? 16 pages of feedback in under 48 hours!?! I think the author got the message loud and clear. I agree that opportunities exist for this particular post, I too was equally disappointed after reading the content, as the headline definitely caught my attention.
It is almost shameful that this has turned into a case of kicking a person when they are down. I think the message was loud and clear well before page 2 or 3 not to mention page 16 of comments...
To their defense... At least this person was willing to contribute. Many of the names I am seeing are unfamiliar to me... I am willing to bet that they have yet to offer original submissions.
August 11, 2009 at 4:09 pm
Not all posts are related to the article itself. That is one of the benefits of this community and where you actually learn new things or ideas.
August 11, 2009 at 10:59 pm
Andy DBA (8/10/2009)
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
Using a LEFT JOIN will not return the same results if there are multiple CustomerAddress records that match a Customer record.
August 12, 2009 at 7:45 am
Lynn Pettis (8/11/2009)
Not all posts are related to the article itself. That is one of the benefits of this community and where you actually learn new things or ideas.
And some of us discover new things, and find out that folks here have already written about them.
I will say that I for one wouldn't write 20 tips for better SQL without doing some research to make sure that all 20 were valid for all versions of SQL (or I'd comment them to state which versions each of them applied to).
August 12, 2009 at 9:55 am
I will say that I for one wouldn't write 20 tips for better SQL without doing some research to make sure that all 20 were valid for all versions of SQL (or I'd comment them to state which versions each of them applied to).
Don't trouble on my account for "Ashton-Tate/Microsoft SQL Server" v1.0 or "Compact Edition". 😉
2005 and 2008, Standard and Enterprise, and 32/64 bit is already rather a lot to address. I'd say write about the ones that you use, and somebody will correct you about the others!
August 12, 2009 at 10:00 am
rja.carnegie (8/12/2009)
I will say that I for one wouldn't write 20 tips for better SQL without doing some research to make sure that all 20 were valid for all versions of SQL (or I'd comment them to state which versions each of them applied to).
Don't trouble on my account for "Ashton-Tate/Microsoft SQL Server" v1.0 or "Compact Edition". 😉
2005 and 2008, Standard and Enterprise, and 32/64 bit is already rather a lot to address. I'd say write about the ones that you use, and somebody will correct you about the others!
Yes perhaps I should have qualified it... current versions of SQL server... which would likely be 2000, 2005, and 2008. Yes Microsoft dropped support or whatever with 2000 but its still out there in force.
Never even heard of Ashton-Tate/Microsoft SQL Server... surprises me since my company's main product is DBASE 3 based.
August 12, 2009 at 10:11 am
rja.carnegie (8/12/2009)
Don't trouble on my account for "Ashton-Tate/Microsoft SQL Server" v1.0 or "Compact Edition". 😉
Ashton-Tate (!) - Did you type this into the site directly or use DisplayWrite on your IBM luggable ... 😛
.
August 12, 2009 at 9:24 pm
Thanks to my insomnia…I read the comments after reading the whole article.
Thanks the author for posting this and many thanks to the experts for the corrections. A cup is a half full -- not all 20 tips are wrong.....and some of the wrong ones somewhat benefit me.....I wouldn’t know what I thought was right actually was wrong.
August 12, 2009 at 10:46 pm
mtassin (8/12/2009)
Lynn Pettis (8/11/2009)
Not all posts are related to the article itself. That is one of the benefits of this community and where you actually learn new things or ideas.And some of us discover new things, and find out that folks here have already written about them.
I will say that I for one wouldn't write 20 tips for better SQL without doing some research to make sure that all 20 were valid for all versions of SQL (or I'd comment them to state which versions each of them applied to).
Viewing 15 posts - 151 through 165 (of 244 total)
You must be logged in to reply to this topic. Login to reply