January 15, 2008 at 2:14 pm
We are runinng Sql 2k5, and are considering going through the effort of referencing all objectowners before the object name. Is it really adventageous to do this?
Will we see any performace improvement?
January 15, 2008 at 4:01 pm
yes, yes and yes. I still struggle as to why people still ask this years after this was first raised, especially as it is covered in serveral KB articles.
Generally stored procs are hardest hit and a combination of issues can result in serialisation of the calls to the proc in the cache.
http://www.grumpyolddba.co.uk/sql2000/user%20sp%20procs.htm although this isn't totally what you asked the result is the same and is still present in sql2005, in fact I'm dealing with an issue of this just now.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 16, 2008 at 6:30 am
Just understand that, depending on the stored procedure in question, the performance increase will be miniscule or incredibly huge. The simplest of simple insert statements, for example, going to a single table from a set of variables, while it will improve, it's improving by a millisecond or two. You just won't see it. But some large scale procedure that hits lots of tables will probably see major improvements because, by including the schema, you've eliminated recompiles as well as the additional lookups required to determine which object is which.
It's absolutely a standard best practice to ensure that all objects are referenced by the correct schema name as was outlined in the post above.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 16, 2008 at 12:49 pm
the only real factor is if you get compile locking, this tends to only happen in fairly high repeat usage of the same proc - you can tell if you have this by querying sys.processes.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 16, 2008 at 10:33 pm
Information regarding SQL2005:
Information regarding SQL2000:
I'd always recommend it as the best approach, but any gains you might see will depend on how your applications are set up.
January 16, 2008 at 10:57 pm
On thing to consider is that as of SQL 2008 anything over 2 parts in the SELECT statement is considered deprecated. So - put your database+owner names in the FROM,and use an alias.....
http://msdn2.microsoft.com/en-us/library/ms143729(SQL.100).aspx
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 17, 2008 at 12:36 am
- second first rule for any rdbms : tell the system what you know !
- If you can have 'yes' or 'no' why would you accept 'maybe' ?
- the fact that there is a performance gain - small or huge - should not make a difference in your decision for advise (based on the second first rule), but can be added to your list of arguments.
Also keep in mind, there may be some smart developers that actualy have taken advantage of the ownership/schema cascade. So depending on which user is connectd the proc uses different objects !
This should actualy be the only reason not to qualify your objects.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 17, 2008 at 12:04 pm
2 part naming in select lists has nothing to do with this. Issues specifically arise when stored procs are called without an owner e.g. exec myproc or tables/functions or whatever are not prefixed e.g. select * from mytable. In highly concurrent environments it can affect stored procedure plans, as does recompiles of the entire proc - it's just something to do and has been recommended since sql 7.0 as far as I can remember, although it might be earlier.
Columns are not objects.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 17, 2008 at 12:11 pm
colin Leversuch-Roberts (1/17/2008)
2 part naming in select lists has nothing to do with this. Issues specifically arise when stored procs are called without an owner e.g. exec myproc or tables/functions or whatever are not prefixed e.g. select * from mytable. In highly concurrent environments it can affect stored procedure plans, as does recompiles of the entire proc - it's just something to do and has been recommended since sql 7.0 as far as I can remember, although it might be earlier.Columns are not objects.
I understand all that - but a lot of folks have this kind of overboard syntax(which is about to become deprecated)
select
dbo.mytable.id,
dbo.mytable.f1,
dbo.mytable.f2
from dbo.mytable
Now - according to the "new standard", the RIGHT way to do that is:
select mt.id,
mt.f1,
mt.f2
from dbo.mytable AS mt
If the OP is going to go through all of the effort to put prefixes on everything, it might as well be done the right way so you don't have to do it again in a while.
And - in case you missed it - the object I was referring to was the table....
Just a thought...
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 17, 2008 at 12:22 pm
good to say i've not encountered that level of, I was going to say stupidity, but to be honest very little fails to amaze me these days!
sorry for any confusion on objects.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 17, 2008 at 12:34 pm
we7313 (1/15/2008)
We are runinng Sql 2k5, and are considering going through the effort of referencing all objectowners before the object name. Is it really adventageous to do this?Will we see any performace improvement?
It depends:
select dbo.table1.col1, dbo.table1.col2, dbo.table2.col1
from dbo.table1
inner join dbo.table2
on dbo.table1.col3 = dbo.table2.col2
where dbo.table1.col4 = 'widget'
Is serious overkill with no performance gain at all over:
select table1.col1, table1.col2, table2.col1
from dbo.table1
inner join dbo.table2
on table1.col3 = table2.col3
where table1.col4 = 'widget'
Will get an improvement. The size of improvement will depend on a lot of factors.
Either will perform better than:
select table1.col1, table1.col2, table2.col1
from table1
inner join table2
on table1.col3 = table2.col3
where table1.col4 = 'widget'
With no user/schema data at all.
If these are from a proc called "dbo.Proc1":
exec dbo.proc1
Will perform better than:
exec proc1
The reason for this is that the database engine doesn't have to look up "wait a minute, are there possible 2 or more versions of "Proc1", owned by different schemas/users?" Then, when it runs the proc, since the proc itself declares which tables belong to which users/schemas, it won't have to figure out which one to use in which case.
On the other hand, if you have:
accounting.table1
hr.table1
production.table1
it.table1
All in the same database, then you can make a more general proc that takes advantage of this by leaving off the schema name ("dbo."), and the proc will access whichever version of table1 is owned by the user who is currently using the proc.
Note: I've never done this. I've read about it. I can't think of any reason I would have four tables with similar enough structures, identical names, and different schemas, that they could all be accessed by the same proc differently depending on who is running the proc. That, to me, seems like a horrible case of normal-form violation, and even a misuse of the whole "relational" concept. I just mention it because I've heard it can be done.
I always make my code look like the second example I gave (use "dbo." in the "From" clause, but not elsewhere), and use aliases where appropriate. I've tested this, and it does give a performance boost, but not a noticeable one on simple queries in simple databases with a single user. In complex procs, in heavy-use databases, the improvement is very, very noticeable.
- 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
January 17, 2008 at 12:39 pm
GSquared (1/17/2008)
we7313 (1/15/2008)
select dbo.table1.col1, dbo.table1.col2, dbo.table2.col1
from dbo.table1
inner join dbo.table2
on dbo.table1.col3 = dbo.table2.col2
where dbo.table1.col4 = 'widget'
Is serious overkill with no performance gain at all over:
And (like just previously mentioned) deprecated....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 18, 2008 at 1:39 am
Oh yes, use table_aliases in your queries !
Advantages are obvious.
And a human may even succeed to interprete your query way faster because it will be readable.
One thing MS might do is adjust the "lovely":sick: view/query designer graphical tool, so it automaticaly adds a table_alias in stead of owner.table.column.
Many of our devs just point and click (or visa versa) and we get to clean up the mess - at preformance problems time - for the queries to be readable and interpretable for optimisation.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 18, 2008 at 8:27 am
For those that do non-tsql development I wanted to point out a VERY important tidbit in Linchi's article mentioned above:
"If the stored procedure is called as a language event (i.e. SqlCommand.CommandType=CommandType.Text), SP:CacheMiss is always seen in the trace regardless whether the procedure call is owner qualified or not."
Thus you really should make it a standard practice to declare that the statement you are executing is a stored procedure to avoid this overhead.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply