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

速度很慢的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

脚本主要存在以下问题:

  1. 循环处理:脚本使用了一个WHILE循环,循环直到处理完HMFG.dbo.t_sn_wip_240426表中所有opstatus=0的行。这种迭代方式可能会非常消耗资源,尤其是当这类行数众多时。循环处理会导致数据库引擎反复执行相同的查询逻辑,增加了数据库服务器的负担,且不利于充分利用数据库的批量处理能力。

  2. 重复查询与子查询:在每次循环中,脚本都会从HMFG.dbo.t_sn_wip_240426表中选取前1000个待处理的snwp_id和snwp_serial_number,然后分别对这些记录进行两次EXISTS子查询,以确定是否满足设置f1和f2标志的条件。这种做法不仅重复查询了HTPV.dbo.t_program_values_temp0426表,而且在数据量较大时,子查询可能会成为性能瓶颈。

  3. 临时表与索引:虽然您为临时表#tab_f1和#tab_f2创建了索引,但每次循环都要清空并重新插入数据,导致临时表的使用效率不高。同时,临时表的插入操作本身也增加了额外的写入开销。

  4. 多次更新:脚本通过两次UPDATE语句分别设置f1和f2标志,然后再进行一次UPDATE操作来设置opstatus。多次更新操作不仅增加了数据库事务的复杂性,还可能导致更多的锁竞争和资源消耗。


优化后代码
SET NOCOUNT ON;

IF OBJECT_ID('tempdb..#tab_conditions') IS NOT NULL DROP TABLE #tab_conditions;
CREATE TABLE #tab_conditions (
    snwp_id BIGINT PRIMARY KEY,
    f1 BIT NOT NULL DEFAULT (0),
    f2 BIT NOT NULL DEFAULT (0)
);

-- 预计算条件组合对应的tme_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.tme_program_values_temp0426 tpv
)
-- 更新#tab_conditions,记录满足条件的snwp_id及其对应的f1/f2标志
INSERT INTO #tab_conditions (snwp_id, f1, f2)
SELECT wip.snwp_id, MAX(f1_condition_met) AS f1, MAX(f2_condition_met) AS f2
FROM HMFG.dbo.tme_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.tme_sn_wip_240426表
UPDATE HMFG.dbo.tme_sn_wip_240426
SET f1 = t.f1,
    f2 = t.f2,
    opstatus = 1
FROM HMFG.dbo.tme_sn_wip_240426 wip
LEFT JOIN #tab_conditions t ON wip.snwp_id = t.snwp_id
WHERE wip.opstatus = 0;

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

主要优化措施如下:

  1. 避免循环:原脚本使用WHILE循环逐批处理数据,这里改为一次性处理所有符合条件的数据。

  2. 预计算:通过CTE(公用表表达式)预先计算出HTPV.dbo.t_program_values_temp0426表中每个条件组合对应的HMFG.dbo.t_sn_wip_240426.snwp_id集合,减少后续查询的复杂度。

  3. 合并更新:将原脚本中分别更新f1和f2的两步操作合并为一次UPDATE语句,同时更新f1、f2和opstatus字段。

  4. 简化临时表:修改临时表#tab_f1和#tab_f2的结构,直接存储满足条件的snwp_id及其对应的f1和f2标志,省去了多次EXISTS子查询。

  5. 使用LEFT JOIN:在最终的UPDATE语句中,使用LEFT JOIN确保即使某些snwp_id在临时表中不存在(即不满足条件),也能正确更新其opstatus字段为1。


提高SQL水平,AI提出的几点建议:

1.理解数据模型与关联关系:

  • 明确表间关系:在编写SQL查询时,首先要清楚各个表之间的关联关系,了解哪些字段是用于连接不同表的关键字段。
  • 熟悉数据模型:理解每个表的结构、字段含义以及数据分布情况,这对于编写高效查询至关重要。

2.避免过度使用循环与临时表:

  • 尽可能利用SQL的集算特性:SQL语言擅长处理集合操作,尽量避免使用循环等过程化编程思路。许多原本需要循环处理的任务,如批量更新、条件判断等,往往可以通过一次或几次集算查询实现。
  • 合理使用临时表:临时表在某些场景下可以帮助简化查询逻辑或提高查询性能,但过度使用或不当使用(如频繁插入、删除)可能导致额外开销。评估是否真正需要临时表,以及如何最高效地使用它。

3.掌握SQL查询优化技巧:

  • 使用有效索引:确保涉及的表有合适的索引,尤其是在JOIN、WHERE、GROUP BY等子句中使用的字段。索引可以大幅降低数据访问成本,提高查询速度。
  • 避免全表扫描与子查询嵌套:尽可能减少全表扫描,尤其是当表数据量较大时。合理运用JOIN、子查询、窗口函数等替代嵌套子查询,以提高查询效率。
  • 利用预计算与物化视图:对于复杂查询或频繁使用的查询片段,可以考虑使用CTE(公用表表达式)、临时表或物化视图进行预计算,简化主查询逻辑。

4.学习与实践SQL性能调优:

  • 学习SQL性能分析方法:理解执行计划、查询统计信息、等待事件等概念,学会使用数据库提供的性能分析工具(如SQL Server的Execution Plan、MySQL的EXPLAIN等)。
  • 实践性能调优案例:通过解决实际的性能问题,逐步积累经验。可以从简单查询开始,逐步挑战复杂查询的优化。

5.持续学习与跟进新技术:

  • 学习SQL高级特性:如窗口函数、递归查询、分区表、并行查询等,这些特性在特定场景下能显著提升查询性能。
  • 关注数据库技术发展:数据库技术不断进步,新的数据库产品、特性、优化方法层出不穷。定期关注相关资讯、文档、博客、论坛等,保持知识更新。
posted @ 2024-04-27 10:47  .NET每天都很酷  阅读(31)  评论(0编辑  收藏  举报