August 10, 2011 at 3:20 am
This abbreviated syntax
Database..Table
appears quite frequently in various stored procedures I am working with.
I tend to change this to the explicit version
Database.dbo.Table
when I find it, but what benefits, if any, does this provide? Am I wasting my time?
Thanks for any input.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 10, 2011 at 3:22 am
August 10, 2011 at 3:52 am
Jayanth_Kurup (8/10/2011)
...I read somewhere if the schema name is not provided usually it searches all sys and dbo schemas before looking for any user defined schema objects.
It's not exactly right: if the schema name if not provided SQLServer always checks if there is a object in a user-default schema.
And that is exactly why there is a performance benefit to always specify the schema (eg. dbo) for any object (eg. tables, views, stored procs etc.). So, SQLServer doesn't need to spend a time for any checks, it will straight away refer to the required object.
You are not wasting your time. 😉
August 10, 2011 at 5:17 am
Eugene Elutin (8/10/2011)
Jayanth_Kurup (8/10/2011)
...I read somewhere if the schema name is not provided usually it searches all sys and dbo schemas before looking for any user defined schema objects.
It's not exactly right: if the schema name if not provided SQLServer always checks if there is a object in a user-default schema.
And that is exactly why there is a performance benefit to always specify the schema (eg. dbo) for any object (eg. tables, views, stored procs etc.). So, SQLServer doesn't need to spend a time for any checks, it will straight away refer to the required object.
You are not wasting your time. 😉
I think you misunderstood my previous post , i mentioned that the order in which SQL search for schema qualified objects is something lik sys followed by dbo followed by user defined schemas. If the fully qualified name includes the user schema then sql can skip the sys and dbo schema.
August 10, 2011 at 6:36 am
Jayanth_Kurup (8/10/2011)
Eugene Elutin (8/10/2011)
Jayanth_Kurup (8/10/2011)
...I read somewhere if the schema name is not provided usually it searches all sys and dbo schemas before looking for any user defined schema objects.
It's not exactly right: if the schema name if not provided SQLServer always checks if there is a object in a user-default schema.
And that is exactly why there is a performance benefit to always specify the schema (eg. dbo) for any object (eg. tables, views, stored procs etc.). So, SQLServer doesn't need to spend a time for any checks, it will straight away refer to the required object.
You are not wasting your time. 😉
I think you misunderstood my previous post , i mentioned that the order in which SQL search for schema qualified objects is something lik sys followed by dbo followed by user defined schemas. If the fully qualified name includes the user schema then sql can skip the sys and dbo schema.
Sorry, but I'm afraid I misunderstood your post again, because SQL does never search for schema qualified objects and that is the whole idea of why qualifying object schema makes it faster. SQL only need to seach for unqualified schema objects, but not in the order of sys, dbo then user defined schema - quite oposite: default user schema then dbo, never sys (try select * from objects - will it work?) .
To make it clear:
1. SELECT * FROM TableName - here is sample of unquailfied schema object used, SQL will perform the following: 1. check if user has default schema, 2. check if the object exists in this schema, 3. if not, it tries dbo schema. this technique can be used if your design uses different schemas for different users - it's quite rarely case and I've never personally seen the real life implementation like this.
2. SELECT * FROM ShemaName.TableName - here is sample of quailfied schema object used. If not designed for the 1. then do it always like that to safe some time on lookup.
August 10, 2011 at 7:34 am
Thank you.
Eugene, your answers make a lot of sense, thanks for taking the time to respond.
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 10, 2011 at 7:54 am
I don't know why but I feel it's important to remind everyone that dbo.table and table will generate 2 different plans.
IIRC, even DBO. and dbo. will also do 2 plans. So there's a performance hit there.
Has for using schema or not it's more a good practice to point to the correct object than speed issues. I can't imagine that process taking more than a few nanoseconds. So unless you're running at 1M+ queries / sec I don't really think you have to worry about that one.
Maybe I'll run some tests later today if I have time. Maybe ping me back if I don't do it today!
August 10, 2011 at 8:25 am
In our nanoworld, nanoseconds are precious 😀
Couple relevant links:
http://technet.microsoft.com/en-gb/library/cc966420.aspx
and
http://www.sqlservercentral.com/articles/Administration/procedurecache/591/
In the last one you will also find why naming stored procedures with sp_ prefix is not good idea...
August 10, 2011 at 8:37 am
Eugene Elutin (8/10/2011)
In our nanoworld, nanoseconds are precious 😀Couple relevant links:
http://technet.microsoft.com/en-gb/library/cc966420.aspx
and
http://www.sqlservercentral.com/articles/Administration/procedurecache/591/
In the last one you will also find why naming stored procedures with sp_ prefix is not good idea...
I'd seriously test that theory out. I'd bet that you won't see any major difference untill you hit the Billion execution. It's a good practice, but I wouldn't spend 1 year refactoring an app without more proof of the benefit...
August 10, 2011 at 8:54 am
Ninja's_RGR'us (8/10/2011)
... but I wouldn't spend 1 year refactoring an app without more proof of the benefit...
seriously, as a contractor, in a such economy hardtime, I definitely would 😀
And if this work needs to be justified by prooving with tests, I would create required tests which would proof benefits 😉
August 10, 2011 at 11:55 am
Eugene Elutin (8/10/2011)
Ninja's_RGR'us (8/10/2011)
... but I wouldn't spend 1 year refactoring an app without more proof of the benefit...seriously, as a contractor, in a such economy hardtime, I definitely would 😀
And if this work needs to be justified by prooving with tests, I would create required tests which would proof benefits 😉
Maybe that's why you're having a hard time getting contracts ;-).
August 10, 2011 at 12:04 pm
Ninja's_RGR'us (8/10/2011)
Eugene Elutin (8/10/2011)
Ninja's_RGR'us (8/10/2011)
... but I wouldn't spend 1 year refactoring an app without more proof of the benefit...seriously, as a contractor, in a such economy hardtime, I definitely would 😀
And if this work needs to be justified by prooving with tests, I would create required tests which would proof benefits 😉
Maybe that's why you're having a hard time getting contracts ;-).
Not at all, creating the work for yourself causes more work for people arround, so everyone is very supportive...:hehe:
August 10, 2011 at 12:08 pm
You do what you have to. I'll keep on this side of the line :-).
August 11, 2011 at 3:04 am
I tried the following and hers what i found, please feel free to commet
I created a schema sampler and a table called sampler.sysobjects with the schema.
I then created a login and user and mapped the user with the schema sampler
When i login as the above user and run select * from sysobjects it queried the catalog view instead of the table which was mapped to the schema sampler.
So it looks like when the querying objects without the schema name it actually preferred the sys schema object over the object created against the schema defined for this user. I would assume this is because the sys schema resolved for the object sysobjects before the sampler schema did
August 11, 2011 at 7:22 am
Jayanth_Kurup (8/11/2011)
...So it looks like when the querying objects without the schema name it actually preferred the sys schema object over the object created against the schema defined for this user. I would assume this is because the sys schema resolved for the object sysobjects before the sampler schema did
...
It does looks like, but it is not what happens! Sorry again, but your assumption is wrong.
To prove it you can create table sampler.tables with the your user schema.
And try select * from tables
You will not get result from sys.tables.
But how to explain yours one? Here we go.
SQLServer has some internal object naming conventions.
For example if you name your stored proc with sp_ prefix it will always think it's a system one first.
Another example is naming user object exactly as existing system object with sys prefix as in your case.
And that what really happens!
That is why when you name your custom object in a user schema as sysobjects, sysindexes or anything else which name begins with sys you will get results from sys.sysobjects, sys.sysindexes and etc untill you specify the schema. When you name your object in a user schema as exiting system objects which names do not have sys prefix (eg. sys.objects, sys.tables etc) - Alas! It will not work, you will see that "select * from tables" will refer to 'tables' from your user default schema and from sys.
I tried the following and hers what i found, please feel free to commet
I created a schema sampler and a table called sampler.sysobjects with the schema.
I then created a login and user and mapped the user with the schema sampler
When i login as the above user and run select * from sysobjects it queried the catalog view instead of the table which was mapped to the schema sampler.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply