Excel中多命名单元格IN运算符SQL查询优化需求

问题 #

需要优化一个从Excel引用80个命名单元格的SQL查询，原查询通过IN运算符匹配单元格值，但随着引用数量增加，耗时急剧上升：60个单元格耗时约5分钟，70个约10分钟，80个直接超时。原查询代码如下：

SELECT distinct
                  dbo.PRODORDERMASTER.PRODORDER as 'OP', dbo.PRODORDERMASTER.QTYREQ, dbo.PRODORDER.BOMNO as 'CODIGO', dbo.ESPECIFICPART.DESCRIPT as 'DESCRIPCION', dbo.PRODORDERMASTER.ORDERDATE as 'FECHA OP', 
                  dbo.PRODORDERMASTER.DATEREQ as 'FECHA REQ', dbo.PRODORDER.MULTIPLOS, dbo.SALESMASTER.AutMaquina as 'VoBo', MAX(dbo.ProdOrderProcesos.Numero) AS 'PROCESOS', dbo.ESPECIFICPART.impresion AS 'IMPRESION'


FROM        dbo.SALES INNER JOIN
              dbo.PRODORDER INNER JOIN
              dbo.PRODORDERMASTER ON dbo.PRODORDER.PRODORDER = dbo.PRODORDERMASTER.PRODORDER INNER JOIN
              dbo.ProdOrderProcesos ON dbo.PRODORDER.PRODORDER = dbo.ProdOrderProcesos.ProdOrder ON dbo.SALES.IDDET_SALE = dbo.PRODORDERMASTER.IDDETALLE INNER JOIN
              dbo.SALESMASTER ON dbo.SALES.IDPEDIDO = dbo.SALESMASTER.IDPEDIDO INNER JOIN
              dbo.ESPECIFICPART ON dbo.PRODORDER.BOMNO = dbo.ESPECIFICPART.PARTNO

WHERE       (dbo.ProdOrderProcesos.Division = 'CAPLE') AND (dbo.PRODORDERMASTER.TipoEspecific <> 'Displays') AND 
            (dbo.PRODORDERMASTER.TipoEspecific <> 'Digital') AND (dbo.PRODORDERMASTER.FINISHED = 'False') AND (dbo.PRODORDERMASTER.CANCEL = 'False')
            AND 
            dbo.PRODORDERMASTER.PRODORDER IN ("& Number.ToText(GetValue("OP_01")) & ", "& Number.ToText(GetValue("OP_02"))
              & ", "& Number.ToText(GetValue("OP_03")) & ", "& Number.ToText(GetValue("OP_04")) & ", "& Number.ToText(GetValue("OP_05")) 
              & ", "& Number.ToText(GetValue("OP_06")) & ", "& Number.ToText(GetValue("OP_07")) & ", "& Number.ToText(GetValue("OP_08")) 
              & ", "& Number.ToText(GetValue("OP_09")) & ", "& Number.ToText(GetValue("OP_10")) & ", "& Number.ToText(GetValue("OP_11")) 
              & ", "& Number.ToText(GetValue("OP_12")) & ", "& Number.ToText(GetValue("OP_13")) & ", "& Number.ToText(GetValue("OP_14")) 
              & ", "& Number.ToText(GetValue("OP_15")) & ", "& Number.ToText(GetValue("OP_16")) & ", "& Number.ToText(GetValue("OP_17")) 
              & ", "& Number.ToText(GetValue("OP_18")) & ", "& Number.ToText(GetValue("OP_19")) & ", "& Number.ToText(GetValue("OP_20")) 
              & ", "& Number.ToText(GetValue("OP_21")) & ", "& Number.ToText(GetValue("OP_22")) & ", "& Number.ToText(GetValue("OP_23")) 
              & ", "& Number.ToText(GetValue("OP_24")) & ", "& Number.ToText(GetValue("OP_25")) & ", "& Number.ToText(GetValue("OP_26"))
              & ", "& Number.ToText(GetValue("OP_27")) & ", "& Number.ToText(GetValue("OP_28")) & ", "& Number.ToText(GetValue("OP_29")) 
              & ", "& Number.ToText(GetValue("OP_30")) & ", "& Number.ToText(GetValue("OP_31")) & ", "& Number.ToText(GetValue("OP_32")) 
              & ", "& Number.ToText(GetValue("OP_33")) & ", "& Number.ToText(GetValue("OP_34")) & ", "& Number.ToText(GetValue("OP_35")) 
              & ", "& Number.ToText(GetValue("OP_36")) & ", "& Number.ToText(GetValue("OP_37")) & ", "& Number.ToText(GetValue("OP_38")) 
              & ", "& Number.ToText(GetValue("OP_39")) & ", "& Number.ToText(GetValue("OP_40")) & ", "& Number.ToText(GetValue("OP_41")) 
              & ", "& Number.ToText(GetValue("OP_42")) & ", "& Number.ToText(GetValue("OP_43")) & ", "& Number.ToText(GetValue("OP_44")) 
              & ", "& Number.ToText(GetValue("OP_45")) & ", "& Number.ToText(GetValue("OP_46")) & ", "& Number.ToText(GetValue("OP_47")) 
              & ", "& Number.ToText(GetValue("OP_48")) & ", "& Number.ToText(GetValue("OP_49")) & ", "& Number.ToText(GetValue("OP_50")) 
              & ", "& Number.ToText(GetValue("OP_51")) & ", "& Number.ToText(GetValue("OP_52")) & ", "& Number.ToText(GetValue("OP_53")) 
              & ", "& Number.ToText(GetValue("OP_54")) & ", "& Number.ToText(GetValue("OP_55")) & ", "& Number.ToText(GetValue("OP_56")) 
              & ", "& Number.ToText(GetValue("OP_57")) & ", "& Number.ToText(GetValue("OP_58")) & ", "& Number.ToText(GetValue("OP_59")) 
              & ", "& Number.ToText(GetValue("OP_60")) & ", "& Number.ToText(GetValue("OP_61")) & ", "& Number.ToText(GetValue("OP_62")) 
              & ", "& Number.ToText(GetValue("OP_63")) & ", "& Number.ToText(GetValue("OP_64")) & ", "& Number.ToText(GetValue("OP_65")) 
              & ", "& Number.ToText(GetValue("OP_66")) & ", "& Number.ToText(GetValue("OP_67")) & ", "& Number.ToText(GetValue("OP_68")) 
              & ", "& Number.ToText(GetValue("OP_69")) & ", "& Number.ToText(GetValue("OP_70")) & ", "& Number.ToText(GetValue("OP_71")) 
              & ", "& Number.ToText(GetValue("OP_72")) & ", "& Number.ToText(GetValue("OP_73")) & ", "& Number.ToText(GetValue("OP_74")) 
              & ", "& Number.ToText(GetValue("OP_75")) & ", "& Number.ToText(GetValue("OP_76")) & ", "& Number.ToText(GetValue("OP_77")) 
              & ", "& Number.ToText(GetValue("OP_78")) & ", "& Number.ToText(GetValue("OP_79")) & ", "& Number.ToText(GetValue("OP_80"))
              &   ")

            GROUP BY dbo.PRODORDERMASTER.PRODORDER, dbo.PRODORDERMASTER.QTYREQ, dbo.PRODORDER.BOMNO, dbo.ESPECIFICPART.DESCRIPT, dbo.PRODORDERMASTER.ORDERDATE, dbo.PRODORDERMASTER.DATEREQ, 
              dbo.PRODORDER.MULTIPLOS, dbo.SALESMASTER.AutMaquina, dbo.ESPECIFICPART.impresion

            ORDER BY dbo.PRODORDERMASTER.PRODORDER ASC

优化思路 #

1. 替换单个命名单元格为Excel连续区域 #

把所有OP编号放到Excel的一个连续区域（比如A1:A80），直接将该区域作为临时数据源导入查询，通过JOIN关联到目标表，而非拼接字符串到IN子句。这种方式能让数据库利用索引高效匹配，避免字符串拼接带来的性能损耗和SQL注入风险。

2. 精简表关联与前置过滤条件 #

• 检查所有关联表是否必需：若仅需SALESMASTER的AutMaquina字段，可通过子查询提前获取，减少JOIN层级
• 将WHERE子句中的过滤条件（如Division = 'CAPLE'、FINISHED = 'False'）前置到JOIN操作前，先过滤掉无关数据，减少后续处理的数据量
• 把TipoEspecific <> 'Displays' AND TipoEspecific <> 'Digital'改为TipoEspecific NOT IN ('Displays', 'Digital')，简化逻辑

3. 优化索引配置 #

确保以下字段存在合适的索引：

• PRODORDERMASTER.PRODORDER（核心过滤与JOIN字段）
• ProdOrderProcesos.Division和ProdOrderProcesos.ProdOrder
• PRODORDERMASTER.TipoEspecific、FINISHED、CANCEL（过滤字段）
• 为GROUP BY和SELECT中的字段创建覆盖索引，避免全表扫描和额外排序操作

4. 移除冗余的DISTINCT #

原查询同时使用SELECT DISTINCT和GROUP BY，但GROUP BY已经会对结果分组去重，直接删除DISTINCT可减少不必要的计算开销。

5. 改用临时表或表值参数传递OP编号 #

避免动态拼接IN列表，改用临时表或表值参数传递所有OP编号，再通过JOIN或IN (SELECT ...)匹配，示例代码如下：

-- 创建临时表存储OP编号
CREATE TABLE #TempOP (PRODORDER VARCHAR(50))
-- 批量插入Excel中的OP编号（可通过Excel数据导入或Power Query实现）
INSERT INTO #TempOP VALUES ('OP_01'), ('OP_02'), ..., ('OP_80')

SELECT 
  pom.PRODORDER as 'OP', pom.QTYREQ, po.BOMNO as 'CODIGO', ep.DESCRIPT as 'DESCRIPCION', pom.ORDERDATE as 'FECHA OP', 
  pom.DATEREQ as 'FECHA REQ', po.MULTIPLOS, sm.AutMaquina as 'VoBo', MAX(pop.Numero) AS 'PROCESOS', ep.impresion AS 'IMPRESION'
FROM dbo.PRODORDERMASTER pom
JOIN dbo.PRODORDER po ON po.PRODORDER = pom.PRODORDER
JOIN dbo.ProdOrderProcesos pop ON po.PRODORDER = pop.ProdOrder
JOIN dbo.SALES s ON s.IDDET_SALE = pom.IDDETALLE
JOIN dbo.SALESMASTER sm ON s.IDPEDIDO = sm.IDPEDIDO
JOIN dbo.ESPECIFICPART ep ON po.BOMNO = ep.PARTNO
-- 关联临时表过滤OP编号
JOIN #TempOP tmp ON pom.PRODORDER = tmp.PRODORDER
WHERE 
  pop.Division = 'CAPLE' 
  AND pom.TipoEspecific NOT IN ('Displays', 'Digital')
  AND pom.FINISHED = 'False' 
  AND pom.CANCEL = 'False'
GROUP BY 
  pom.PRODORDER, pom.QTYREQ, po.BOMNO, ep.DESCRIPT, pom.ORDERDATE, pom.DATEREQ, 
  po.MULTIPLOS, sm.AutMaquina, ep.impresion
ORDER BY pom.PRODORDER ASC

-- 清理临时表
DROP TABLE #TempOP

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