SQL关联查询执行耗时优化求助：特定子查询性能提升

核心优化点 #

1. 消除SUBSTRING导致的索引失效 #

原查询中WHERE SUBSTRING(P.[ProductNo], 1, 2) IN ('43', '72')会让ProductNo字段的索引无法被有效利用，替换为前缀匹配写法，同时建议在远程服务器的PRODUCT表上创建ProductNo的前缀索引：

-- 替换WHERE条件
WHERE P.[ProductNo] LIKE '43%' OR P.[ProductNo] LIKE '72%'

-- 在远程服务器执行，创建前缀索引（如果权限允许）
CREATE NONCLUSTERED INDEX IX_PRODUCT_ProductNo_Prefix ON [FlexNet_prd].[dbo].[PRODUCT] ([ProductNo]) INCLUDE ([ID])

如果无法创建索引，也可以用LEFT(P.[ProductNo], 2) IN ('43', '72')，部分场景下优化器会比SUBSTRING更友好。

2. 简化窗口函数逻辑，减少数据处理量 #

原查询用ROW_NUMBER()分组取最大Quantity的行，可以改用TOP 1 WITH TIES结合排序，避免子查询的额外开销，同时确保过滤逻辑先执行：

SELECT DISTINCT LEFT(P.[ProductNo], 2) AS [Code]
FROM [LESMESPRD].[FlexNet_prd].[dbo].[ORDER_DETAIL] AS OD
INNER JOIN [LESMESPRD].[FlexNet_prd].[dbo].[WIP_COMPONENT] AS WC 
    ON WC.[WiporderNo] = OD.[OrderNo]  
    AND WC.[WipOrderType] = OD.[OrderType]  
    AND WC.[Active] = 1 
INNER JOIN [LESMESPRD].[FlexNet_prd].[dbo].[COMPONENT] AS C 
    ON C.[ID] = WC.[ComponentID]
INNER JOIN [LESMESPRD].[FlexNet_prd].[dbo].[PRODUCT] AS P 
    ON P.[ID] = C.[ProductID]
WHERE P.[ProductNo] LIKE '43%' OR P.[ProductNo] LIKE '72%'
ORDER BY ROW_NUMBER() OVER (PARTITION BY OD.[OrderNo], P.[ProductNo] ORDER BY OD.[Quantity] DESC)
OFFSET 0 ROWS FETCH NEXT 1 ROWS WITH TIES

或者用关联子查询替代窗口函数，直接定位到每个分组的最大Quantity行：

SELECT DISTINCT LEFT(P.[ProductNo], 2) AS [Code]
FROM [LESMESPRD].[FlexNet_prd].[dbo].[ORDER_DETAIL] AS OD
INNER JOIN [LESMESPRD].[FlexNet_prd].[dbo].[WIP_COMPONENT] AS WC 
    ON WC.[WiporderNo] = OD.[OrderNo]  
    AND WC.[WipOrderType] = OD.[OrderType]  
    AND WC.[Active] = 1 
INNER JOIN [LESMESPRD].[FlexNet_prd].[dbo].[COMPONENT] AS C 
    ON C.[ID] = WC.[ComponentID]
INNER JOIN [LESMESPRD].[FlexNet_prd].[dbo].[PRODUCT] AS P 
    ON P.[ID] = C.[ProductID]
WHERE P.[ProductNo] LIKE '43%' OR P.[ProductNo] LIKE '72%'
AND OD.[Quantity] = (
    SELECT MAX(OD2.[Quantity])
    FROM [LESMESPRD].[FlexNet_prd].[dbo].[ORDER_DETAIL] AS OD2
    WHERE OD2.[OrderNo] = OD.[OrderNo] 
    AND OD2.[ProductNo] = P.[ProductNo]
)

3. 优化链接服务器的数据传输 #

链接服务器查询慢的核心原因往往是把远程全表数据拉到本地再过滤，可以通过以下方式避免：

• 尽量将过滤、关联逻辑封装在远程服务器的存储过程或视图中，本地仅调用结果
• 使用OPENQUERY直接在远程执行查询，减少数据传输量：

SELECT [Code]
FROM OPENQUERY(LESMESPRD, '
    SELECT DISTINCT LEFT(P.[ProductNo], 2) AS [Code]
    FROM [FlexNet_prd].[dbo].[ORDER_DETAIL] AS OD
    INNER JOIN [FlexNet_prd].[dbo].[WIP_COMPONENT] AS WC 
        ON WC.[WiporderNo] = OD.[OrderNo]  
        AND WC.[WipOrderType] = OD.[OrderType]  
        AND WC.[Active] = 1 
    INNER JOIN [FlexNet_prd].[dbo].[COMPONENT] AS C 
        ON C.[ID] = WC.[ComponentID]
    INNER JOIN [FlexNet_prd].[dbo].[PRODUCT] AS P 
        ON P.[ID] = C.[ProductID]
    WHERE P.[ProductNo] LIKE ''43%'' OR P.[ProductNo] LIKE ''72%''
    GROUP BY OD.[OrderNo], P.[ProductNo], LEFT(P.[ProductNo], 2)
    HAVING OD.[Quantity] = MAX(OD.[Quantity])
')

4. 检查并优化关联字段的索引 #

在远程服务器上，确保以下字段存在合适的索引：

• ORDER_DETAIL: OrderNo, OrderType, Quantity（建议创建包含ProductNo的复合索引：IX_ORDER_DETAIL_OrderNo_OrderType）
• WIP_COMPONENT: WiporderNo, WipOrderType, Active, ComponentID（复合索引：IX_WIP_COMPONENT_WiporderNo_WipOrderType_Active）
• COMPONENT: ID, ProductID（主键索引通常已存在，确认是否包含ProductID）
• PRODUCT: ID, ProductNo（如之前建议的前缀索引）

内容的提问来源于stack exchange，提问作者Karolina Klimavičienė