[Solved] Better Way of Doing SQL Server Remote Query?
Looking to automatically optimize YOUR SQL query? Start for free.

EverSQL Database Performance Knowledge Base

Better Way of Doing SQL Server Remote Query?

Database type:

I have two SQL Server instances and I do a lot of remote querying of a database on one server from another server. Like this query on server1.database1:

select T1.id
from server1.database1.dbo.table1 T1
inner join server2.datbase2.dbo.table2 T2
on T1.id = T2.id

I've inherited this code from someone else and was wondering if there's a better (faster) way of doing this? I mean, is there a way I could create an exact replica copy of server2.databse2.dbo.table2 on server1.database1.dbo that updates itself and keeps itself current in real-time?

Microsoft SQL Server Standard Edition (64-bit) Version 10.0.4000.0

EDIT: Actually, what I do now in this scenario is, if I can, I use open query and with(nolock) to grab the smallest dataset I need, and I put that in a temp table. And I set up the "id" column to be a unique clustered index, so that it can join quickly on whatever I'm joining on server 1.

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
CREATE INDEX table2_idx_id ON dbo.table2 (id);
The optimized query:
SELECT
        T1.id 
    FROM
        server1.database1.dbo.table1 T1 
    INNER JOIN
        server2.datbase2.dbo.table2 T2 
            ON T1.id = T2.id

Related Articles



* original question posted on StackOverflow here.