如何從連結伺服器查詢數據，並將其傳遞參數以進行過濾？

我有一個非常大的查詢，需要在多個數據庫上執行，並將結果附加到臨時表並返回。

基本語法如下所示：

INSERT INTO #tmpTable (Id, ...)

SELECT T1.Id, ...
FROM Server.Database.dbo.Table1 as T1
INNER JOIN #tmpIds as T ON T1.Id = T.Id

INNER JOIN Server.Database.dbo.Table2 as T2 ON T1.Id = T2.Id
INNER JOIN Server.Database.dbo.Table3 as T3 ON T2.Id = T3.Id
LEFT OUTER JOIN Server.Database.dbo.Table4 as T4 ON T3.Id = T4.Id
LEFT OUTER JOIN Server.Database.dbo.Table5 as T5 ON T4.Id = T5.Id
LEFT OUTER JOIN Server.Database.dbo.Table6 as T6 ON T5.Id = T6.Id

如果在單個伺服器上本地執行，查詢會快速執行，但是如果它是從使用上述 4 部分名稱的連結伺服器執行的，則需要很長時間才能執行。

問題似乎是它首先查詢連結伺服器以獲取未過濾的結果集，然後將其加入#tmpIds本地伺服器上的表，這使得查詢需要很長時間才能執行。

如果我對 Ids 進行硬編碼以過濾連結伺服器上的結果集，例如

SELECT T1.Id, ...
FROM Server.Database.dbo.Table1 as T1
-- INNER JOIN #tmpIds as T ON T1.Id = T.Id
INNER JOIN Server.Database.dbo.Table2 as T2 ON T1.Id = T2.Id
INNER JOIN Server.Database.dbo.Table3 as T3 ON T2.Id = T3.Id
LEFT OUTER JOIN Server.Database.dbo.Table4 as T4 ON T3.Id = T4.Id
LEFT OUTER JOIN Server.Database.dbo.Table5 as T5 ON T4.Id = T5.Id
LEFT OUTER JOIN Server.Database.dbo.Table6 as T6 ON T5.Id = T6.Id

WHERE T1.Id IN (1, 2, 3)

它在幾秒鐘內快速執行。

有沒有辦法執行此查詢，以便在將#tmpId結果集返回到本地伺服器之前先按表過濾來自連結伺服器的查詢結果集？

一些注意事項

• 該查詢非常龐大且複雜，由於導致維護噩夢，動態 SQL 不是一個可行的選擇。

我願意接受有關如何將動態 SQL 用於其他方面的建議，例如執行儲存過程或 UDF，如果有辦法通過連結伺服器執行此操作（嘗試了幾種不同的方法，例如sp_executeSQL、OPENROWSET和OPENQUERY，但是那些都失敗了）。

• 因為它使用 4 部分命名約定，所以我不能在遠端伺服器上使用 UDF
• 分佈式事務被禁用，因此以下不起作用

INSERT INTO #table 
EXEC Server.Database.dbo.StoredProcedure @ids

性能問題實際上與LEFT OUTER JOIN表格有關。如果我將它們更改為INNER JOIN，或者如果我從列中排除了它們的數據SELECT，則查詢執行良好。

我最終做的是在連結伺服器上創建一個包含我想要的所有數據的連結伺服器，然後簡單地從帶有表View的主伺服器加入它。#tmpIds

我不認為這會起作用，因為我認為在過濾之前加入所有內容並將其拉到第二台伺服器與我現在所做的相同，並且會導致相同的性能問題，但令人驚訝的是，這似乎並沒有就是這樣。

CREATE VIEW MyView 
AS

SELECT T1.Id, T2.ColA, ...
FROM Table1 as T1
INNER JOIN Table2 as T2 ON T1.Id = T2.Id
INNER JOIN Table3 as T3 ON T2.Id = T3.Id
LEFT OUTER JOIN Table4 as T4 ON T3.Id = T4.Id
LEFT OUTER JOIN Table5 as T5 ON T4.Id = T5.Id
LEFT OUTER JOIN Table6 as T6 ON T5.Id = T6.Id

GO

和

INSERT INTO #tmpTable (Id, ...)

SELECT T1.Id, T1.ColA, ...
FROM Server.Database.dbo.MyView as T1
INNER JOIN #tmpIds as T ON T1.Id = T.Id

所有連接的列都被正確索引，但是根據這個答案

儘管遠端伺服器上的表可能存在索引，但 SQL 可能無法利用它們，但它可以建構一個確實利用索引的本地查詢計劃。

而這個

讓連結伺服器盡可能做。

SQL Server 不可能優化連結伺服器上的查詢，即使是另一個 SQL Server

所以我猜測用於查詢的查詢計劃沒有使用定義的索引，並且 SQL Server 正在為LEFT OUTER JOIN表生成一個糟糕的查詢計劃。

您是否嘗試過 FORCE ORDER 查詢提示？它強制編譯器在優化時保持查詢中列出的連接順序。

SELECT T1.Id, ...
FROM Server.Database.dbo.Table1 as T1
INNER JOIN #tmpIds as T ON T1.Id = T.Id

INNER JOIN Server.Database.dbo.Table2 as T2 ON T1.Id = T2.Id
INNER JOIN Server.Database.dbo.Table3 as T3 ON T2.Id = T3.Id
LEFT OUTER JOIN Server.Database.dbo.Table4 as T4 ON T3.Id = T4.Id
LEFT OUTER JOIN Server.Database.dbo.Table5 as T5 ON T4.Id = T5.Id
LEFT OUTER JOIN Server.Database.dbo.Table6 as T6 ON T5.Id = T6.Id
OPTION (FORCE ORDER)

編輯： 鑑於 FORCE ORDER 不起作用，你有沒有想過做這樣的事情：

WHERE T1.Id IN (SELECT Id FROM #tmpIds)

第二次編輯： 再試一次，雖然這個有點複雜。

你能做這樣的事情：

在遠端伺服器上創建一個永久的“臨時”表

CREATE TABLE tmpTable1 (Id INT)

然後（仍然在遠端伺服器上）創建一個視圖

CREATE VIEW queryView AS
SELECT Table1.* 
FROM Table1
JOIN tmpTable1
   ON Table1.Id = tmpTable1.Id

然後在你的“家”實例上的過程中

DELETE FROM Server.Database.dbo.tmpTable1
INSERT INTO Server.Database.dbo.tmpTable1 VALUES
SELECT * FROM #tmpIds

然後在您的查詢中加入Server.Database.dbo.queryView

引用自：https://dba.stackexchange.com/questions/41621