If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > Linked Server linking to local database ?

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Feb 2010
Posts: 75
Linked Server linking to local database ?

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.

For example:
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,
Reply With Quote
  #2 (permalink)  
Old
World Class Flame Warrior
 
Join Date: Jun 2003
Location: Ohio
Posts: 12,555
When the data sources for a query are on the same server, that server can leverage indexes and statistics to come up with an efficient query plan.
When they are on different servers, you frequently end up performing table scans, or even transferring the entire table over the network in order to be processed locally.
You can limit the number of records scanned or returned by the remote server by using a WHERE clause to filter the remote dataset (the criteria will be applied by the remote server).
You can also speed things up by selecting only the necessary columns from the remote datasource. Do NOT use "Select *", as this will return every column over the network whether you need it or not.
__________________
If it's not practically useful, then it's practically useless.

blindman
www.chess.com: "sqlblindman"
www.LobsterShot.blogspot.com
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Feb 2010
Posts: 75
Quote:
Originally Posted by blindman View Post
When the data sources for a query are on the same server, that server can leverage indexes and statistics to come up with an efficient query plan.
Thanks very much blindman. It sounds like linked servers have some kind of performance improvement when they are actually a local linked server as opposed to remote ones.

I guess my real question is, are local linked server queries exactly as good as regular local queries, or is there still some kind of overhead imposed by the fact that you used the linked server syntax?
Reply With Quote
  #4 (permalink)  
Old
World Class Flame Warrior
 
Join Date: Jun 2003
Location: Ohio
Posts: 12,555
Off the top of my head, I don't know whether the query optimizer is smart enough to say "Hey!? These two datasources are on the same server! Well, I'll just ignore that fact that one of them is linked server and run the entire thing as a local query...."
Regardless, you'd still save bandwidth.
__________________
If it's not practically useful, then it's practically useless.

blindman
www.chess.com: "sqlblindman"
www.LobsterShot.blogspot.com
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Feb 2010
Posts: 75
Ok fair enough. Thanks again blindman you always seem to be the first (and sometimes only) guy to answer my questions
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On