Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sent_sara
Constraint Violating Yak Guru
377 Posts |
 Posted - 2009-02-17 : 13:27:42
|
| Based on the listed linked server definitions, what are the potential performance differences between the following 3 SQL statements? Assume that all statements are run on Server A and that DeptID is indexed on every table.Statement 1: select * from CustData..Cust c join CustData..Dept d on (c.DeptID = d.DeptID)Statement 2: select * from CustData..Cust c join LinkServerA.CustData..Dept d on (c.DeptID = d.DeptID)LinkServerA: Linked server definition, defined on Server A, that points back to itselfStatement 3: select * from CustData..Cust c join LinkServerB.CustData..Dept d on (c.DeptID = d.DeptID)LinkServerB: Linked server definition, defined on Server A, that points to remote Server B |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-02-17 : 13:39:57
|
| Queries running in Linked server will not take advantage of index. It goes with REMOTE SCAN in execution plan. |
 |
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2009-02-17 : 13:41:52
|
| I think your first 2 queries will behave alike, The third one wouldn't use indexes because of remote query call. Open up the execution plan and it should be clear. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-02-17 : 13:47:53
|
| It is bad practice to create linked server point to itself. Only it is need when storing results of Store proc to temp table(when columns are unknown). |
|
|
|
sent_sara
Constraint Violating Yak Guru
377 Posts |
Posted - 2009-02-17 : 13:50:26
|
txs sakets_2000 and sodeepquote:
Originally posted by sodeep
It is bad practice to create linked server point to itself. Only it is need when storing results of Store proc to temp table(when columns are unknown).
|
|
|
|
|
|
|
linked server performance