December 16, 2011 at 11:55 am
If I have a SELECT statement that JOINs with a table in a different database (same server), I can write it like this:
SELECT b.EmployeeName FROM Invoice AS a
JOIN PersonnelDB.dbo.Employee AS b
ON a.EmployeeID = b.EmployeeID
Or, I can create a view on the same table in the default database and write it like so:
SELECT b.EmployeeName FROM Invoice AS a
JOIN dbo.vEmployee AS b
ON a.EmployeeID = b.EmployeeID
where dbo.vEmployee is SELECT EmployeeiD, EmployeeName FROM PersonnelDB.dbo.Employee
Is there a performance difference between the two methods? What if the two tables referenced were on different servers? Thanks for any input.
December 17, 2011 at 4:25 am
If the objective is to avoid fully qualified names for tables, use Synonyms instead.
Views should be used to enhance security (visibility) of data. I don’t think you are trying to achieve the same here.
For More:
December 17, 2011 at 4:33 pm
For documentation purposes I would fully qualify the non-local table - that way whoever reads the code in the future will be immediately aware that the table is not local.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.December 17, 2011 at 6:41 pm
Thanks, PaulB and Dev. My goal is to increase readability and maintainability but without doing anything to slow down processing. I will rewrite this code using synonyms and see how it goes.
December 19, 2011 at 7:50 am
Dave-148053 (12/17/2011)
Thanks, PaulB and Dev. My goal is to increase readability and maintainability but without doing anything to slow down processing. I will rewrite this code using synonyms and see how it goes.
Well... using synonyms is not going to improve readability and will certainly add overhead affecting performance - what's wrong with fully qualifying the external table which is the way to accomplish your two stated goals?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.December 19, 2011 at 7:58 am
I’m not so sure that synonyms affect performance. Can you share a bit more about it? I’ve used synonyms few times when I had more then one environment and in each environment I had different server/database name. At those cases I wanted to have the same code in my procedures so I had to use synonyms. Didn’t use it in other situations.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 19, 2011 at 8:20 am
Adi Cohn-120898 (12/19/2011)
I’m not so sure that synonyms affect performance. Can you share a bit more about it? I’ve used synonyms few times when I had more then one environment and in each environment I had different server/database name. At those cases I wanted to have the same code in my procedures so I had to use synonyms. Didn’t use it in other situations.
Synonyms add I/O e.g. RDBMS needs to perform extra I/O to resolve the actual "location and name" of the base object therefore it affects performance negatively.
I have had the opportunity of working on high-end, high-volume environments where the use of synonyms was banned. Let me put it this way, on a small database nobody would care if you use synonyms or not but when every milisecond counts on a high-end database you really don't want to use them.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.December 19, 2011 at 12:17 pm
I think if you have a convention where you write synonym names a certain way, you will increase readability.
December 19, 2011 at 12:27 pm
PaulB-TheOneAndOnly (12/17/2011)
For documentation purposes I would fully qualify the non-local table - that way whoever reads the code in the future will be immediately aware that the table is not local.
A little late, but I don't do this because it creates lots of maintenance touch points. I would create a local view that qualifies the external table, but I'd then point everyone to the local view. That way if I move the other database, I am only modifying my view.
I dislike synonyms for this as the seem more obscure to me, like triggers. A place that I rarely look for tracking down code.
December 19, 2011 at 11:47 pm
Steve Jones - SSC Editor (12/19/2011)
PaulB-TheOneAndOnly (12/17/2011)
For documentation purposes I would fully qualify the non-local table - that way whoever reads the code in the future will be immediately aware that the table is not local.A little late, but I don't do this because it creates lots of maintenance touch points. I would create a local view that qualifies the external table, but I'd then point everyone to the local view. That way if I move the other database, I am only modifying my view.
I dislike synonyms for this as the seem more obscure to me, like triggers. A place that I rarely look for tracking down code.
Exactly !
This is the way I advise to use. On top of that I suggest using naming conventions (e.g. RemoteV....) so it is clear for every user this isn't a local object as usual.
This one especially goes for linked server queries. We've recently has issues where dev teams did write queries using linked servers, of course linq queries or application side coded queries. Guess who had to migrate a server keeping the original server name to avoid problems with these linked servers 🙁
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
December 19, 2011 at 11:59 pm
ALZDBA (12/19/2011)
Steve Jones - SSC Editor (12/19/2011)
PaulB-TheOneAndOnly (12/17/2011)
For documentation purposes I would fully qualify the non-local table - that way whoever reads the code in the future will be immediately aware that the table is not local.A little late, but I don't do this because it creates lots of maintenance touch points. I would create a local view that qualifies the external table, but I'd then point everyone to the local view. That way if I move the other database, I am only modifying my view.
I dislike synonyms for this as the seem more obscure to me, like triggers. A place that I rarely look for tracking down code.
Exactly !
This is the way I advise to use. On top of that I suggest using naming conventions (e.g. RemoteV....) so it is clear for every user this isn't a local object as usual.
This one especially goes for linked server queries. We've recently has issues where dev teams did write queries using linked servers, of course linq queries or application side coded queries. Guess who had to migrate a server keeping the original server name to avoid problems with these linked servers 🙁
IMO if we are not adding any filters in View data, also if we are not joining two or more tables and presenting complex data in view, they are quite unnecessary. Synonyms should be fine there.
I agree with Paul on performance argument but I don’t think OP has same requirements. Also performance degradation would be negligible, if any.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply