What is the overhead of a cross server request compared to a native request?

Question

If I connect to a specific server on SQL Server and I use a query that refers to a table on another server, it slows down the query and if so, how much (for example, if I join the tables in the table the server I'm currently on)? I ask because I want to know if it will be even more efficient to import this table to the server I'm working on than to cross-query the server.

Answer 1

I would say it depends. Yes, probably not the answer you wanted, but it depends on the situation. Using linked servers is really cost-related, especially depending on the number of rows you are trying to return and the types of queries you are trying to run. You should be able to view your execution plan in your queries and see how much is used to get into the tables of related servers. This, as well as the time required to return the results, will probably help determine if necessary.

As for the local placement of tables, this also depends. Do you need modern data or static data? If static, then importing the table would not be a bad idea. If this data is constantly changing and you need changes, this may not be the best solution. You can always consider creating SSIS packages for this at night, but again, it just depends.

Good luck.

Answer 2

As sgeddes mentions, it depends.

My own experience with server related queries was that they are rather slow for large tables. Depending on how you write the query, predicates can be evaluated on the server where the statements are running (very likely if you join them), which means that in any case, you can transfer the entire table to this server and then filter result. And this is definitely bad for performance.