SQL Server Performance

What are the performance issues while using linked server by using join quries?

Discussion in 'Getting Started' started by kvganeshbabu83, Jun 2, 2008.

  1. kvganeshbabu83 New Member

    What are the performance issues while using linked server by using join quries?
  2. MichaelB Member

    If you join a linked server table to a local table the performance will completely stink.
    It is better (depending on size) to just select the table from the linked server into a local temp or table variable and use that in the join. that will keep the data local and will then increase performance. You must test this because the movement of that table COULD be as long or longer than the join performance. I don't think this is normally the case though.
  3. kvganeshbabu83 New Member

    Thank you Mr Michael for Reply. I will try the above steps. But i have more than 60 lakhs of records in that server table. So i would do inner join from that table. I know that this will not good performance. But how can i copy that large data table to local. It too take much more time even query.
  4. MichaelB Member

    You can also - up to you- move the smaller dataset to the server that has the larger dataset and execute it there.. or use openquery and move the small data source over to the larger dataset's server and execute it there using open query from the smaller table server.. u know? I hope this is clear.
    lets say table1 is large on server1 and table2 is small on server2
    Lets say you want to execute your calling program on server2 then you can do this there
    this is psudocode.. so not syntax exact..k?
    ******on server 2*****
    declare @strSQL varchar(1000)
    set @strSQL = '
    select * into #test from server2.dbname.dbo.table2 as t --this moves the small dataset over to server1
    select * from #test t INNER JOIN table1 as o on o.id = t.id' --now the join
    exec server1.dbo.sp_executesql @strSQL --doing this allows us to run on server 2

    I hope this make sense. always execute on the server with the smaller server or use sp_executesql to run it wherever it needs to be run.

Share This Page