SQL存储过程传入字符串数组参数时查询慢的优化方案

直接传入表值参数做IN查询时单值快、多值慢，是SQL Server跨库查询场景下的典型性能问题，用临时表预处理参数是经过大量生产验证的有效优化手段，具体实现逻辑和方法如下：

性能问题根因 #

你当前使用的自定义表值参数[TESTSU].[StringArray]存在两个核心性能瓶颈：

• SQL Server查询优化器默认预估表值参数仅包含1行数据，当传入几十、上百个参数值时，会错误选择关联算法（比如用适合小数据集的嵌套循环代替适合大数据集的哈希匹配），跨多库、多表关联时执行效率会骤降。
• 你在IN子句中直接对表值参数的每一行做LTRIM(RTRIM())计算，查询过程中无法用到关联字段上的现有索引，会进一步放大性能损耗。

临时表在数据写入完成后会自动生成准确的行数统计和数据分布统计，优化器可以基于真实的参数数量生成合理的执行计划，从根源解决多值传入时的性能问题。

具体实现步骤 #

1. 在存储过程起始位置创建临时表，字段类型和长度与关联字段cpd_cpno完全一致，同时给临时表的匹配字段加聚集主键，保证临时表本身的查询效率。
2. 提前对传入的表值参数做去空格、去重、过滤空值处理，一次性写入临时表，避免主查询运行时反复做函数计算。
3. 将原查询中WHERE cpd_cpno IN (子查询)的逻辑，改为和临时表做内连接，减少子查询带来的额外开销。

优化后的存储过程代码 #

USE [TEST]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [TESTSU].[SelectReport] (@StringAsArray [TESTSU].[StringArray] READONLY)
AS BEGIN
    -- 创建参数临时表，字段类型与cpd_cpno保持一致，加聚集索引提升关联效率
    CREATE TABLE #TmpCpNoList (
        CpNo VARCHAR(50) PRIMARY KEY CLUSTERED -- 字段长度请根据实际cpd_cpno的定义调整
    )

    -- 一次性清洗、写入参数数据
    INSERT INTO #TmpCpNoList (CpNo)
    SELECT DISTINCT LTRIM(RTRIM(StringValue))
    FROM @StringAsArray
    WHERE LTRIM(RTRIM(StringValue)) <> '' -- 过滤空值避免无效匹配

    SELECT 
        'SAM' as PLATFORM,
        'SAM'+ '0'+ordh_sysrefno as ZINDEX,
        ad_sapcode AS "SAP ADVERTISER CODE",
        ad_advcde AS "BMS ADVERTISER CODE",
        ad_advnme AS "ADVERTISER NAME",
        ag_sapcode AS "SAP AGENCY CODE",
        ag_agencde AS "BMS AGENCY CODE",
        ag_agennme AS "AGENCY NAME",
        ordh_docno AS "TO NUMBER",
        ordh_createdate AS "TO CREATE DATE",
        ordh_conttp AS "CONTRACT TYPE",
        tt_desc AS "TELECAST TYPE",
        '' AS "PACKAGE TYPE",
        '' AS "REVENUE TYPE",
        sapcode as "SAP PROGRAM CODE",
        pg_prgcode as "BMS PROGRAM CODE",
        pg_prgname as "PROGRAM",
        ordd_teledte AS "TELECAST DATE",
        ordd_agencost AS "INTERNAL COST",
        ordd_billcost AS "BILLING COST",
        'PHP' AS CURRENCY,
        '' AS PRODUCTION,
        spd_cpno as "CP NUMBER",
        cph_cpdte as "CP DATE",
        cph_prndte as "CP PRINT DATE",
        CASE ordh_conttp
            WHEN 'C' THEN spd_invno
            WHEN 'X' THEN spd_exinvno
            WHEN 'P' THEN spd_pbinvno
            ELSE '' 
        END AS "INVOICE NUMBER",
        -- 简化COALESCE嵌套写法，原生支持多参数依次判断非空
        COALESCE(A.invh_agencom, B.invh_agencom, C.invh_agencom)  as "COMMISSION AMOUNT",
        COALESCE(A.invh_vat, B.invh_vat, C.invh_vat) as "VAT AMOUNT",
        COALESCE(A.invh_billamt, B.invh_billamt, C.invh_billamt) as "BILLED AMOUNT",
        spd_stat as "STATUS"
    FROM
        SERVER.DB2.ADMINSA.ord_hdr 
    INNER JOIN SERVER.DB2.ADMINSA.ord_dtl ON ordh_sysrefno = ordd_sysrefno
    INNER JOIN SERVER.DB2.ADMINSA.spot_dtl ON ordd_sysrefno = spd_sysrefno and ordd_dtlno = spd_dtlno
    INNER JOIN SERVER.DB2.ADMINSA.program ON pg_prgcode = ordd_prgcode
    INNER JOIN SERVER.DB2.ADMINSA.advertiser ON ad_advcde = ordh_advcde
    INNER JOIN SERVER.DB2.ADMINSA.agency ON ag_agencde = ordh_agencde
    INNER JOIN SERVER.DB2.ADMINSA.cp_hdr ON ordh_sysrefno = cph_refno
    INNER JOIN SERVER.DB2.ADMINSA.cp_dtl ON 
        cph_cpno = cpd_cpno 
        and ordd_teledte = cpd_teledte
        and ordd_teletp = cpd_teletp
        and ordd_prgcode = cpd_prgcode
        and ordd_pcode = cpd_pcode
        and ordd_version = cpd_version
        and ordd_spotlen = cpd_spotlen
    -- 直接关联预处理好的临时表，替代原IN子查询
    INNER JOIN #TmpCpNoList t ON cpd_cpno = t.CpNo
    FULL OUTER JOIN SERVER.DB2.ADMINSA.inv_hdr A ON spd_invno = A.invh_invno and spd_sysrefno = A.invh_refno
    FULL OUTER JOIN SERVER.DB2.ADMINSA.inv_hdr B ON spd_exinvno = B.invh_invno and spd_sysrefno = B.invh_refno
    FULL OUTER JOIN SERVER.DB2.ADMINSA.inv_hdr C ON spd_exinvno = C.invh_invno and spd_sysrefno = C.invh_refno
    INNER JOIN SERVER.DB2.ADMINSA.telecast_type ON ordd_teletp = tt_code
    LEFT OUTER JOIN SERVER.TEST.TESTSU.programs_season ON 
        platform = 'SAM'
        and pg_prgcode = bmscode
        and cpd_teledte BETWEEN date_start AND date_end
END
GO

临时表会在存储过程执行结束后自动销毁，不需要额外手动删除。

额外优化建议 #

• 检查跨库关联的所有表，确认cpd_cpno、ordh_sysrefno、spd_invno、spd_exinvno这类高频关联字段上建有对应索引，索引对跨库查询的性能提升幅度远大于语句写法调整。
• 当前逻辑中对inv_hdr表做了三次全外连接，业务逻辑确认无误的前提下，可以优化为一次关联后通过CASE判断取值，减少表扫描次数。
• 如果传入的参数值存在大量重复，临时表写入时的DISTINCT去重可以减少后续关联的计算量。

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