从海量数据表中筛选符合不同条件组合的数据的SQL优化

 SET NOCOUNT ON;
 DECLARE @sn VARCHAR(200);
 DECLARE @n INT;
 DECLARE @sn_tab TABLE(id BIGINT,sn VARCHAR(200));

 IF OBJECT_ID('tempdb..#tab_f1') IS NOT NULL DROP TABLE #tab_f1
 CREATE TABLE #tab_f1(id BIGINT)
 CREATE INDEX idx_f1_id ON #tab_f1(id)

IF OBJECT_ID('tempdb..#tab_f2') IS NOT NULL DROP TABLE #tab_f2
 CREATE TABLE #tab_f2(id BIGINT)
 CREATE INDEX idx_f2_id ON #tab_f2(id)

 WHILE EXISTS(SELECT 1 FROM HMFG.dbo.t_sn_wip_240426 WHERE opstatus=0)
 BEGIN 
	
	DELETE FROM @sn_tab;
	INSERT INTO @sn_tab(id,sn)
	SELECT TOP 1000  snwp_id,snwp_serial_number FROM HMFG.dbo.t_sn_wip_240426 WHERE opstatus=0;

	INSERT INTO #tab_f1(id)
	SELECT stb.id FROM @sn_tab  stb WHERE EXISTS(SELECT 1 FROM HTPV.dbo.t_program_values_temp0426 tpv
	WHERE tpv.pgvl_serial_number=stb.sn
	AND tpv.pgvl_step_name = 'Efficiency & Power Factor Test 230V 5V PIN' AND tpv.pgvl_item_name = 'Output 1 Pin' AND  tpv.pgvl_item_para = 'Vin=230.000 Fin=50.000,A/O/V=0.000' AND	 tpv.pgvl_values = '0')

	INSERT INTO #tab_f2(id)
	SELECT stb.id FROM @sn_tab  stb WHERE EXISTS(SELECT 1 FROM HTPV.dbo.t_program_values_temp0426 tpv
	WHERE tpv.pgvl_serial_number=stb.sn
	AND tpv.pgvl_step_name = '静态测试' AND tpv.pgvl_item_name = '输入功率(W)' AND  tpv.pgvl_item_para = 'Vin:230.0000,Fin:50.0000;LD NoLoad' AND tpv.pgvl_values = '0')

	IF EXISTS(SELECT 1 FROM #tab_f1)
	BEGIN
	    UPDATE HMFG.dbo.t_sn_wip_240426
		SET f1=1
		FROM HMFG.dbo.t_sn_wip_240426 wip INNER JOIN #tab_f1 f1 ON wip.snwp_id=f1.id;
	END

	IF EXISTS(SELECT 1 FROM #tab_f2)
	BEGIN
	    UPDATE HMFG.dbo.t_sn_wip_240426
		SET f2=1
		FROM HMFG.dbo.t_sn_wip_240426 wip INNER JOIN #tab_f2 f2 ON wip.snwp_id=f2.id;
	END

	UPDATE HMFG.dbo.t_sn_wip_240426 SET opstatus=1 
	FROM  HMFG.dbo.t_sn_wip_240426 wip
	INNER JOIN @sn_tab st ON wip.snwp_id=st.id
	SET  @n= @@ROWCOUNT;

	PRINT @n;
 END

SET NOCOUNT ON;

IF OBJECT_ID('tempdb..#tab_f1') IS NOT NULL DROP TABLE #tab_f1;
IF OBJECT_ID('tempdb..#tab_f2') IS NOT NULL DROP TABLE #tab_f2;

CREATE TABLE #tab_f1 (
    snwp_id BIGINT PRIMARY KEY,
    f1 BIT NOT NULL DEFAULT (0)
);

CREATE TABLE #tab_f2 (
    snwp_id BIGINT PRIMARY KEY,
    f2 BIT NOT NULL DEFAULT (0)
);

-- 预计算条件组合对应的t_sn_wip_240426.snwp_id集合
WITH cte_condition_combinations AS (
    SELECT DISTINCT
        tpv.pgvl_serial_number,
        CASE WHEN tpv.pgvl_step_name = 'Efficiency & Power Factor Test 230V 5V PIN'
             AND tpv.pgvl_item_name = 'Output 1 Pin'
             AND tpv.pgvl_item_para = 'Vin=230.000 Fin=50.000,A/O/V=0.000'
             AND tpv.pgvl_values = '0' THEN 1 ELSE 0 END AS f1_condition_met,
        CASE WHEN tpv.pgvl_step_name = '静态测试'
             AND tpv.pgvl_item_name = '输入功率(W)'
             AND tpv.pgvl_item_para = 'Vin:230.0000,Fin:50.0000;LD NoLoad'
             AND tpv.pgvl_values = '0' THEN 1 ELSE 0 END AS f2_condition_met
    FROM HTPV.dbo.t_program_values_temp0426 tpv
)
-- 更新#tab_f1和#tab_f2，记录满足条件的snwp_id及其对应的f1/f2标志
INSERT INTO #tab_f1 (snwp_id, f1)
SELECT wip.snwp_id, MAX(f1_condition_met) AS f1
FROM HMFG.dbo.t_sn_wip_240426 wip
INNER JOIN cte_condition_combinations cc ON wip.snwp_serial_number = cc.pgvl_serial_number
WHERE wip.opstatus = 0
GROUP BY wip.snwp_id;

INSERT INTO #tab_f2 (snwp_id, f2)
SELECT wip.snwp_id, MAX(f2_condition_met) AS f2
FROM HMFG.dbo.t_sn_wip_240426 wip
INNER JOIN cte_condition_combinations cc ON wip.snwp_serial_number = cc.pgvl_serial_number
WHERE wip.opstatus = 0
GROUP BY wip.snwp_id;

-- 一次性更新HMFG.dbo.t_sn_wip_240426表
UPDATE HMFG.dbo.t_sn_wip_240426
SET f1 = t1.f1,
    f2 = t2.f2,
    opstatus = 1
FROM HMFG.dbo.t_sn_wip_240426 wip
LEFT JOIN #tab_f1 t1 ON wip.snwp_id = t1.snwp_id
LEFT JOIN #tab_f2 t2 ON wip.snwp_id = t2.snwp_id
WHERE wip.opstatus = 0;

-- 输出更新的行数
DECLARE @n INT = @@ROWCOUNT;
PRINT @n;