CODEWITHSUNDEEP
sql-serverlinked-server
user8533094
user8533094

Reputation: 55

Alternate to SQL Server Linked Server

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?

Upvotes: 3

Views: 6295

Answers (1)

Preben Huybrechts
Preben Huybrechts

Reputation: 6141

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

  1. Create a temp table (preferably with indexes)
  2. 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*/')
  1. 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)

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

Upvotes: 3

Related Questions