Alternate to SQL Server Linked Server

Question

I am trying to build a program that compares 2 database servers that have exact table but in some table have additional column. I am using linked server to connect these 2 database servers.

But I found a problem, when I try to compare some data the connection is mostly timeout. And when I check Activity Monitor and Execution plan, more than 90% is in remote query - this makes comparing 1 record that has 5 child entries run for 5-7 minutes.

This is a sample query that I try to run.

Select 
    pol.PO_TXN_ID, pol.Pol_Num 
From 
    ServerA.InstanceA.dbo.POLine pol  
Where 
    not exist (Select 1 
               From ServerB.InstanceA.dbo.POLine pol2 
               where pol.PO_TXN_ID = pol2.PO_TXN_ID 
                 and pol.Pol_Num = pol2.Pol_Num)

I tried using OPENROWSET, but our administrator does not permit to install it on the production server.

Is there any alternative that I can use to optimize my query instead using linked server?

Preben Huybrechts · Accepted Answer · 2020-09-22 07:05:49Z

3

Options:

OpenQuery() / 4 part naming with temp tables.
ETL (eg: SQL Server Integration Services)

The problem with linked servers especially with 4 part naming like in your example:

The query engine doesn't know how to optimize it. He can't access statistics on the linked servers
Resulting in doing full table scans, pulling all the data to the source SQL server and then processing it. (High network IO, Bad execution plans, resulting in long running queries)

Option 1

Create a temp table (preferably with indexes)
Query the linked server with OPENQUERY and preferably a filter condition. eg:

CREATE TABLE #MyTempTable(Id INT NOT NULL PRIMARY KEY, /*Other columns*/)
INSERT INTO #MyTempTable(Id, , /*Other columns*/)
SELECT *
FROM OPENQUERY(ServerA, 'SELECT Id,  /*Other columns*/ FROM Table WHERE /*Condition*/')

Use the temp table(s) to do your calculation.

Still needs at least 1 linked server

OPENQUERY has better performance when your database is not a SQL Server (e.g. Postgres, MySql, Oracle,...) as the query is executed on the linked server instead of pulling all the data to the source server.

Option 2

You can use an ETL tool like SQL Server Integration Services (SSIS)

Load the data from the 2 servers
Use a Slowly changing dimension or lookup component to determine the differences.
Insert/update what you want/need

No linked servers are needed, SSIS can connect to the databases directly

edited Sep 22, 2020 at 7:05

answered Sep 22, 2020 at 6:47

Preben Huybrechts

6,1972 gold badges30 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user8533094 Over a year ago

Thank you for your comment, i tried the openquery and it works faster than linked server

Preben Huybrechts Over a year ago

Please consider upvoting / accepting the answer if it solved your question.

Collectives™ on Stack Overflow

Alternate to SQL Server Linked Server

1 Answer 1

Option 1

Option 2

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Option 1

Option 2

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related