August 10, 2009 at 8:03 am
5. Fully Qualified Names - Always use the fully qualified name when calling stored procedures. This would be the format database_name.schema_name.table_name. For example, use EXEC master.dbo.Your_Proc_name instead of EXEC Your_Proc_name This is a very common mistake, which causes an extra trip to the procedure cache to get the execution plan for execution.
This may be true from a performance perspective, but from a portability and maintenance perspective, it can be very useful to avoid the database_name in a stored procedure call.
Here's an example I run into quite often: renaming a database with stored procs. If the database is renamed, all the fully qualified procedure calls have to be changed.
Moreover, using the database name prevents you from running more than one "version" of a database, e.g., development, staging and production.
August 10, 2009 at 8:07 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%5B/quote%5D
This can't be a meaningful surely. The reference article is talking about operator precedence which is about the order they are applied in mixed operator statements.
In a where clause, you have to use the operator which gives you the required result. of course = will tend to return fewer rows, but you can hardly substitute it for a <.
Also I understood that <= was slightly more efficient than < (i.e. there are cases where you can choose the comparison value so that <= is used, and it will be slightly - very slighty - quicker than <).
10. WHERE clauses -
SELECT emp_name FROM table_name WHERE LOWER(emp_name) = 'edu'
v
SELECT emp_name FROM table_name WHERE emp_name = 'EDU' OR emp_name = 'edu'
The relative performance of these statemetns depends on table size, availabilityof an index and the slectivity of the index.
If the table is small, or if there is no index starting with emp_name or the selectivity is low (many records will match), then the first statment will work best since only one scan will be performed, but with an available selective index, the second works better. But then a case insensitive index will work better still (with just a search for emp_name = 'EDU').
August 10, 2009 at 8:08 am
in 7.0 and 2000 if you named a proc sp_ you got a trip to master to try and resolve the name. In 2005 that has changed a bit. Now its about quickly resolving a name to the procedure cache as much as anything from what I can gather.
August 10, 2009 at 8:19 am
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
Your own procs shouldn't be in master, but hey, you own that server. 😉 (I put stuff into tempdb! I am crazy!)
Until SQL Server 2005, dbname..object meant "dbname.user.object if it exists, otherwise dbname.dbo.object", with suitable users' created objects being dbo by default, and in fact not-dbo objects rarely used, and discouraged, except for temporary objects which are a different story anyway. I assume, a hangover from an old SQL Server edition.
After the 2005 edition, dbname.user.object comes second or last, I forget which, the middle part of the name becomes something more like a true implementation of "schema" concept anyway, and code that just goes "SELECT column FROM table" becomes (I think) faster and more re-useable internally in SQL Server (execution plans cache) because it's no longer the case that your use of the command has to be considered as possibly "SELECT column FROM gregory.table" and mine is "SELECT column FROM robert.table".
August 10, 2009 at 8:28 am
rja.carnegie (8/10/2009)
... because it's no longer the case that your use of the command has to be considered as possibly "SELECT column FROM gregory.table" and mine is "SELECT column FROM robert.table".
It still is possible. If a table (view, proc) isn't schema-qualified, SQL looks first in the user's default schema then in the dbo schema (I think). Worth testing.
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 8:30 am
Grasshopper, by the way, I'm not putting them in master.....just using a dbname as an example (obviously a bad example).
Thanks for your response, but I'm having a little trouble understanding your post-2005 answer. Are you saying that it is in fact faster to write "select column from tablename" as opposed to "select column from mydb..tablename"?
August 10, 2009 at 8:46 am
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.
August 10, 2009 at 9:00 am
GilaMonster (8/10/2009)
rja.carnegie (8/10/2009)
... because it's no longer the case that your use of the command has to be considered as possibly "SELECT column FROM gregory.table" and mine is "SELECT column FROM robert.table".It still is possible. If a table (view, proc) isn't schema-qualified, SQL looks first in the user's default schema then in the dbo schema (I think). Worth testing.
For SQL Server 2005; if a value is specified in the default schema for a user, under "user mapping", it will look in there first. This defaults to dbo when a new user is created so a query will look in dbo first.
August 10, 2009 at 9:07 am
Lynn Pettis (8/10/2009)
oli (8/10/2009)
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.
And it is a policy I totally agree with. I've learned hit the discussion on any article here to see what people think about it and also to see if there are any corrections after the article was published. It's part of why I like this place, it's a great testing ground for a lot of stuff.
It's great to see various viewpoints (both newbie and pro) on SQL and see what works and what doesn't without making the same painful mistakes (both in the articles and the questions asked on the forums). I haven't had the need (nor is it part of my current job duties) to set up daily maintenance plans but from the forums, I've learned to NOT truncate and then autoshrink the transaction log as part of my daily maintenance plain, right Gail? 😀 But if it ever comes up in the future... well, now I know.
-- Kit
August 10, 2009 at 9:27 am
i would have to differ on the dynamic sql statement. if you have a query that runs in 60 seconds and rewrite it as dynamic (truely dynamic with changing variables each call) it can still execute much faster even with the recompile, etc.
August 10, 2009 at 9:28 am
gregory.anderson (8/10/2009)
Grasshopper, by the way, I'm not putting them in master.....just using a dbname as an example (obviously a bad example).Thanks for your response, but I'm having a little trouble understanding your post-2005 answer. Are you saying that it is in fact faster to write "select column from tablename" as opposed to "select column from mydb..tablename"?
Less typing? I think it doesn't matter a lot whether you specify the database or not, since the query is interpreted in a database context anyway.
What I meant to say was that "SELECT column FROM tableName" - with or without database name as well - used to be bad practice compared to "SELECT column FROM dbo.tableName", but everybody did it anyway - in SQL it's correct to use one-part table names, in MS SQL Server it was a performance mistake. But in 2005 this was "fixed". I like to write dbo anyway. Who knows, they may change it back.
which I did not write, and which was written before 2005 was released but with discussion covering after 2005, the new situation may be more complicated than I described: this one says the new rule (2005) is that (your default schema).table is tried first, then dbo.table - but if "your default schema" is dbo then it's like I was saying. The old way was (your user in database).table first and then dbo.table. And I see the commenters are also arguing about the real performance impact.
August 10, 2009 at 9:34 am
rja.carnegie (8/10/2009)
gregory.anderson (8/10/2009)
Grasshopper, by the way, I'm not putting them in master.....just using a dbname as an example (obviously a bad example).Thanks for your response, but I'm having a little trouble understanding your post-2005 answer. Are you saying that it is in fact faster to write "select column from tablename" as opposed to "select column from mydb..tablename"?
Less typing? I think it doesn't matter a lot whether you specify the database or not, since the query is interpreted in a database context anyway.
What I meant to say was that "SELECT column FROM tableName" - with or without database name as well - used to be bad practice compared to "SELECT column FROM dbo.tableName", but everybody did it anyway - in SQL it's correct to use one-part table names, in MS SQL Server it was a performance mistake. But in 2005 this was "fixed". I like to write dbo anyway. Who knows, they may change it back.
which I did not write, and which was written before 2005 was released but with discussion covering after 2005, the new situation may be more complicated than I described: this one says the new rule (2005) is that (your default schema).table is tried first, then dbo.table - but if "your default schema" is dbo then it's like I was saying. The old way was (your user in database).table first and then dbo.table. And I see the commenters are also arguing about the real performance impact.
Always better to CREATE with the owner and also to qualify with owner...
it's possible to create dbo.sprocname
and
myname.sprocname
in the same DB. now if you do and exec sprocname who wins? (food for thought)
August 10, 2009 at 9:44 am
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
.
August 10, 2009 at 9:55 am
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
August 10, 2009 at 10:37 am
Wouldn't an inner join work just as well, and inner joins are a bit more efficent than outers.
LOL perhaps forget that proposal.
Viewing 15 posts - 61 through 75 (of 244 total)
You must be logged in to reply to this topic. Login to reply