August 10, 2009 at 6:44 am
ta.bu.shi.da.yu (8/10/2009)
But both of these are anti-semi-joins, which means that I don't actually think you need the top 1.
What TOP 1?
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 6:46 am
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.
=, >, =, <=, , !=, !>, !<
for details, refer to the article: http://msdn.microsoft.com/en-us/library/ms190276.aspx
The article you've linked to does not support your point, as far as I can see. That article addresses operator precedence, not performance. Your point may be correct for all I know, but that article doesn't address it one way or the other.
Strange as it may seem, if you run the Oracle Automatic Workload Repository ADDM report then you'll often get Oracle warning not to use the operator. It seems that they say this because it does a full scan against the tables, whereas if you do something like number > 0 and number < 0 then it will use an index. Odd, but true.
P.S. yes, I know this is SQL Server Central. I just thought it was interesting to note this. Apologies if anyone gets hot under the collar for introducing the dreaded competitor into the conversation!
August 10, 2009 at 6:47 am
August 10, 2009 at 6:47 am
ta.bu.shi.da.yu (8/10/2009)
Strange as it may seem, if you run the Oracle Automatic Workload Repository ADDM report then you'll often get Oracle warning not to use the operator. It seems that they say this because it does a full scan against the tables, whereas if you do something like number > 0 and number < 0 then it will use an index. Odd, but true.
Oracle != SQL Server
August 10, 2009 at 6:49 am
oli (8/10/2009)
ta.bu.shi.da.yu (8/10/2009)
Strange as it may seem, if you run the Oracle Automatic Workload Repository ADDM report then you'll often get Oracle warning not to use the operator. It seems that they say this because it does a full scan against the tables, whereas if you do something like number > 0 and number < 0 then it will use an index. Odd, but true.
Oracle != SQL Server
No, it's Oracle SQL Server!
I never said it was. I was giving what I thought was an interesting response. Sorry if you feel offended.
August 10, 2009 at 6:52 am
Overall I've rated this article poorly. Sorry, a "Top Twenty Tips" sounds good (a nice bit of alliteration), but unfortunately for a topic as complex as this you can only mislead. I've broken most of these "rules" at some time or other - producing better code / performance as a result. Aside from the obvious mistake, I would suggest that a "disclaimer" (i.e. these rules are made to be broken, and don't always get the results) - and call it "Top Twenty Hints" instead.
The real "tips" would be to: thoroughly read a good book or 2 (e.g. Inside SQL Server 2005: T-SQL Querying), learn how to read and interpret execution plans, understand the implications of compilation and the optimiser on balancing resource usage, finally - don't make to many assumptions based on fixed sets of rules (rule-based optmisers were succeeded a long time ago - take the hint).
P.S.
It's the *= syntax (the query join operators) that is deprecated in SQL 2005, so you can still do inner joins in the old style, but not outer joins. Also, you can just run the database in compatibility mode (80) - but that has drawbacks too (not least that most of your code using any "new style 2005 syntax / features" will break).
This DBA says - "It depends".
August 10, 2009 at 6:59 am
ta.bu.shi.da.yu (8/10/2009)
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 7:03 am
ronmoses (8/10/2009)
ta.bu.shi.da.yu (8/10/2009)
vetri (8/10/2009)
Hi,I tried with these two statements but the second one is not get executing and it says "Incorrect syntax near the keyword 'exists'." What wrong with this or is this wrong statement?
SELECT * FROM employee WHERE emp_no NOT IN (SELECT emp_no from emp_detail)
SELECT * FROM employee WHERE NOT EXISTS (SELECT emp_no FROM emp_detail)
Change the second one to:
SELECT * FROM employee WHERE NOT EXISTS (SELECT * FROM emp_detail)
That may correct the error, but they're still not equivalent statements. As stated elsewhere, the first statement returns only those records from employee where there are matching records in emp_detail. The second returns ALL records from employee if there are ANY records in emp_detail, otherwise it returns NO records. A significant difference, I'd say.
Everyone seems to be imagining a correlation that isn't there (but should be) in the NOT EXISTS statement.
Replacing SELECT emp_no with SELECT * doesn't change anything.
You can write SELECT N'fish banana cupcake' if you wish...EXISTS doesn't care.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
August 10, 2009 at 7:03 am
Certainly number 11 is wrong in most cases. The others i will leave up to the group, but a function on the left side of an = sign in a where clause means that it will not use an index.
Jimmy
"I'm still learning the things i thought i knew!"August 10, 2009 at 7:04 am
I was going to post a detailed reply to the article, but I see that Gail beat me to it. (No fair! MAJOR time-zone head-start! 🙂 )
There's some good advice in the article. Point 15 about not using "select *", point 13 about avoiding unnecessary Distinct commands, points 19 and 20 have some value, and parts of some of the rest of it, are valid. Much of the rest needs more research by the author, as already pointed out.
It's a good effort, it just needed a bit more review before publication, to avoid some of the harsher post-publication reviews. (The discussion accomplishes the same thing as pre-publication review, just less privately.)
- 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 7:06 am
1. Keywords - Use SQL keywords in capital letters to increase readability
I find that the nice highlighting in SSMS more than adequate and a lot easier on the eyes than CAPITAL LETTERS EVERYWHERE.
August 10, 2009 at 7:11 am
GilaMonster (8/10/2009)
rja.carnegie (8/10/2009)
How much space does a variable consume? I assume kilobytes at most typically, which is really nothing at all. I mean, you aren't going to use thousands of variables in a procedure.Not much. I did a quick test and a plan for a proc with 50 variables took a few kb more space in the plan cache than a plan for a proc with the same queries but no variables.
Thanks. I'm assuming this can be expected to translate into "no significant impact on server performance". Although I'm often surprised by what does and doesn't matter, with SQL Server.
Last I heard, SELECT @variable1 = expression, @variable2 = expression, ... ; is deprecated.
Got a reference for that? If something is deprecated, Books online will state so.
...Oh. What it actually says in 2000 and 2005 is "For assigning variables, we recommend that you use SET @local_variable instead of SELECT @local_variable." Which is odd, because SELECT seems to be more powerful, for instance allowing you to capture both @@ROWCOUNT and @@ERROR immediately after a statement, if you so wish. Well, I started on 7.0, so maybe it was deprecated then and de-deprecated since. Or maybe I escalated it mentally from "For no reason we are recommending you not use this command" to "We're going to take it away". Is there a reason besides less typing, distinction between DML and variable operations, another issue that only existed in SQL Server 6.5, or maybe being less compatible with a competitor's SQL product?
Likewise you can SELECT @variable = expression [, ...] FROM query, or SET @variable = (SELECT expression FROM query). Now which of those is it that raises an error if there's more than one row... I think it's the second. And I suggest that usually IS an error, unintended behaviour of the program, so you want to use the syntax that tells you so. And if SQL Server came with an animated paperclip (I don't recall seeing one) then on the first way, it would be hinting that you should just use a cursor if you want to.
August 10, 2009 at 7:20 am
oli (8/10/2009)
1. Keywords - Use SQL keywords in capital letters to increase readability
I find that the nice highlighting in SSMS more than adequate and a lot easier on the eyes than CAPITAL LETTERS EVERYWHERE.
Meaning in Chinese depends on tone, meaning in program code depends on colour... maybe for some folks more than others. And our printer is monochrome, we just get grey shades. Some pretty pale grey, actually. And if I use a word that happens to be reserved, maybe obscurely so, as for instance a column name... well, maybe I should not do that, but... I like the caps. And we also develop in Java, which has an entirely different colour scheme. And I am the secret lord of dynamic SQL, so I am more powerful than you can possibly imagine but most of my DML is plain red.
So, yeah, to taste. 😉
August 10, 2009 at 7:20 am
oli (8/10/2009)
1. Keywords - Use SQL keywords in capital letters to increase readability
I find that the nice highlighting in SSMS more than adequate and a lot easier on the eyes than CAPITAL LETTERS EVERYWHERE.
Nevertheless, many people consider it good style to capitalize keywords.
I'm one.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
August 10, 2009 at 7:21 am
GilaMonster (8/10/2009)
I've seen SQL convert a predicate WHERE SomeColumn != 0 into WHERE (SomeColumn > 0 OR SomeColumn < 0) and seek on an appropriate index. It's still two partial scans of the table but, if there are lots of cases where SomeColumn=0, it's likely better than a full scan of the index or table (likely, because this can be a bit of an edge case as to whether seeks which do partial scans are better than full scans)
Following a theme, here's a trivial example:
DECLARE @Employee TABLE (emp_id INT NOT NULL PRIMARY KEY NONCLUSTERED)
SELECT * FROM @Employee E WHERE emp_id 0
[font="Courier New"] |--Clustered Index Seek(OBJECT: (@Employee AS [E]), SEEK: ([E].[emp_id] (0)) ORDERED FORWARD)[/font]
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 31 through 45 (of 244 total)
You must be logged in to reply to this topic. Login to reply