Azure SQL中如何强制JOIN在远程执行以优化查询性能？

针对本地JOIN远程表导致数据传输量大、查询耗时久的问题，以下几种方法可强制JOIN逻辑在远程服务器执行，仅返回最终结果到本地：

方法1：使用OPENQUERY直接推送完整查询到远程 #

OPENQUERY会将指定查询发送到远程服务器执行，仅返回结果集到本地，是强制远程执行的直接方式。需注意OPENQUERY不支持直接引用本地变量，可通过动态SQL传递参数：

DECLARE @Lft INT = -- 你的参数值
DECLARE @Rgt INT = -- 你的参数值
DECLARE @RemoteQuery NVARCHAR(MAX) = N'
SELECT DISTINCT c.CustomerID, c.Field6
FROM UniLevelTree tree
INNER JOIN Customer c ON tree.CustomerID = c.CustomerID
    AND c.CompanyID = 12345678
    AND c.CustomerStatusTy = 1
INNER JOIN PartnerMilestone milestone ON milestone.PartnerID = c.CustomerKey
INNER JOIN Orders orders ON orders.CustomerID = c.CustomerID
    AND orders.CompanyID = c.CompanyID
    AND orders.OrderDate >= ''2020-01-02T00:00:00.000Z''
    AND orders.OrderDate <= ''2023-04-20T00:00:00.000Z''
INNER JOIN Period p ON p.StartDate >= ''2020-01-01T00:00:00.000Z''
    AND p.StartDate <= ''2023-04-30T00:00:00.000Z''
    AND p.CompanyID = c.CompanyID
WHERE tree.Lft >= ' + CAST(@Lft AS NVARCHAR) + '
    AND tree.Lft <= ' + CAST(@Rgt AS NVARCHAR) + '
    AND tree.CompanyID = 1858
    AND milestone.MilestoneKey IN(''Earnings10000'', ''Title20'', ''TPV50000'', ''PPV10000'')
    AND orders.isCommissionable IN(1)
    AND orders.OrderTy IN(5, 8)
    AND orders.CommissionableVolume BETWEEN 0 AND 5000
    AND milestone.AchievedDate BETWEEN ''2020-01-01T00:00:00.000Z'' AND ''2023-04-30T00:00:00.000Z''
'

SELECT * FROM OPENQUERY(redactedRemoteWebContext, @RemoteQuery)

提示：若存在SQL注入风险，建议结合sp_executesql实现参数化传递，避免直接拼接字符串。

方法2：封装为远程存储过程 #

在远程数据库创建存储过程，将完整的JOIN和筛选逻辑放入其中，本地仅调用该存储过程获取结果：

远程数据库创建存储过程： #

CREATE PROCEDURE GetFilteredCustomers
    @Lft INT,
    @Rgt INT
AS
BEGIN
    SELECT DISTINCT c.CustomerID, c.Field6
    FROM UniLevelTree tree
    INNER JOIN Customer c ON tree.CustomerID = c.CustomerID
        AND c.CompanyID = 12345678
        AND c.CustomerStatusTy = 1
    INNER JOIN PartnerMilestone milestone ON milestone.PartnerID = c.CustomerKey
    INNER JOIN Orders orders ON orders.CustomerID = c.CustomerID
        AND orders.CompanyID = c.CompanyID
        AND orders.OrderDate >= '2020-01-02T00:00:00.000Z'
        AND orders.OrderDate <= '2023-04-20T00:00:00.000Z'
    INNER JOIN Period p ON p.StartDate >= '2020-01-01T00:00:00.000Z'
        AND p.StartDate <= '2023-04-30T00:00:00.000Z'
        AND p.CompanyID = c.CompanyID
    WHERE tree.Lft >= @Lft
        AND tree.Lft <= @Rgt
        AND tree.CompanyID = 1858
        AND milestone.MilestoneKey IN('Earnings10000', 'Title20', 'TPV50000', 'PPV10000')
        AND orders.isCommissionable IN(1)
        AND orders.OrderTy IN(5, 8)
        AND orders.CommissionableVolume BETWEEN 0 AND 5000
        AND milestone.AchievedDate BETWEEN '2020-01-01T00:00:00.000Z' AND '2023-04-30T00:00:00.000Z'
END

本地调用存储过程： #

EXEC redactedRemoteWebContext.dbo.GetFilteredCustomers @Lft = -- 你的参数值, @Rgt = -- 你的参数值

方法3：使用远程上下文嵌套子查询 #

将所有远程表的JOIN逻辑嵌套在远程数据库的上下文子查询中，引导查询优化器将整个子查询推送到远程执行（需确保所有表属于同一远程库）：

SELECT DISTINCT remoteResult.CustomerID, remoteResult.Field6
FROM (
    SELECT c.CustomerID, c.Field6
    FROM redactedRemoteWebContext.UniLevelTree tree
    INNER JOIN redactedRemoteWebContext.Customer c ON tree.CustomerID = c.CustomerID
        AND c.CompanyID = 12345678
        AND c.CustomerStatusTy = 1
    INNER JOIN redactedRemoteWebContext.PartnerMilestone milestone ON milestone.PartnerID = c.CustomerKey
    INNER JOIN redactedRemoteWebContext.Orders orders ON orders.CustomerID = c.CustomerID
        AND orders.CompanyID = c.CompanyID
        AND orders.OrderDate >= '2020-01-02T00:00:00.000Z'
        AND orders.OrderDate <= '2023-04-20T00:00:00.000Z'
    INNER JOIN redactedRemoteWebContext.Period p ON p.StartDate >= '2020-01-01T00:00:00.000Z'
        AND p.StartDate <= '2023-04-30T00:00:00.000Z'
        AND p.CompanyID = c.CompanyID
    WHERE tree.Lft >= @Lft
        AND tree.Lft <= @Rgt
        AND tree.CompanyID = 1858
        AND milestone.MilestoneKey IN('Earnings10000', 'Title20', 'TPV50000', 'PPV10000')
        AND orders.isCommissionable IN(1)
        AND orders.OrderTy IN(5, 8)
        AND orders.CommissionableVolume BETWEEN 0 AND 5000
        AND milestone.AchievedDate BETWEEN '2020-01-01T00:00:00.000Z' AND '2023-04-30T00:00:00.000Z'
) AS remoteResult

验证方式：执行查询后查看执行计划，若显示Remote Query运算符且仅返回最终结果集，则说明JOIN已在远程执行。

关键注意事项 #

• 所有涉及的表必须属于同一远程数据库实例，否则无法将JOIN逻辑完全推送到远程。
• 确保本地服务器对远程数据库有足够的查询权限，包括表访问和存储过程执行权限。
• 动态SQL传递参数时需注意防范SQL注入风险，优先使用参数化查询方式。

内容的提问来源于stack exchange，提问作者sav