SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


supplying a schema in queries, performance?


supplying a schema in queries, performance?

Author
Message
ryan.mcatee
ryan.mcatee
SSC-Enthusiastic
SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)

Group: General Forum Members
Points: 125 Visits: 134
To demonstrate this behavior:

CREATE SCHEMA testA
GO

CREATE SCHEMA testB
GO

CREATE TABLE dbo.Cities (city varchar(50))
GO

CREATE TABLE testA.Cities (city varchar(50))
GO

CREATE TABLE testB.Cities (city varchar(50))
GO

INSERT INTO dbo.Cities VALUES ('Chicago')
INSERT INTO testA.Cities VALUES ('Cairo')
INSERT INTO testB.Cities VALUES ('Atlanta')
GO

CREATE PROCEDURE testB.GetCities
AS
SELECT * FROM Cities
GO

EXEC testB.GetCities
GO



Output is:
Atlanta


...CREATE PROCEDURE testB.GetCities
WITH EXECUTE AS CALLER

...CREATE PROCEDURE testB.GetCities
WITH EXECUTE AS OWNER

These all yielded the same result.
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)

Group: General Forum Members
Points: 96531 Visits: 38986
ryan.mcatee (3/13/2013)
To demonstrate this behavior:

CREATE SCHEMA testA
GO

CREATE SCHEMA testB
GO

CREATE TABLE dbo.Cities (city varchar(50))
GO

CREATE TABLE testA.Cities (city varchar(50))
GO

CREATE TABLE testB.Cities (city varchar(50))
GO

INSERT INTO dbo.Cities VALUES ('Chicago')
INSERT INTO testA.Cities VALUES ('Cairo')
INSERT INTO testB.Cities VALUES ('Atlanta')
GO

CREATE PROCEDURE testB.GetCities
AS
SELECT * FROM Cities
GO

EXEC testB.GetCities
GO



Output is:
Atlanta


...CREATE PROCEDURE testB.GetCities
WITH EXECUTE AS CALLER

...CREATE PROCEDURE testB.GetCities
WITH EXECUTE AS OWNER

These all yielded the same result.



Which means if you call a stored procedure in a specific schema and the table in that stored procedure is not accessed with a schema, it first looks in the schema of the stored proc (not dbo) to see if the table exists there. Hmm, "default" (schema the stored proc resides) then dbo, not dbo, then schema.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Ed Wagner
Ed Wagner
SSC Guru
SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)

Group: General Forum Members
Points: 50211 Visits: 10844
Well, at least that makes sense. :-)


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Orlando Colamatteo
Orlando Colamatteo
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40530 Visits: 14413
BinaryDigit (3/12/2013)
Hi

A random question, if you write queries and put the schema before objects will it affect performance in any way and if so what type of metrics are we looking at? even if they are tiny :-D

Regards
Gordon Beeming

Cumulatively, yes, there can be a performance gain by schema-qualifying all your objects. It avoids the overhead of the engine having to find the object you meant to refernece either in the default schema (depends on context as shown above) or in the dbo schema. I like to see all objects schema-qualified. In my opinion it makes the code more readable and avoids potential for latent bugs becoming actual bugs.

Consider the code example above. If someone were to drop the table testB.Cities for some reason the stored procedure would suddenly start returning Chicago...probably not an acceptable side-effect of dropping a table. Similarly, if when the proc had gone live at a time when there was no such table as testB.Cities and later someone added that table to the database the proc would suddenly stop returning Chicago and would start returning Atlanta. These are not things I would like to see happening when a table is added or removed from a database.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
BinaryDigit
BinaryDigit
Mr or Mrs. 500
Mr or Mrs. 500 (545 reputation)Mr or Mrs. 500 (545 reputation)Mr or Mrs. 500 (545 reputation)Mr or Mrs. 500 (545 reputation)Mr or Mrs. 500 (545 reputation)Mr or Mrs. 500 (545 reputation)Mr or Mrs. 500 (545 reputation)Mr or Mrs. 500 (545 reputation)

Group: General Forum Members
Points: 545 Visits: 200
Thank you everyone for the replies :-D

I have made a C# application that looks at a database and then goes through Triggers, Views and Stored procedures and looks for any references to other objects in the database that don't contain a schema before the object Smile. Hopefully from now it always returns no results as I have updated some systems with schemas before all objects.




The Fastest Methods aren't always the Quickest Methods
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search