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 |
|
haroon2k9
Constraint Violating Yak Guru
328 Posts |
 Posted - 2010-02-01 : 05:20:31
|
| Hi,we had some performance issue like,Server1 and server2.we created a linked server for server1 which needed for server2 as to pull the data to server2 from server1.okwe pull the data for server2 from server1 like we wrote a sp asmethi:1(select colm1,col2,clo3...server1.dbname.dbo.tblname t1 innerjoin server1.dbname.dbo.tblname2 t2 on t1.id=t2.id)this is what we have created a sproc in server2.Found Data Retrival was very slow.methid :2so we have changed like..created a sp in server1 like(select colm1,col2,clo3....dbo.tblname t1 innerjoin dbo.tblname2 t2 on t1.id=t2.id)in server 2.created a sp as to execute server1.dbname.dbo.sprocnameperformance was ok.could ypu please poroviode me on technical stuffs.i was wondeering,how the second one was performance good compared to method 1..2.do you have anyother alternate,pl suggest. |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-02-01 : 05:30:24
|
| In the first query your join is performed on server2 which means that it has to transfer all data from both tables to server2 before doing the join and getting the correct result, while in the second one the join is performed directly on server1 before transfering the result set only back to server2.- LumbagoIf the facts don't fit the theory, change the facts. Albert Einstein |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-01 : 05:38:49
|
| My guess is that in first case it actually fetches the data from included tables in server1 and all that data is then loaded into a temporary tables and the join is done locally in server2. Since join happens between the temporary tables locally, it wont be having any indexes you may have on source server tables. While in second case the procedure when executed does actual join in server1 itself between actual tables (with right indexes and updated statistics) and it just returns resultset alone to server2 causing it to perform better |
|
|
|
|
|
|
Linked Server