Home Dashboard Directory Help
Search

Linked Server performance should be vigorously improved by BetterToday


Status: 

Closed
 as Won't Fix Help for as Won't Fix


1
0
Sign in
to vote
Type: Suggestion
ID: 778967
Opened: 2/11/2013 7:36:00 AM
Access Restriction: Public
0
Workaround(s)
view

Description

Accessing data through a linked server is very slow, mostly because data is downloaded to the local server first in order to evaluate predicates.

Particularly the following, most common, linked server scenario should be improved:

* A view is created to join two (or more) 1:1 related tables, one from the local server and one from the linked server.
* The view's JOIN predicate joins both tables on their primary keys.

Currently, updating data by updating the view is very slow, particularly because ALL data from the linked server seems to be downloaded to the local SQL Server instance first before updating the rows, which is unnecessary.

I suggest to have SQL Server handle SELECT, UPDATE, INSERT and DELETE statements for views, which are joining a number of 1:1 tables on their primary keys, in a way that SQL Server performs these in a n-step process, one at each instance, within a distributed transaction.

This would result in SQL Server avoiding to first download all of the remote tables' data from the linked SQL Server instances down to the local server.


Here's a sample:

LocalServer:
---------------
CREATE TABLE t0 (ID0 UNIQUEIDENTIFIER PRIMARY KEY, Col0 INT NOT NULL)

LinkedServer1:
---------------
CREATE TABLE t1 (ID1 UNIQUEIDENTIFIER PRIMARY KEY IDENTITY, Col1 INT NOT NULL)

LinkedServer2:
---------------
CREATE TABLE t2 (ID2 UNIQUEIDENTIFIER PRIMARY KEY, Col2 INT NOT NULL)


LocalServer:
---------------
CREATE VIEW Tables AS
SELECT *
FROM t0
INNER JOIN t1 ON t0.ID0 = t1.ID1
INNER JOIN t2 ON t1.ID1 = t2.ID2


LocalServer:
---------------
INSERT INTO Tables (Col0, Col1, Col2) VALUES (0, 1, 2)
---------------
Since there is an IDENTITY primary key table at LinkedServer1, the ID column should get auto generated there and then replicated to the other two tables referred to by the 1:1 join clause. Except for the new IDENTITY value, no data is loaded from any of the other servers.

So this is supposed to be getting executed:

INSERT INTO LinkedServer1...t1 (Col1) VALUES (1)
@@Identity = LinkedServer1...t1.@@IDENTITY
INSERT INTO t0 (ID0, Col0) VALUES (@@Identity, 0)
INSERT INTO LinkedServer2...t2 (ID2, Col2) VALUES (@@Identity, 2)


LocalServer:
---------------
DELETE Tables WHERE ID2 = {...}
---------------
Since all three tables are joined by their primary keys, one row gets deleted from each of the three tables. No data is loaded from any of the other servers.

So this is supposed to be getting executed:

DELETE t0 WHERE ID0 = {...}
DELETE LinkedServer1...t1 WHERE ID1 = {...}
DELETE LinkedServer2...t2 WHERE ID2 = {...}


LocalServer:
---------------
UPDATE Tables SET Col1 = 5 WHERE ID2 = {...}
---------------
Since all three tables are joined by their primary keys, only the table at LinkedServer1 gets updated by using the primary key to select the row. No data is loaded from any of the other servers.

So this is supposed to be getting executed:

UPDATE LinkedServer1...t1 SET Col1 = 5 WHERE ID1 = {...}


LocalServer:
---------------
UPDATE Tables SET Col1 = 5 WHERE Col2 = 2
---------------
Since all three tables are joined by their primary keys, only the primary keys of LinkedServer2 are downloaded matching the predicate. Then those primary keys are used to update the data at LinkedServer1.

So this is supposed to be getting executed:

SELECT ID2 INTO #temp FROM LinkedServer2...t2 WHERE Col2 = 2
UPDATE LinkedServer1...t1 SET Col1 = 5
FROM LinkedServer1...t1
INNER JOIN #temp ON ID1 = ID2



So, in general: If UNIQUE/PRIMARY KEYS are involved in creating a view spanning several SQL Server instances, SQL Server should intelligently try to minimize the traffic between all the linked server instances.

RFC
Details
Sign in to post a comment.
Posted by Microsoft on 2/13/2013 at 12:28 PM
Thanks for feedback, we will look into this- Vishal
Sign in to post a workaround.