如何提高對連結伺服器中相同表的 SELECT 查詢的性能

我有一個SELECT對連結伺服器的查詢。它看起來與此相似

SELECT     @Var1 = (SELECT  COL1
                   FROM    [LinkedServer].[dbo].[TableA]
                   WHERE   <Condition1>),
          @Var2 = (SELECT  COL1
                   FROM    [LinkedServer].[dbo].[TableA]
                   WHERE   <Condition2>),
          @Var3 = (SELECT  COL1
                   FROM    [LinkedServer].[dbo].[TableA]
                   WHERE   <Condition3>),
          ...
FROM       [LinkedServer].[dbo].[TableB]
WHERE      <Condition4>

所以這個連結伺服器中的同一個表有多個SELECT語句。有沒有辦法提高性能？複製TableA到本地數據庫伺服器中的臨時表是一個壞主意嗎？尺寸TableA很大。

$$ UPDATE1 $$：更新了我的查詢

SELECT      VAR_1, 
           (SELECT  SOL
           FROM     [LinkedServer].[dbo].[TableSol]
           WHERE    COL_1 = [TableA].COL_1
           AND      COL_2 = [TableA].COL_2
           AND      COL_A = [TableA].COL_A) AS VAR_2,
           (SELECT  SOL
           FROM     [LinkedServer].[dbo].[TableSol]
           WHERE    COL_1 = [TableA].COL_3
           AND      COL_2 = [TableA].COL_4
           AND      COL_A = [TableA].COL_A) AS VAR_3,
           (SELECT  SOL
           FROM     [LinkedServer].[dbo].[TableSol]
           WHERE    COL_1 = [TableA].COL_5
           AND      COL_2 = [TableA].COL_6
           AND      COL_A = [TableA].COL_A) AS VAR_4,
FROM        [LinkedServer].[dbo].[TableA]
INNER JOIN  [LinkedServer].[dbo].[TableB] ON [TableA].COL_10 = [TableB].COL_11

我花了 15 秒來執行上述查詢。我改為使用交叉表，如下所示：

SELECT      VAR_1
           SOL.VAR_2,
           SOL.VAR_3,
           SOL.VAR_4,
FROM        [LinkedServer].[dbo].[TableA]
INNER JOIN  [LinkedServer].[dbo].[TableB] ON [TableA].COL_10 = [TableB].COL_11
CROSS APPLY (SELECT 
               CASE 
                   WHEN COL_1 = [TableA].COL_1 AND COL_2 = [TableA].COL_2 THEN SOL
                   ELSE ''
               END AS VAR_2,
               CASE 
                   WHEN COL_1 = [TableA].COL_3 AND COL_2 = [TableA].COL_4 THEN SOL
                   ELSE ''
               END AS VAR_3,
               CASE 
                   WHEN COL_1 = [TableA].COL_5 AND COL_2 = [TableA].COL_6 THEN SOL
                   ELSE ''
               END AS VAR_4
            FROM    [LinkedServer].[dbo].[TableSol]
            WHERE    COL_A = [TableA].COL_A
           ) AS SOL (VAR_2, VAR_3, VAR_4)

但是我花了更長的時間（> 10分鐘，我只是停下來，因為它沒有完成）。我錯過了什麼？

可以使用交叉表重寫查詢，以避免多次命中 TableA：

DECLARE @TableA TABLE (col1 int, col2 int);
DECLARE @TableB TABLE (col3 int, col4 int);

INSERT INTO @TableA 
VALUES 
   (1,1),
   (2,2),
   (3,3),
   (4,4);

INSERT INTO @TableB
VALUES 
   (1,1),
   (2,2),
   (3,3),
   (4,4);

DECLARE @Var1 int, @Var2 int, @Var3 int;

SELECT     @Var1 = CA.var1,
          @Var2 = CA.var2,
          @Var3 = CA.var3
FROM @TableB AS TB
CROSS APPLY (
   SELECT 
       SUM(CASE WHEN col2 = 1 THEN col1 END) AS var1,
             -- ^^ This is <Condition1>
       SUM(CASE WHEN col2 = 2 THEN col1 END) AS var2,
             -- ^^ This is <Condition2>
       SUM(CASE WHEN col2 = 3 THEN col1 END) AS var3
             -- ^^ This is <Condition3>
   FROM @TableA AS TA
) AS CA(var1, var2, var3)
WHERE TB.col3 > 2 -- <Condition4>

SELECT @Var1, @Var2, @var3

這應該使它更快。如果沒有，請發布執行計劃，以便我們進一步調查。

您可以使用 4 部分名稱表示法在連結伺服器上將查詢作為傳遞查詢執行，並且sp_executesql：

DECLARE @var1 int, @var2 int, @var3 int, @var4 int;

EXEC [linkedserver].[databasename].sys.sp_executesql 
   N'
SELECT      @var1 = VAR_1, 
           @var2 = (
           SELECT  SOL
           FROM     [dbo].[TableSol]
           WHERE    COL_1 = [TableA].COL_1
           AND      COL_2 = [TableA].COL_2
           AND      COL_A = [TableA].COL_A),
           @var3 = (
           SELECT  SOL
           FROM     [dbo].[TableSol]
           WHERE    COL_1 = [TableA].COL_3
           AND      COL_2 = [TableA].COL_4
           AND      COL_A = [TableA].COL_A),
           @var4 = (
           SELECT  SOL
           FROM     [dbo].[TableSol]
           WHERE    COL_1 = [TableA].COL_5
           AND      COL_2 = [TableA].COL_6
           AND      COL_A = [TableA].COL_A),
FROM        [dbo].[TableA]
INNER JOIN  [dbo].[TableB] 
   ON [TableA].COL_10 = [TableB].COL_11
   ',
   N'@var1 int OUTPUT, @var2 int OUTPUT, @var3 int OUTPUT, @var4 int OUTPUT',
   @var1 OUTPUT, 
   @var2 OUTPUT,
   @var3 OUTPUT, 
   @var4 OUTPUT;

SELECT @var1, @var2, @var3, @var4;

如果您不需要分配給變數，則可以使用以下語法：

EXEC [linkedserver].[databasename].sys.sp_executesql 
   N'
SELECT      VAR_1, 
           (
           SELECT  SOL
           FROM     [dbo].[TableSol]
           WHERE    COL_1 = [TableA].COL_1
           AND      COL_2 = [TableA].COL_2
           AND      COL_A = [TableA].COL_A),
           (
           SELECT  SOL
           FROM     [dbo].[TableSol]
           WHERE    COL_1 = [TableA].COL_3
           AND      COL_2 = [TableA].COL_4
           AND      COL_A = [TableA].COL_A),
           (
           SELECT  SOL
           FROM     [dbo].[TableSol]
           WHERE    COL_1 = [TableA].COL_5
           AND      COL_2 = [TableA].COL_6
           AND      COL_A = [TableA].COL_A),
FROM        [dbo].[TableA]
INNER JOIN  [dbo].[TableB] 
   ON [TableA].COL_10 = [TableB].COL_11
   '

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