April 16, 2015 at 10:41 am
I'm using SQL Server 2008R2. I am developing a database which requires access to data from other servers. So far I have been creating views using OPENQUERY (where there's a performance benefit) to select specifically the columns I want. Generally, for my purposes, I find these OPENQUERY based views to perform better (some times significantly so) to simple SELECT <COLUMNS> FROM <SERVER>.<DATABASE>.<SCHEMA>.<TABLE> WHERE <Where clause Statements> format views. My understanding is that this is because an OPENQUERY "pushes" the query processing to the remote server and simply returns the final result set to the local server i.e. there's no cross-server join/synchronization going on.
My question is, if I were to create a Synonym for a table object on the remote server, where does the processing happen if I query from this Synonym or create a join with this synonym to a table in my local database?
Essentially, I am trying to understand if there are any "hidden gotcha's" primarily from a performance perspective, to using synonyms.
Thanks in advance for any insight that you can provide.
April 16, 2015 at 2:14 pm
The synonym points to the remote server and it won't do anything different from using the four-part name (server.database.schema.table) directly. It's exactly the same from a performance standpoint.
-- Gianluca Sartori
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply