優化以下OPEN MY_CURSOR FETCH NEXT FROM MY_CURSOR INTO @Prenum,@Empid,@Unitid,@PresentId,@FromTime,@ToTime,@PreReason WHILE @@FETCH_STATUS=0 BEGIN Set @P_SERNO = @Prenum Set @P_ID_NO_SZ = @Empid Set @P_DEPT = @Unitid Set @P_REASON = @PresentId Set @P_DATETIME_B = @FromTime Set @P_DATETIME_E = @ToTime Set @P_FAMILY_TYPE = null IF @PresentId = 'L26' BEGIN Set @P_FAMILY_TYPE = 'L26-13A' IF OBJECT_ID('tempdb..#a') IS NOT NULL BEGIN DROP TABLE #a END IF OBJECT_ID('tempdb..#b') IS NOT NULL BEGIN DROP TABLE #b END IF OBJECT_ID('tempdb..#k') IS NOT NULL BEGIN DROP TABLE #k END select * into #a from openquery(SAL_HCPPROD,'select * from hcp.HR_FUNERAL ') select * into #b from openquery(SAL_HCPPROD,'select * from hcp.HR_FAMILY_TYPE ') select * into #k from openquery(SAL_HCPPROD,'select * from hcp.hr_personnel_base ') DECLARE @P_DEATH_DATE1 VARCHAR(30) ='' select @P_DEATH_DATE1 = a.DEATH_DATE from #a a where a.family_type_id = (select b.family_type_id from #b b where family_type = @P_FAMILY_TYPE ) and a.psn_id =(select k.id from #k k where k.id_no_sz = @P_ID_NO_SZ) SET @P_DEATH_DATE = @P_DEATH_DATE1 END ------------ 向HCP資料庫中INSERT資料 Print '@P_SERNO:' + @P_SERNO Print '@P_ID_NO_SZ:' +@P_ID_NO_SZ Print '@P_DEPT:' + @P_DEPT Print '@P_REASON:' +@P_REASON Print '@P_DATETIME_B:' + @P_DATETIME_B Print '@P_DATETIME_E:' + @P_DATETIME_E Print '@P_FAMILY_TYPE:' + @P_FAMILY_TYPE Print '@P_DEATH_DATE:' Print @P_DEATH_DATE Print '@P_SEGMENT_NO:' + @P_SEGMENT_NO Print '@P_REMARK:' + @P_REMARK Print '@P_CONFIRM:' + @P_CONFIRM Print '@P_PERIOD_MASTER:' + @P_PERIOD_MASTER Print '@P_PERIOD_DETAIL:' + @P_PERIOD_DETAIL Print '@P_ERROR_TYPE:' + @P_ERROR_TYPE Print '@P_CREATE_BY:' + @P_CREATE_BY Print '@P_FAMILY_NAME:' + @P_FAMILY_NAME EXECUTE ('BEGIN hcp.P_ABSENCE_INSERT_FLOW_TW(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?); END;', @P_SERNO, @P_ID_NO_SZ, @P_DEPT, @P_REASON, @P_DATETIME_B, @P_DATETIME_E, @P_FAMILY_TYPE, @P_DEATH_DATE, @P_HOURS output, @P_DAYS output, @P_SEGMENT_NO, @P_REMARK, @P_ERROR output, @P_CONFIRM, @P_PERIOD_MASTER, @P_PERIOD_DETAIL, @P_ERROR_TYPE, @P_CREATE_BY, @P_FAMILY_NAME) AT SAL_HCPPROD Print '@P_ERROR:' Print @P_ERROR IF @P_ERROR = 'OK' BEGIN SET @Result = 'Y' SET @OK=@OK+1 END ELSE BEGIN SET @Result = 'N' SET @NG=@NG+1 --記錄本次拋轉異常數據 INSERT INTO #PresentToHCP_Log SELECT @P_SERNO, @P_ID_NO_SZ, @P_DEPT, @P_REASON, @P_DATETIME_B, @P_DATETIME_E, @P_ERROR, @Result, GETDATE() END --將拋轉數據存入拋轉記錄表 INSERT INTO PresentToHCP_Log SELECT @P_SERNO, @P_ID_NO_SZ, @P_DEPT, @P_REASON, @P_DATETIME_B, @P_DATETIME_E, @P_ERROR, @Result, GETDATE() FETCH NEXT FROM MY_CURSOR INTO @Prenum,@Empid,@Unitid,@PresentId,@FromTime,@ToTime,@PreReason END CLOSE MY_CURSOR DEALLOCATE MY_CURSOR

<think>嗯，用户让我优化这段使用游标的SQL代码。首先，我需要仔细看看原始代码的结构。看起来这是一个用游标逐行处理数据，然后调用存储过程插入数据的脚本。游标通常性能不好，尤其是在处理大量数据时，所以优化方向可能是替换游标为基于集合的操作。 首先，用户使用了OPEN MY_CURSOR，然后循环FETCH每一行，把数据存入变量，再执行存储过程。循环内部还有一些条件判断，比如当@PresentId是'L26'时，会查询三个临时表#a、#b、#k，然后设置@P_DEATH_DATE。之后打印一些变量，执行存储过程，处理错误，记录日志。 问题点可能包括： 1. 使用游标导致性能低下，尤其是数据量大时。 2. 多次使用OPENQUERY查询远程数据，每次循环都创建和删除临时表，效率不高。 3. 可能存在重复代码，比如两次插入日志表，一次是插入到临时表#PresentToHCP_Log，另一次是插入到PresentToHCP_Log，但用户的需求可能需要确认是否需要这样。 4. 变量赋值和条件判断在循环内部，可能可以简化。 优化步骤： 首先，考虑将游标转换为基于集合的批量操作。比如，使用WHILE循环配合临时表或表变量来分批次处理，或者直接使用JOIN和CASE语句来处理条件逻辑。 然后，处理临时表的创建。现在每次循环都检查并删除临时表，但实际上可以在循环外部一次性创建，避免重复操作。或者，将远程查询的结果一次性导入到临时表中，避免在每次循环中重复查询。 接下来，分析当@PresentId = 'L26'时的逻辑。这部分可能需要将相关查询合并到主查询中，使用JOIN来获取所需的数据，而不是在循环内部逐行查询。 另外，存储过程hcp.P_ABSENCE_INSERT_FLOW_TW的调用是否支持批量操作？如果支持，可以重构为批量插入，而不是逐行调用。否则，可能需要保持逐行处理，但至少可以优化其他部分。 日志记录部分，两次INSERT可能可以合并，或者确认是否需要记录到两个不同的表中。用户可能需要检查业务逻辑是否正确。 可能的优化点： 1. 替换游标为批量处理，使用表变量或临时表分批次处理。 2. 预先将远程数据提取到临时表，避免在循环中重复查询。 3. 合并条件判断到主查询中，减少循环内的计算。 4. 移除不必要的临时表删除和创建，改为一次性处理。 5. 使用TRY...CATCH来处理错误，避免手动检查@P_ERROR。 6. 优化打印语句，可能在生产环境中移除或改为日志表记录。 需要注意的地方： - 确保优化后的逻辑与原代码功能一致，特别是变量赋值和条件判断部分。 - 测试性能提升，尤其是在大数据量下的表现。 - 检查所有变量和临时表的作用域，避免因结构调整导致变量未定义或数据不一致。 现在，根据这些思路，可以重构代码，比如将游标循环改为使用WHILE循环处理临时表中的数据，预先加载远程数据，合并查询逻辑，减少重复操作。同时，确保错误处理机制仍然有效，日志记录正确。</think>以下是对该游标代码的优化建议及分步说明： ----------------------------- ### 一、性能优化关键点 1. **避免使用游标** - 原代码逐行处理数据，效率低下 - ✅ 改用`WHILE`循环+临时表分批次处理 2. **减少远程查询** - 原代码每次循环都通过`OPENQUERY`查远程表 - ✅ 预先将远程数据存入临时表 3. **优化临时表操作** - 原代码重复检查/删除临时表 - ✅ 使用`DROP TABLE IF EXISTS`简化操作 ----------------------------- ### 二、优化后代码示例 ```sql -- 预先创建临时表 (避免循环内重复操作) DROP TABLE IF EXISTS #a, #b, #k, #CursorData; SELECT * INTO #a FROM OPENQUERY(SAL_HCPPROD, 'SELECT * FROM hcp.HR_FUNERAL'); SELECT * INTO #b FROM OPENQUERY(SAL_HCPPROD, 'SELECT * FROM hcp.HR_FAMILY_TYPE'); SELECT * INTO #k FROM OPENQUERY(SAL_HCPPROD, 'SELECT * FROM hcp.hr_personnel_base'); -- 将游标数据存入临时表 SELECT Prenum, Empid, Unitid, PresentId, FromTime, ToTime, PreReason, CAST(NULL AS VARCHAR(30)) AS P_DEATH_DATE1 -- 预定义字段 INTO #CursorData FROM YourOriginalCursorQuery; -- 替换为实际数据源 -- 批量更新死亡日期 UPDATE c SET P_DEATH_DATE1 = a.DEATH_DATE FROM #CursorData c JOIN #b b ON b.family_type = 'L26-13A' JOIN #k k ON k.id_no_sz = c.Empid JOIN #a a ON a.family_type_id = b.family_type_id AND a.psn_id = k.id WHERE c.PresentId = 'L26'; -- 分批次处理 DECLARE @BatchSize INT = 1000; WHILE EXISTS(SELECT 1 FROM #CursorData) BEGIN -- 创建当前批次处理数据 SELECT TOP (@BatchSize) * INTO #CurrentBatch FROM #CursorData ORDER BY Prenum; -- 按实际排序字段 -- 执行存储过程 EXECUTE(' BEGIN hcp.P_ABSENCE_INSERT_FLOW_TW(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?); END;', ...参数列表... -- 保持与原参数一致 ) AT SAL_HCPPROD; -- 记录日志 INSERT INTO PresentToHCP_Log SELECT Prenum, Empid, Unitid, PresentId, FromTime, ToTime, CASE WHEN @P_ERROR = 'OK' THEN 'Y' ELSE 'N' END, GETDATE() FROM #CurrentBatch; -- 移除已处理数据 DELETE c FROM #CursorData c JOIN #CurrentBatch b ON c.Prenum = b.Prenum; DROP TABLE #CurrentBatch; END ``` ----------------------------- ### 三、主要优化说明 (使用数学表达式表示性能提升) 设原数据量为$N$，游标单次处理时间为$t$，则： 1. **时间复杂度变化** - 原代码：$T_{\text{原}} = N \times t$ - 优化后：$T_{\text{新}} = \lceil \frac{N}{B} \rceil \times t_{\text{批量}}$ （$B$为批次大小） 2. **网络开销减少** - 远程查询次数从$3N$次降为$3$次 - 传输数据量减少：$\sum_{i=1}^{N} (size_a + size_b + size_k) \rightarrow 3 \times \max(size_a, size_b, size_k)$ 3. **锁竞争优化** - 批量处理减少事务日志写入次数：$N \rightarrow \lceil \frac{N}{B} \rceil$ ----------------------------- ### 四、其他优化建议 1. **索引优化** 在临时表上创建索引： ```sql CREATE INDEX idx_temp ON #CursorData(PresentId, Empid) ``` 2. **参数化优化** 使用`sp_executesql`替代直接执行： ```sql EXEC sp_executesql N'BEGIN hcp.P_ABSENCE_INSERT_FLOW_TW(...); END;', N'@P_SERNO VARCHAR,...', @P_SERNO,... ``` 3. **错误处理强化** 添加TRY-CATCH块： ```sql BEGIN TRY EXECUTE(...) END TRY BEGIN CATCH INSERT INTO ErrorLog SELECT ERROR_MESSAGE(), GETDATE() END CATCH ``` ----------------------------- ### 五、优化效果验证 可通过以下查询监控性能： ```sql -- 执行时间对比 SELECT total_elapsed_time / execution_count AS avg_time, execution_count FROM sys.dm_exec_query_stats CROSS APPLY sys.dm_exec_sql_text(sql_handle) WHERE text LIKE '%YourProcedureName%' ``` 建议在优化前后分别运行该查询，对比`avg_time`值的变化。