拼接不同表两列作为WHERE条件，SQL查询无法终止执行

1. 避免字段拼接导致的索引失效 #

原查询在IN子查询中使用concat(rsw_dept, rsw_rsm_id_fk)生成匹配值，这会让数据库无法利用rsw_dept、rsw_rsm_id_fk以及sdr_ID上的索引，同时强制数据库对每条记录做字符串拼接运算，直接拖慢查询速度。推荐改用EXISTS子查询拆分匹配逻辑：

select top 100 * 
from ProductionPeriodic.dbo.ScanDataRaw sdr
where exists (
    select 1
    from [dbo].[RollSheetArchiveDetails] rsad
    inner join dbo.RollSheetMain rsm on rsad.rsw_rsm_id_fk = rsm.rsm_id 
    where rsw_PoNo = 'UHB800008'
    and rsm_status = 'R'
    and sdr.sdr_ID = concat(rsad.rsw_dept, rsad.rsw_rsm_id_fk)
)
and sdr_ScanDate = '30/09/2022'

如果sdr_ID的结构是固定长度的rsw_dept拼接rsw_rsm_id_fk（比如rsw_dept固定为3位），可以直接拆分sdr_ID做精准匹配，进一步提升效率：

select top 100 * 
from ProductionPeriodic.dbo.ScanDataRaw sdr
where exists (
    select 1
    from [dbo].[RollSheetArchiveDetails] rsad
    inner join dbo.RollSheetMain rsm on rsad.rsw_rsm_id_fk = rsm.rsm_id 
    where rsw_PoNo = 'UHB800008'
    and rsm_status = 'R'
    and left(sdr.sdr_ID, 3) = rsad.rsw_dept
    and right(sdr.sdr_ID, len(sdr.sdr_ID)-3) = rsad.rsw_rsm_id_fk
)
and sdr_ScanDate = '30/09/2022'

2. 优化日期字符串的匹配逻辑 #

sdr_ScanDate为字符串类型，直接用'30/09/2022'匹配可能触发隐式类型转换或因格式问题导致全表扫描。建议显式转换为日期类型后匹配（以SQL Server为例）：

and CONVERT(date, sdr_ScanDate, 103) = '2022-09-30'

这种写法能避免字符串格式不一致带来的无效扫描，若存在基于日期的函数索引也能直接利用。

3. 补充必要的索引 #

检查以下字段是否存在非聚集索引，缺失的话及时创建：

• RollSheetArchiveDetails(rsw_PoNo, rsw_rsm_id_fk, rsw_dept)
• RollSheetMain(rsm_id, rsm_status)
• ScanDataRaw(sdr_ID, sdr_ScanDate)

创建索引示例：

CREATE NONCLUSTERED INDEX IX_RollSheetArchiveDetails_PoNo_RsmId_Dept 
ON [dbo].[RollSheetArchiveDetails] (rsw_PoNo, rsw_rsm_id_fk, rsw_dept);

CREATE NONCLUSTERED INDEX IX_RollSheetMain_Id_Status 
ON dbo.RollSheetMain (rsm_id, rsm_status);

CREATE NONCLUSTERED INDEX IX_ScanDataRaw_ID_ScanDate 
ON ProductionPeriodic.dbo.ScanDataRaw (sdr_ID, sdr_ScanDate);

4. 替换IN子查询为JOIN #

数据量较大时，IN子查询的效率通常低于JOIN，可改用以下写法：

select top 100 sdr.* 
from ProductionPeriodic.dbo.ScanDataRaw sdr
inner join (
    select distinct concat(rsad.rsw_dept, rsad.rsw_rsm_id_fk) as match_id
    from [dbo].[RollSheetArchiveDetails] rsad
    inner join dbo.RollSheetMain rsm on rsad.rsw_rsm_id_fk = rsm.rsm_id 
    where rsw_PoNo = 'UHB800008'
    and rsm_status = 'R'
) t on sdr.sdr_ID = t.match_id
where CONVERT(date, sdr_ScanDate, 103) = '2022-09-30'

用distinct去重匹配值，减少JOIN阶段的运算量。

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