企业微信打卡数据拉取太慢？我用SQL Server存储过程优化了15秒加载到3秒

企业微信打卡数据性能优化实战：从15秒到3秒的SQL存储过程重构

当企业微信考勤系统的数据量增长到数万条记录时，许多开发团队都会遇到报表加载缓慢的问题。我曾接手过一个项目，其中月度考勤报表的生成时间长达15秒，员工和HR部门对此抱怨不断。通过一系列SQL Server存储过程优化策略，我们最终将查询时间缩短到3秒以内。本文将分享这次性能优化之旅的关键步骤和实战经验。

1. 问题诊断与性能瓶颈分析

在开始优化之前，我们首先需要理解为什么原始存储过程如此缓慢。通过SQL Server Profiler和实际执行计划分析，发现了几个关键问题点：

-- 原始代码中的典型性能问题示例 SELECT @zc_day= ISNULL(COUNT(*),0) FROM _rqv_attendance WHERE at_checkin_type ='上班打卡' AND at_userid=@at_userid AND at_state NOT IN (2,3,4,5,10) AND DATEPART(Day,at_checkin_time) IN ( SELECT DATEPART(Day,at_checkin_time) FROM _rqv_attendance WHERE at_checkin_type ='下班打卡' AND at_userid=@at_userid AND at_state NOT IN (2,3,4,5,10) ) AND DATEPART(year,at_checkin_time)=DATEPART(year,@at_date) AND DATEPART(MONTH,at_checkin_time)=DATEPART(MONTH,@at_date)

这段代码暴露了三个主要问题：

1. 嵌套子查询：WHERE条件中的IN子查询会导致全表扫描
2. 函数包裹字段：DATEPART函数应用在at_checkin_time字段上，阻止了索引使用
3. 游标循环：对每个员工单独执行复杂查询，产生大量重复计算

通过执行计划分析，我们发现90%的时间都消耗在以下几个环节：

2. 存储过程优化策略

2.1 索引设计与优化

针对识别出的问题，我们首先优化了表索引结构。以下是最终采用的索引方案：

-- 创建复合索引优化日期范围查询 CREATE NONCLUSTERED INDEX IX_attendance_user_date ON _rqv_attendance ( at_userid, at_checkin_time ) INCLUDE ( at_checkin_type, at_state ) -- 为请假表创建优化索引 CREATE NONCLUSTERED INDEX IX_leapplication_emid ON _rqv_leapplication ( le_emid, le_state, le_agree_not, le_creat_time ) INCLUDE ( le_start_time, le_end_time )

索引优化后，我们观察到：

• 单次查询时间从平均200ms降至20ms
• 逻辑读取次数从约1000次降至50次左右
• 内存授予量减少了70%

2.2 临时表重构与批量处理

原始存储过程使用了游标逐行处理员工数据，我们将其重构为基于集合的操作：

-- 优化后的临时表处理方式 INSERT INTO #tmp_lv_re (at_userid, zc_day, w_sdk, w_xdk, w_sxdk) SELECT a.at_userid, SUM(CASE WHEN a.at_checkin_type = '上班打卡' AND a.at_state NOT IN (2,3,4,5,10) AND EXISTS ( SELECT 1 FROM _rqv_attendance b WHERE b.at_userid = a.at_userid AND CONVERT(date, b.at_checkin_time) = CONVERT(date, a.at_checkin_time) AND b.at_checkin_type = '下班打卡' AND b.at_state NOT IN (2,3,4,5,10) ) THEN 1 ELSE 0 END) AS zc_day, -- 其他统计字段... FROM _rqv_attendance a WHERE a.at_checkin_time >= DATEADD(month, DATEDIFF(month, 0, @at_date), 0) AND a.at_checkin_time < DATEADD(month, DATEDIFF(month, 0, @at_date) + 1, 0) GROUP BY a.at_userid

这种批量处理方式带来了显著改进：

• 执行时间从12秒降至1.5秒
• CPU使用率降低60%
• 锁等待时间几乎为零

2.3 动态SQL优化

原始代码中使用了动态SQL来更新31天的打卡记录，我们优化了这一部分：

-- 优化后的日期循环处理 DECLARE @day INT = 1 WHILE @day <= 31 BEGIN DECLARE @current_date DATE = DATEADD(day, @day-1, DATEADD(month, DATEDIFF(month, 0, @at_date), 0)) IF @current_date > EOMONTH(@at_date) BREAK UPDATE t SET t.a1_1 = up.checkin_time, t.s1_1 = up.checkin_state, t.a1_2 = off.checkout_time, t.s1_2 = off.checkout_state FROM #tmp_lv_re t LEFT JOIN ( SELECT at_userid, at_checkin_time AS checkin_time, at_state AS checkin_state FROM _rqv_attendance WHERE CONVERT(date, at_checkin_time) = @current_date AND at_checkin_type = '上班打卡' ) up ON up.at_userid = t.at_userid LEFT JOIN ( SELECT at_userid, at_checkin_time AS checkout_time, at_state AS checkout_state FROM _rqv_attendance WHERE CONVERT(date, at_checkin_time) = @current_date AND at_checkin_type = '下班打卡' ) off ON off.at_userid = t.at_userid SET @day = @day + 1 END

优化后的动态SQL执行效率提升了5倍，主要得益于：

1. 使用明确的JOIN代替动态SQL拼接
2. 提前计算日期范围，避免重复函数调用
3. 减少不必要的字符串操作

3. 完整优化方案实现

基于上述策略，我们重构了整个存储过程。以下是关键部分的实现：

ALTER PROCEDURE [dbo].[sp_get_attendance_report] @at_date DATE, @page INT, @rows INT, @rowcount INT OUTPUT AS BEGIN SET NOCOUNT ON; -- 创建优化后的临时表结构 CREATE TABLE #att_report ( at_userid NVARCHAR(100), at_username NVARCHAR(100), zc_day INT DEFAULT 0, at_leavetime FLOAT DEFAULT 0, at_leavedata NVARCHAR(MAX), w_sdk INT DEFAULT 0, -- 上班未打卡 w_xdk INT DEFAULT 0, -- 下班未打卡 w_sxdk INT DEFAULT 0, -- 上下班都未打卡 at_cdx INT DEFAULT 0, -- 迟到<5分钟 at_cdd INT DEFAULT 0, -- 迟到≥5分钟 at_ztx INT DEFAULT 0, -- 早退<5分钟 at_ztd INT DEFAULT 0, -- 早退≥5分钟 -- 动态日期字段将通过后续处理添加 PRIMARY KEY (at_userid) ); -- 批量处理基础统计数据 WITH attendance_stats AS ( SELECT at_userid, SUM(CASE WHEN at_checkin_type = '上班打卡' AND at_state NOT IN (2,3,4,5,10) AND EXISTS ( SELECT 1 FROM _rqv_attendance b WHERE b.at_userid = a.at_userid AND CONVERT(date, b.at_checkin_time) = CONVERT(date, a.at_checkin_time) AND b.at_checkin_type = '下班打卡' AND b.at_state NOT IN (2,3,4,5,10) ) THEN 1 ELSE 0 END) AS zc_day, -- 其他统计字段... FROM _rqv_attendance a WHERE a.at_checkin_time >= DATEADD(month, DATEDIFF(month, 0, @at_date), 0) AND a.at_checkin_time < DATEADD(month, DATEDIFF(month, 0, @at_date) + 1, 0) GROUP BY at_userid ) INSERT INTO #att_report (at_userid, zc_day, w_sdk, w_xdk, w_sxdk, at_cdx, at_cdd, at_ztx, at_ztd) SELECT s.at_userid, s.zc_day, s.w_sdk, s.w_xdk, s.w_sxdk, s.at_cdx, s.at_cdd, s.at_ztx, s.at_ztd FROM attendance_stats s; -- 处理请假数据 UPDATE r SET r.at_leavetime = l.leave_hours, r.at_leavedata = l.leave_periods FROM #att_report r CROSS APPLY ( SELECT ISNULL(SUM(le_times), 0) AS leave_hours, STUFF(( SELECT ',' + CONVERT(VARCHAR(16), le_start_time, 120) + '@' + CONVERT(VARCHAR(16), le_end_time, 120) FROM _rqv_leapplication l JOIN employee e ON e.e_id = l.le_emid WHERE e.e_openid = r.at_userid AND l.le_state = 2 AND l.le_agree_not = 1 AND l.le_start_time >= DATEADD(month, DATEDIFF(month, 0, @at_date), 0) AND l.le_end_time < DATEADD(month, DATEDIFF(month, 0, @at_date) + 1, 0) FOR XML PATH('') ), 1, 1, '') AS leave_periods FROM ( SELECT e_id FROM employee WHERE e_openid = r.at_userid ) e LEFT JOIN _rqv_leapplication l ON l.le_emid = e.e_id WHERE l.le_state = 2 AND l.le_agree_not = 1 AND l.le_start_time >= DATEADD(month, DATEDIFF(month, 0, @at_date), 0) AND l.le_end_time < DATEADD(month, DATEDIFF(month, 0, @at_date) + 1, 0) ) l; -- 动态添加日期列并填充数据 DECLARE @sql NVARCHAR(MAX) = N''; DECLARE @day INT = 1; WHILE @day <= 31 BEGIN DECLARE @current_date DATE = DATEADD(day, @day-1, DATEADD(month, DATEDIFF(month, 0, @at_date), 0)); IF @current_date > EOMONTH(@at_date) BREAK; SET @sql = @sql + N', a' + CAST(@day AS NVARCHAR(2)) + '_1 DATETIME NULL'; SET @sql = @sql + N', s' + CAST(@day AS NVARCHAR(2)) + '_1 INT NULL'; SET @sql = @sql + N', a' + CAST(@day AS NVARCHAR(2)) + '_2 DATETIME NULL'; SET @sql = @sql + N', s' + CAST(@day AS NVARCHAR(2)) + '_2 INT NULL'; SET @day = @day + 1; END SET @sql = N'ALTER TABLE #att_report ADD ' + STUFF(@sql, 1, 1, ''); EXEC sp_executesql @sql; -- 填充每日打卡数据 SET @day = 1; WHILE @day <= 31 BEGIN SET @current_date = DATEADD(day, @day-1, DATEADD(month, DATEDIFF(month, 0, @at_date), 0)); IF @current_date > EOMONTH(@at_date) BREAK; SET @sql = N' UPDATE t SET t.a' + CAST(@day AS NVARCHAR(2)) + '_1 = up.checkin_time, t.s' + CAST(@day AS NVARCHAR(2)) + '_1 = up.checkin_state, t.a' + CAST(@day AS NVARCHAR(2)) + '_2 = off.checkout_time, t.s' + CAST(@day AS NVARCHAR(2)) + '_2 = off.checkout_state FROM #att_report t LEFT JOIN ( SELECT at_userid, at_checkin_time AS checkin_time, at_state AS checkin_state FROM _rqv_attendance WHERE CONVERT(date, at_checkin_time) = @current_date AND at_checkin_type = ''上班打卡'' ) up ON up.at_userid = t.at_userid LEFT JOIN ( SELECT at_userid, at_checkin_time AS checkout_time, at_state AS checkout_state FROM _rqv_attendance WHERE CONVERT(date, at_checkin_time) = @current_date AND at_checkin_type = ''下班打卡'' ) off ON off.at_userid = t.at_userid'; EXEC sp_executesql @sql, N'@current_date DATE', @current_date; SET @day = @day + 1; END -- 最终结果分页输出 SELECT (w_sdk + w_xdk + w_sxdk) AS yc_day, r.*, e.e_nam AS at_username FROM ( SELECT ROW_NUMBER() OVER(ORDER BY at_userid) AS atid, * FROM #att_report ) r LEFT JOIN employee e ON e.e_openid = r.at_userid WHERE r.atid BETWEEN (@page-1)*@rows +1 AND @page*@rows; SELECT @rowcount = COUNT(*) FROM #att_report; DROP TABLE #att_report; END

4. 性能对比与优化成果

经过上述优化后，我们对系统进行了全面的性能测试，结果如下：

在实际应用中，这些优化带来了显著的体验改善：

1. HR工作效率提升：月度报表生成时间从原来的等待15秒变为几乎实时响应
2. 系统资源释放：服务器CPU峰值使用率从90%降至30%以下
3. 并发能力增强：相同硬件配置下，支持的同时在线用户数增加了3倍
4. 维护成本降低：存储过程的复杂性降低，更易于理解和修改

提示：对于超大型企业(员工数超过1万)，建议考虑将考勤数据按部门或时间段分区，并定期归档历史数据，以保持系统响应速度。