3

If this has been answered elsewhere, please post a link to it, yell at me, and close this question. I looked around and saw similar things, but didn't find exactly what I was looking for.

I am currently writing several stored procedures that require data from another database. That database could be on another server or the same server, it just depends on the customer's network. I want to use a Synonym so that if the location of the table that I need data from changes, I can update the synonym once and not have to go back in to all of the stored procedures and update their references.

What I want to know is what the best approach is with a synonym. I read a post on SO before that said there was a performance hit when using a view or table (especially across a linked server). This may be due to SQL Server's ability to recognize indexes on tables when using synonyms. I can't find that post anymore or I would post a link to it. It was suggested that the best approach is to create a synonym for a stored procedure, and load the resulting data in to a memory or temp table.

I may not have my facts straight on that, though, and was hoping for some clarification. From what I can tell, creating and loading data in to memory tables generally accounts for a large percentage of the execution plan. Is using a stored procedure worth the extra effort of loading the data in to a table over just being able to run queries against a view or table? What is the most efficient way to get data from another database using a synonym?

Thanks!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
bsayegh
  • 990
  • 6
  • 17
  • You can use linked servers to do this however generally this sort of remote queries are not a good idea. – sam yi Dec 23 '13 at 15:14
  • Unfortunately I don't think I have a choice but to use a linked server. My question is more about what the best way to get the data from the other database/server would be. Synonym for a stored proc that gets the data from the table/tables or a synonym for the tables themselves? – bsayegh Dec 23 '13 at 15:33
  • 1
    FYI: there's no such thing as a "memory table". OK, well yes, there technically *is* in SQL Server 2012, but they are *very* specialized and not what people usually mean when they talk about "in-memory tables" in SQL Server. *Usually* what they mean in a table variable, which for various reasons is widely believed to be an in-memory table. It isn't. – RBarryYoung Dec 23 '13 at 15:55
  • Yeah, memory table is a term that I use incorrectly and I am somewhat aware of it, but I let it slip sometimes anymore. My understanding is that temp tables and table variables are all stored in TempDB. Is that correct? – bsayegh Dec 23 '13 at 16:10

2 Answers2

4

Synonyms are just defined alias's to make redirection easier, they have no performance impact worth considering. And yes, they are advised for redirection, they do make it a lot easier.

What a synonym points to on the other hand can have a significant performance impact (this has nothing to do with the synonym itself).

Using tables and views in other databases on the same server-instance has a small impact. I've heard 10% quoted and I can fairly say that I have never observed it to be higher than that. This impact is mostly from reductions in the optimizers efficiency, as far as I can tell.

Using objects on other server-instances, whether through linked server definitions, or OpenQuery is another story entirely. These tend to be much slower, primarily because of the combined effects of MS DTC and the optimizer deciding to do almost no optimizations for the remote aspects of a query. This tends to be bearable for small queries and small remote tables, but increasingly awful the bigger the query and/or remote table is.

Most practitioners eventually decide on one of two fixes for this problem, either 1) If it is a table, then just copy the remote table rows to a local #temp table first and then query on that, or, 2) if it is more complex, then write a stored procedure on the remote server and then execute it with INSERT INTO..EXECUTE AT, to retrieve the remote info.

As for how to use/organize your synonyms, my advice would be to create a separate owner-schema in your database (with an appropriate name like [Remote]) and then put all of your Synonyms there. Then when you need to redirect, you can write a stored procedure that will automatically find all of the synonyms pointing to the old location and change them to the new location (this is how I do it). Makes it a lot easier to deal with location/name changes.

RBarryYoung
  • 55,398
  • 14
  • 96
  • 137
  • Thanks! That was a great answer! The remote table that I need data from can get pretty large depending on the customer, so would you think option 1 is a practical solution? – bsayegh Dec 23 '13 at 16:04
  • @bsayegh Try it and see. It's hard to predict so the best course is usually to try the simplest/easiest way first. – RBarryYoung Dec 23 '13 at 17:51
  • 1
    Using a smaller subset of data, it seems like the first option was a little faster. Im guessing with more data the difference would be more noticeable. Thank you for your suggestions! – bsayegh Dec 24 '13 at 15:30
0

Choosing Option 1 or 2 depends on the nature of your query. If you can retrieve the data with a relatively simple Select with a good Where clause to restrict the number of rows then Option 1 is generally the best choice. DO NOT JOIN local and remote tables. Pull the remote data to a local #temp table and join the local tables on that temp table in a separate query.

If the query is more complex with multiple joins and/or complex Where conditions then retrieving the data into a local #temp table via a Remote procedure call is generally the best choice. Again, DO NOT JOIN local and remote procedures and minimize the number/size of the parameters to the remote procedure.

The balance point between "simple Select" and "complex Select" is a matter of knowing you data and testing.

HTH :)

Ray
  • 11
  • 1