Synonym performance in cross-server scenarios vs. OPENQUERY

  • CanuckBuck

    Hall of Fame

    Points: 3887

    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.

  • spaghettidba

    SSC Guru

    Points: 105671

    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.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply