Sorry for the long-winded question, I think its a pretty simple question I just want to make sure I'm asking it right:
We've discovered some fairly big problems with queries run against linked servers - for some reason any data selected from a linked server seems to select the ENTIRE table across the link, before
filtering on the WHERE clause. At least I think this is what's going on. But one thing is for sure, our queries against linked servers are much much slower than they should be, and I think its because
of the amount of unnecessary traffic coming acros the net work for even a small query. Anyway, regardless of the reason, linked servers are definitely causing performance issues for us.
However, in order to keep our stored procs consistant regardless of what database they are on, we want to use the linked server syntax even if the query is running agaisnt a local database.
Suppose I'm connected locally to [MyDatabase], and I have a linked server entry called [MyLinkedServer] that points to itself, ie 127.0.0.1
Then the following 2 queries are the same - they are returning the same results because they are selecting from the same database and table.
select top 10 * from [MyLinkedServer].[MyDatabase].dbo.MyTable
select top 10 * from [MyDatabase].dbo.MyTable
So my question is, does using the linked server version of the query have any performance impact at all? In other words, is SQL server smart enough to see that the linked server is localhost, and therefore just ignore the linked server request and end up actually just running the second query 'under the hood'?
Or does it go through the special linked server logic (whatever that is), ie downloading the entire table before applying the top 10 filter.
I have a feeling that unfortunately the 2 queries above are different, even if MyLinkedServer is actually the local server. I'm having a hard time testing and proving that however. So I was hoping to get an official answer if anyone knows.
We have 2 database servers with replicated data, and we have the same stored procedures installed on both. However we want both versions of the stored procedures to point to just one of the databases. We want the stored procedures to be exactly the same. The best way I can think to accomplish that is to have a linked server called MyLinkedServer on DB1 that points to DB2, and another linked server also called MyLinkedServer on DB2 that points to itself.
Then the stored procedures can be exactly the same on both databases, both referring to MyLinkedServer in any select statements.
The question is, will runing the queries on DB2 be as fast as they should be, or will they unnecessarily incur all the overhead that comes with using a linked server.
Thanks in advance,