August 10, 2009 at 4:59 am
I deeply begrudge the minimum one star.
Gail's first post expresses it best, and in milder language terms 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
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
August 10, 2009 at 5:06 am
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.
August 10, 2009 at 5:11 am
Some points in this article no longer apply, some are mis-leading and some are just incorrect. Please read with caution.
August 10, 2009 at 5:53 am
very nice article and must read article.
one question is every thing is true for sql 2008 or not ?
August 10, 2009 at 6:00 am
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.
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
August 10, 2009 at 6:02 am
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.
You can use just @ as a variable name, but I almost always resist the temptation. Starting in SQL Server 2000, our practice was to store @@ERROR in a variable @returnerror after failure-point statements, but you could conventionally use @ there, since you'll be typing whatever-it-is a lot.
Dynamic SQL can be parameterised. That means that it isn't bad.
EXEC sp_executesql
N'SELECT * FROM emp where empid = @id',
N'@id int',
@eid
Last I heard, SELECT @variable1 = expression, @variable2 = expression, ... ; is deprecated.
Procedure full names: I may have databases db1, db2, db3, each containing identical proc1 and proc2. I want proc1 to call dbo.proc2 so that each copy of proc1 doesn't have to have the database name of proc2 in it.
Where clauses: if a table is indexed on columns a, b, c, is there an advantage to using WHERE a ... when the rest of the query makes it logically unnecessary?
ORDER BY: it's good practice to consider ORDER BY, in case you fall into assuming that results come out in an intended order without it, because they usually do. But the server is allowed to return results in any order it likes, unless specified. If your program expects to use results in order, for instance with a cursor or a fake cursor, it must specify order.
Cursor: we're always told that they are expensive to use as to resources. Programmers new to SQL may want too much to do things row-wise. A cursor offers protection to your process on a multi-user server that using a loop variable obviously doesn't, unless you use a transaction, which can be overkill. And if your workaround also involves a temporary table then you're in danger of re-inventing the cursor with all its original drawbacks, except that cursor is there ready to be used. Incidentally, I favour cursor variables: self-disposing.
SELECT INTO: that is an interesting point. On 2000 we had a regular problem of a temporary object created in a transaction preventing other temporary objects create/drop until the transaction was done. This sounds like a similar issue. If you want your temporary object to be created from existing table metadata, you could start with an similar query that returns no results to create the table (I like WHERE/ON (1 = 0 ) ), then the real one. And create all your temporary tables before BEGIN TRANSACTION, even the ones that at runtime may not be used.
August 10, 2009 at 6:05 am
kuldip.bhatt (8/10/2009)
one question is every thing is true for sql 2008 or not ?
No. But then not everything in the article is true for 2005 or 2000 either.
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:07 am
17. CREATE TABLE vs. SELECT INTO - Select * INTO works fine for small tables, but when dealing with large record sets or long-running queries, it creates locks on the system objects within the tempdb database. As a result, other queries and procedures that need to create objects within the tempdb database will have to wait for the long-running query to complete. This is because when an object is created, an exclusive lock is taken against the sysobjects, syscolumns, sysindexes tables.
[font="Arial Black"]Absolutely not true. Please, folks... stop perpetuating this myth.[/font] That's a left over wives tale for the original version of 6.5. Don't take my word for it, though... that how myth's get started and perpetuated 😉 Instead, please read the following article especially the part about how the fix has been built in since version 7...
http://support.microsoft.com/kb/153441/EN-US/
--Jeff Moden
Change is inevitable... Change for the better is not.
August 10, 2009 at 6:09 am
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.
Last I heard, SELECT @variable1 = expression, @variable2 = expression, ... ; is deprecated.
Got a reference for that? If something is deprecated, Books online will state so.
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:13 am
Jeff Moden (8/10/2009)
[font="Arial Black"]Absolutely not true. Please, folks... stop perpetuating this myth.[/font] That's a left over wives tale for the original version of 6.5. Don't take my word for it, though... that how myth's get started and perpetuated 😉 Instead, please read the following article especially the part about how the fix has been built in since version 7...
To add to that....
In SQL 2000, any form of temp table creation, if frequent enough (several a second usually), could cause blocking on the allocation pages (most often seen as a PAGELATCH wait on resource 2:1:3). This happened for both CREATE TABLE and SELECT INTO and the workaround was to create more files for TempDB and optionally enable a traceflag. With several enhancements to tempDB and temp table creation in 2005 and above, it's a lot more difficult to run into this contention problem.
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:28 am
GilaMonster (8/10/2009)
Sorry, but I'm going to greatly disagree with a number of these 'tips'
I have to agree with that. Gail's eval of some of the other tips is spot on.
I'll also say that a lot of the advice coming up in some of the discussions for this article is just dead wrong, as well. Be real careful before you take any "Well, here's how I do/did it" advice on this subject. Someone could actually write another article about the things wrong with this article and the follow up discussion.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 10, 2009 at 6:30 am
GilaMonster (8/10/2009)
Sorry, but I'm going to greatly disagree with a number of these 'tips'Also, try to avoid IN. While checking the existence of some values, then use EXISTS instead of IN. Because IN counts the NULL values also, hence slower than EXISTS. Since EXISTS returns Boolean(Yes/No) but IN returns all values hence result set for IN is heavier than EXISTS.
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)Those two queries are not equivalent, hence they won't be running the same speed.
IN checks for matches, so the first query will return all records in employee where there isn't a match in emp_detail. Exists returns true if there are any rows at all in the subquery, it doesn't care about the values returned in the select. Hence that second query will only return results if there are no rows at all in emp_detail and, if that's the case, it will return all the rows in the employee table.
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 asked about this a while back. See http://www.sqlservercentral.com/Forums/Topic626235-338-1.aspx
There was quite a bit of debate.
But both of these are anti-semi-joins, which means that I don't actually think you need the top 1.
August 10, 2009 at 6:33 am
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)
August 10, 2009 at 6:38 am
Jeff Moden (8/10/2009)
GilaMonster (8/10/2009)
Sorry, but I'm going to greatly disagree with a number of these 'tips'I have to agree with that. Gail's eval of some of the other tips is spot on.
I'll also say that a lot of the advice coming up in some of the discussions for this article is just dead wrong, as well. Be real careful before you take any "Well, here's how I do/did it" advice on this subject. Someone could actually write another article about the things wrong with this article and the follow up discussion.
Yow - I hope I didn't give any bad advise! What was the wrong advise you've read in this thread Jeff?
August 10, 2009 at 6:44 am
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.
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
Viewing 15 posts - 16 through 30 (of 244 total)
You must be logged in to reply to this topic. Login to reply