问题详情
-
类型:
改进
-
状态: 开始 (查看工作流)
-
优先级:
重要
-
解决结果: 未解决
-
影响版本: v5.0.20230608_release
-
解决版本: v5.0.20230608_release
-
模块: 调度后台
-
标签:无 编辑标签
-
项目环境:SIT
描述
1.报表统计——调度情况表（机构任务数量）
slow sql 3941 millis. SELECT COUNT( * ) FROM dd_case_task WHERE (org_code = ? AND insurance_type = ? AND task_type = ? AND create_time LIKE ?)["01",1,0,"2023-06-05%"]
改造后：SELECT COUNT(1) FROM dd_case_task WHERE ( org_code = '01' AND insurance_type = 1 AND create_time >= '2023-06-05 00:00:00' AND create_time <= '2023-06-05 23:59:59' )
2.已调度列表——使用了in，导致慢sql
slow sql 3173 millis. SELECT COUNT
FROM dd_case_info WHERE (case_status = ? AND claim_status IN (?, ?, ?, ?, ?, ?, ?))[2,"0","1","2","3","4","5","6"]
改造方式：去掉in查询，注销案件也显示在已调度列表
3.报表统计——机构案件统计
slow sql 3008 millis. SELECT COUNT( * ) FROM dd_case_info WHERE (org_code = ? AND insurance_type = ? AND create_time LIKE ?)["03",1,"2023-06-05%"]
改造后：SELECT COUNT( * ) FROM dd_case_info WHERE (org_code = '03' AND insurance_type = 1 AND create_time >= '2023-06-05 00:00:00' AND create_time <= '2023-06-05 23:59:59')
4.WX_DP20——查询案件（视频页面自助公共池——总数统计）
slow SQL 3034 millis. SELECT COUNT(1) FROM dd_case_info c WHERE 1 = 1 AND c.has_commit_video = '0' AND c.video_user IS NULL AND c.org_code LIKE '03%' AND c.deal_type = '1'
改造方式：org_code 条件改成等于（案件表的org_code 字段全部都存的是二级机构）；或者查询入参增加时间范围————暂时不做调整，有三级机构的视频坐席账号
5.WX_DP20——查询案件（视频页面自助公共池——列表数据 ）
slow SQL 3270 millis. SELECT
c.regist_no,
c.license_no,
c.insured_name,
c.linker_name,
c.linker_phone,
c.damage_time,
c.report_time,
c.damage_place,
c.damage_remark,
c.case_status,
c.subrogation,
c.vip_level,
c.insurance_type,
c.org_code,
c.org_name,
c.video_user,
c.video_user_name,
c.is_new_policy_flag,
c.is_black_list,
c.black_list_type,
c.black_list_type_desc,
c.deal_type,
c.has_commit_video
FROM
dd_case_info c
WHERE
1 = 1
AND c.has_commit_video = '0'
AND c.video_user IS NULL
AND c.org_code LIKE '03%'
AND c.deal_type = '1'
LIMIT 0,100
改造方式：org_code 条件改成等于（案件表的org_code 字段全部都存的是二级机构）；或者查询入参增加时间范围————暂时不做调整，有三级机构的视频坐席账号
活动
| 字段 | 原值 | 新值 |
|---|---|---|
| 描述 |
1.报表统计
slow sql 3941 millis. SELECT COUNT( * ) FROM dd_case_task WHERE (org_code = ? AND insurance_type = ? AND task_type = ? AND create_time LIKE ?)["01",1,0,"2023-06-05%"] |
| 描述 |
1.报表统计
slow sql 3941 millis. SELECT COUNT( * ) FROM dd_case_task WHERE (org_code = ? AND insurance_type = ? AND task_type = ? AND create_time LIKE ?)["01",1,0,"2023-06-05%"] |
1.报表统计——调度情况表（机构任务数量）
slow sql 3941 millis. SELECT COUNT( * ) FROM dd_case_task WHERE (org_code = ? AND insurance_type = ? AND task_type = ? AND create_time LIKE ?)["01",1,0,"2023-06-05%"] |
| 描述 |
1.报表统计——调度情况表（机构任务数量）
slow sql 3941 millis. SELECT COUNT( * ) FROM dd_case_task WHERE (org_code = ? AND insurance_type = ? AND task_type = ? AND create_time LIKE ?)["01",1,0,"2023-06-05%"] |
1.报表统计——调度情况表（机构任务数量）
slow sql 3941 millis. SELECT COUNT( * ) FROM dd_case_task WHERE (org_code = ? AND insurance_type = ? AND task_type = ? AND create_time LIKE ?)["01",1,0,"2023-06-05%"] 2. slow sql 3173 millis. SELECT COUNT(*) FROM dd_case_info WHERE (case_status = ? AND claim_status IN (?, ?, ?, ?, ?, ?, ?))[2,"0","1","2","3","4","5","6"] |
| 描述 |
1.报表统计——调度情况表（机构任务数量）
slow sql 3941 millis. SELECT COUNT( * ) FROM dd_case_task WHERE (org_code = ? AND insurance_type = ? AND task_type = ? AND create_time LIKE ?)["01",1,0,"2023-06-05%"] 2. slow sql 3173 millis. SELECT COUNT(*) FROM dd_case_info WHERE (case_status = ? AND claim_status IN (?, ?, ?, ?, ?, ?, ?))[2,"0","1","2","3","4","5","6"] |
1.报表统计——调度情况表（机构任务数量）
slow sql 3941 millis. SELECT COUNT( * ) FROM dd_case_task WHERE (org_code = ? AND insurance_type = ? AND task_type = ? AND create_time LIKE ?)["01",1,0,"2023-06-05%"] 2.已调度列表——使用了in，导致慢sql slow sql 3173 millis. SELECT COUNT(*) FROM dd_case_info WHERE (case_status = ? AND claim_status IN (?, ?, ?, ?, ?, ?, ?))[2,"0","1","2","3","4","5","6"] |
| 描述 |
1.报表统计——调度情况表（机构任务数量）
slow sql 3941 millis. SELECT COUNT( * ) FROM dd_case_task WHERE (org_code = ? AND insurance_type = ? AND task_type = ? AND create_time LIKE ?)["01",1,0,"2023-06-05%"] 2.已调度列表——使用了in，导致慢sql slow sql 3173 millis. SELECT COUNT(*) FROM dd_case_info WHERE (case_status = ? AND claim_status IN (?, ?, ?, ?, ?, ?, ?))[2,"0","1","2","3","4","5","6"] |
1.报表统计——调度情况表（机构任务数量）
slow sql 3941 millis. SELECT COUNT( * ) FROM dd_case_task WHERE (org_code = ? AND insurance_type = ? AND task_type = ? AND create_time LIKE ?)["01",1,0,"2023-06-05%"] 2.已调度列表——使用了in，导致慢sql slow sql 3173 millis. SELECT COUNT(*) FROM dd_case_info WHERE (case_status = ? AND claim_status IN (?, ?, ?, ?, ?, ?, ?))[2,"0","1","2","3","4","5","6"] 3. slow sql 3008 millis. SELECT COUNT( * ) FROM dd_case_info WHERE (org_code = ? AND insurance_type = ? AND create_time LIKE ?)["03",1,"2023-06-05%"] |
| 描述 |
1.报表统计——调度情况表（机构任务数量）
slow sql 3941 millis. SELECT COUNT( * ) FROM dd_case_task WHERE (org_code = ? AND insurance_type = ? AND task_type = ? AND create_time LIKE ?)["01",1,0,"2023-06-05%"] 2.已调度列表——使用了in，导致慢sql slow sql 3173 millis. SELECT COUNT(*) FROM dd_case_info WHERE (case_status = ? AND claim_status IN (?, ?, ?, ?, ?, ?, ?))[2,"0","1","2","3","4","5","6"] 3. slow sql 3008 millis. SELECT COUNT( * ) FROM dd_case_info WHERE (org_code = ? AND insurance_type = ? AND create_time LIKE ?)["03",1,"2023-06-05%"] |
1.报表统计——调度情况表（机构任务数量）
slow sql 3941 millis. SELECT COUNT( * ) FROM dd_case_task WHERE (org_code = ? AND insurance_type = ? AND task_type = ? AND create_time LIKE ?)["01",1,0,"2023-06-05%"] 改造后：SELECT COUNT(1) FROM dd_case_task WHERE ( org_code = '01' AND insurance_type = 1 AND create_time >= '2023-06-05 00:00:00' AND create_time <= '2023-06-05 23:59:59' ) 2.已调度列表——使用了in，导致慢sql slow sql 3173 millis. SELECT COUNT(*) FROM dd_case_info WHERE (case_status = ? AND claim_status IN (?, ?, ?, ?, ?, ?, ?))[2,"0","1","2","3","4","5","6"] 3. slow sql 3008 millis. SELECT COUNT( * ) FROM dd_case_info WHERE (org_code = ? AND insurance_type = ? AND create_time LIKE ?)["03",1,"2023-06-05%"] |
| 描述 |
1.报表统计——调度情况表（机构任务数量）
slow sql 3941 millis. SELECT COUNT( * ) FROM dd_case_task WHERE (org_code = ? AND insurance_type = ? AND task_type = ? AND create_time LIKE ?)["01",1,0,"2023-06-05%"] 改造后：SELECT COUNT(1) FROM dd_case_task WHERE ( org_code = '01' AND insurance_type = 1 AND create_time >= '2023-06-05 00:00:00' AND create_time <= '2023-06-05 23:59:59' ) 2.已调度列表——使用了in，导致慢sql slow sql 3173 millis. SELECT COUNT(*) FROM dd_case_info WHERE (case_status = ? AND claim_status IN (?, ?, ?, ?, ?, ?, ?))[2,"0","1","2","3","4","5","6"] 3. slow sql 3008 millis. SELECT COUNT( * ) FROM dd_case_info WHERE (org_code = ? AND insurance_type = ? AND create_time LIKE ?)["03",1,"2023-06-05%"] |
1.报表统计——调度情况表（机构任务数量）
slow sql 3941 millis. SELECT COUNT( * ) FROM dd_case_task WHERE (org_code = ? AND insurance_type = ? AND task_type = ? AND create_time LIKE ?)["01",1,0,"2023-06-05%"] 改造后：SELECT COUNT(1) FROM dd_case_task WHERE ( org_code = '01' AND insurance_type = 1 AND create_time >= '2023-06-05 00:00:00' AND create_time <= '2023-06-05 23:59:59' ) 2.已调度列表——使用了in，导致慢sql slow sql 3173 millis. SELECT COUNT(*) FROM dd_case_info WHERE (case_status = ? AND claim_status IN (?, ?, ?, ?, ?, ?, ?))[2,"0","1","2","3","4","5","6"] 改造方式：去掉in查询，注销案件也显示在已调度列表 3. slow sql 3008 millis. SELECT COUNT( * ) FROM dd_case_info WHERE (org_code = ? AND insurance_type = ? AND create_time LIKE ?)["03",1,"2023-06-05%"] 改造后：SELECT COUNT( * ) FROM dd_case_info WHERE (org_code = '03' AND insurance_type = 1 AND create_time >= '2023-06-05 00:00:00' AND create_time <= '2023-06-05 23:59:59') |
| 主题 | 【慢SQL优化】相关功能记录 | 【慢SQL优化——2023-06-05】相关功能记录 |
| 描述 |
1.报表统计——调度情况表（机构任务数量）
slow sql 3941 millis. SELECT COUNT( * ) FROM dd_case_task WHERE (org_code = ? AND insurance_type = ? AND task_type = ? AND create_time LIKE ?)["01",1,0,"2023-06-05%"] 改造后：SELECT COUNT(1) FROM dd_case_task WHERE ( org_code = '01' AND insurance_type = 1 AND create_time >= '2023-06-05 00:00:00' AND create_time <= '2023-06-05 23:59:59' ) 2.已调度列表——使用了in，导致慢sql slow sql 3173 millis. SELECT COUNT(*) FROM dd_case_info WHERE (case_status = ? AND claim_status IN (?, ?, ?, ?, ?, ?, ?))[2,"0","1","2","3","4","5","6"] 改造方式：去掉in查询，注销案件也显示在已调度列表 3. slow sql 3008 millis. SELECT COUNT( * ) FROM dd_case_info WHERE (org_code = ? AND insurance_type = ? AND create_time LIKE ?)["03",1,"2023-06-05%"] 改造后：SELECT COUNT( * ) FROM dd_case_info WHERE (org_code = '03' AND insurance_type = 1 AND create_time >= '2023-06-05 00:00:00' AND create_time <= '2023-06-05 23:59:59') |
1.报表统计——调度情况表（机构任务数量）
slow sql 3941 millis. SELECT COUNT( * ) FROM dd_case_task WHERE (org_code = ? AND insurance_type = ? AND task_type = ? AND create_time LIKE ?)["01",1,0,"2023-06-05%"] 改造后：SELECT COUNT(1) FROM dd_case_task WHERE ( org_code = '01' AND insurance_type = 1 AND create_time >= '2023-06-05 00:00:00' AND create_time <= '2023-06-05 23:59:59' ) 2.已调度列表——使用了in，导致慢sql slow sql 3173 millis. SELECT COUNT(*) FROM dd_case_info WHERE (case_status = ? AND claim_status IN (?, ?, ?, ?, ?, ?, ?))[2,"0","1","2","3","4","5","6"] 改造方式：去掉in查询，注销案件也显示在已调度列表 3.报表统计——机构案件统计 slow sql 3008 millis. SELECT COUNT( * ) FROM dd_case_info WHERE (org_code = ? AND insurance_type = ? AND create_time LIKE ?)["03",1,"2023-06-05%"] 改造后：SELECT COUNT( * ) FROM dd_case_info WHERE (org_code = '03' AND insurance_type = 1 AND create_time >= '2023-06-05 00:00:00' AND create_time <= '2023-06-05 23:59:59') |
| 描述 |
1.报表统计——调度情况表（机构任务数量）
slow sql 3941 millis. SELECT COUNT( * ) FROM dd_case_task WHERE (org_code = ? AND insurance_type = ? AND task_type = ? AND create_time LIKE ?)["01",1,0,"2023-06-05%"] 改造后：SELECT COUNT(1) FROM dd_case_task WHERE ( org_code = '01' AND insurance_type = 1 AND create_time >= '2023-06-05 00:00:00' AND create_time <= '2023-06-05 23:59:59' ) 2.已调度列表——使用了in，导致慢sql slow sql 3173 millis. SELECT COUNT(*) FROM dd_case_info WHERE (case_status = ? AND claim_status IN (?, ?, ?, ?, ?, ?, ?))[2,"0","1","2","3","4","5","6"] 改造方式：去掉in查询，注销案件也显示在已调度列表 3.报表统计——机构案件统计 slow sql 3008 millis. SELECT COUNT( * ) FROM dd_case_info WHERE (org_code = ? AND insurance_type = ? AND create_time LIKE ?)["03",1,"2023-06-05%"] 改造后：SELECT COUNT( * ) FROM dd_case_info WHERE (org_code = '03' AND insurance_type = 1 AND create_time >= '2023-06-05 00:00:00' AND create_time <= '2023-06-05 23:59:59') |
1.报表统计——调度情况表（机构任务数量）
slow sql 3941 millis. SELECT COUNT( * ) FROM dd_case_task WHERE (org_code = ? AND insurance_type = ? AND task_type = ? AND create_time LIKE ?)["01",1,0,"2023-06-05%"] 改造后：SELECT COUNT(1) FROM dd_case_task WHERE ( org_code = '01' AND insurance_type = 1 AND create_time >= '2023-06-05 00:00:00' AND create_time <= '2023-06-05 23:59:59' ) 2.已调度列表——使用了in，导致慢sql slow sql 3173 millis. SELECT COUNT(*) FROM dd_case_info WHERE (case_status = ? AND claim_status IN (?, ?, ?, ?, ?, ?, ?))[2,"0","1","2","3","4","5","6"] 改造方式：去掉in查询，注销案件也显示在已调度列表 3.报表统计——机构案件统计 slow sql 3008 millis. SELECT COUNT( * ) FROM dd_case_info WHERE (org_code = ? AND insurance_type = ? AND create_time LIKE ?)["03",1,"2023-06-05%"] 改造后：SELECT COUNT( * ) FROM dd_case_info WHERE (org_code = '03' AND insurance_type = 1 AND create_time >= '2023-06-05 00:00:00' AND create_time <= '2023-06-05 23:59:59') |
| 描述 |
1.报表统计——调度情况表（机构任务数量）
slow sql 3941 millis. SELECT COUNT( * ) FROM dd_case_task WHERE (org_code = ? AND insurance_type = ? AND task_type = ? AND create_time LIKE ?)["01",1,0,"2023-06-05%"] 改造后：SELECT COUNT(1) FROM dd_case_task WHERE ( org_code = '01' AND insurance_type = 1 AND create_time >= '2023-06-05 00:00:00' AND create_time <= '2023-06-05 23:59:59' ) 2.已调度列表——使用了in，导致慢sql slow sql 3173 millis. SELECT COUNT(*) FROM dd_case_info WHERE (case_status = ? AND claim_status IN (?, ?, ?, ?, ?, ?, ?))[2,"0","1","2","3","4","5","6"] 改造方式：去掉in查询，注销案件也显示在已调度列表 3.报表统计——机构案件统计 slow sql 3008 millis. SELECT COUNT( * ) FROM dd_case_info WHERE (org_code = ? AND insurance_type = ? AND create_time LIKE ?)["03",1,"2023-06-05%"] 改造后：SELECT COUNT( * ) FROM dd_case_info WHERE (org_code = '03' AND insurance_type = 1 AND create_time >= '2023-06-05 00:00:00' AND create_time <= '2023-06-05 23:59:59') |
1.报表统计——调度情况表（机构任务数量）
slow sql 3941 millis. SELECT COUNT( * ) FROM dd_case_task WHERE (org_code = ? AND insurance_type = ? AND task_type = ? AND create_time LIKE ?)["01",1,0,"2023-06-05%"] 改造后：SELECT COUNT(1) FROM dd_case_task WHERE ( org_code = '01' AND insurance_type = 1 AND create_time >= '2023-06-05 00:00:00' AND create_time <= '2023-06-05 23:59:59' ) 2.已调度列表——使用了in，导致慢sql slow sql 3173 millis. SELECT COUNT(*) FROM dd_case_info WHERE (case_status = ? AND claim_status IN (?, ?, ?, ?, ?, ?, ?))[2,"0","1","2","3","4","5","6"] 改造方式：去掉in查询，注销案件也显示在已调度列表 3.报表统计——机构案件统计 slow sql 3008 millis. SELECT COUNT( * ) FROM dd_case_info WHERE (org_code = ? AND insurance_type = ? AND create_time LIKE ?)["03",1,"2023-06-05%"] 改造后：SELECT COUNT( * ) FROM dd_case_info WHERE (org_code = '03' AND insurance_type = 1 AND create_time >= '2023-06-05 00:00:00' AND create_time <= '2023-06-05 23:59:59') 4.WX_DP20——查询案件（视频页面自助公共池、个人池） slow SQL 3034 millis. SELECT COUNT(1) FROM dd_case_info c WHERE 1 = 1 AND c.has_commit_video = '0' AND c.video_user IS NULL AND c.org_code LIKE '03%' AND c.deal_type = '1' |
| 描述 |
1.报表统计——调度情况表（机构任务数量）
slow sql 3941 millis. SELECT COUNT( * ) FROM dd_case_task WHERE (org_code = ? AND insurance_type = ? AND task_type = ? AND create_time LIKE ?)["01",1,0,"2023-06-05%"] 改造后：SELECT COUNT(1) FROM dd_case_task WHERE ( org_code = '01' AND insurance_type = 1 AND create_time >= '2023-06-05 00:00:00' AND create_time <= '2023-06-05 23:59:59' ) 2.已调度列表——使用了in，导致慢sql slow sql 3173 millis. SELECT COUNT(*) FROM dd_case_info WHERE (case_status = ? AND claim_status IN (?, ?, ?, ?, ?, ?, ?))[2,"0","1","2","3","4","5","6"] 改造方式：去掉in查询，注销案件也显示在已调度列表 3.报表统计——机构案件统计 slow sql 3008 millis. SELECT COUNT( * ) FROM dd_case_info WHERE (org_code = ? AND insurance_type = ? AND create_time LIKE ?)["03",1,"2023-06-05%"] 改造后：SELECT COUNT( * ) FROM dd_case_info WHERE (org_code = '03' AND insurance_type = 1 AND create_time >= '2023-06-05 00:00:00' AND create_time <= '2023-06-05 23:59:59') 4.WX_DP20——查询案件（视频页面自助公共池、个人池） slow SQL 3034 millis. SELECT COUNT(1) FROM dd_case_info c WHERE 1 = 1 AND c.has_commit_video = '0' AND c.video_user IS NULL AND c.org_code LIKE '03%' AND c.deal_type = '1' |
1.报表统计——调度情况表（机构任务数量）
slow sql 3941 millis. SELECT COUNT( * ) FROM dd_case_task WHERE (org_code = ? AND insurance_type = ? AND task_type = ? AND create_time LIKE ?)["01",1,0,"2023-06-05%"] 改造后：SELECT COUNT(1) FROM dd_case_task WHERE ( org_code = '01' AND insurance_type = 1 AND create_time >= '2023-06-05 00:00:00' AND create_time <= '2023-06-05 23:59:59' ) 2.已调度列表——使用了in，导致慢sql slow sql 3173 millis. SELECT COUNT(*) FROM dd_case_info WHERE (case_status = ? AND claim_status IN (?, ?, ?, ?, ?, ?, ?))[2,"0","1","2","3","4","5","6"] 改造方式：去掉in查询，注销案件也显示在已调度列表 3.报表统计——机构案件统计 slow sql 3008 millis. SELECT COUNT( * ) FROM dd_case_info WHERE (org_code = ? AND insurance_type = ? AND create_time LIKE ?)["03",1,"2023-06-05%"] 改造后：SELECT COUNT( * ) FROM dd_case_info WHERE (org_code = '03' AND insurance_type = 1 AND create_time >= '2023-06-05 00:00:00' AND create_time <= '2023-06-05 23:59:59') 4.WX_DP20——查询案件（视频页面自助公共池、个人池） slow SQL 3034 millis. SELECT COUNT(1) FROM dd_case_info c WHERE 1 = 1 AND c.has_commit_video = '0' AND c.video_user IS NULL AND c.org_code LIKE '03%' AND c.deal_type = '1' 改造方式：org_code 条件改成等于（案件表的org_code 字段全部都存的是二级机构） |
| 描述 |
1.报表统计——调度情况表（机构任务数量）
slow sql 3941 millis. SELECT COUNT( * ) FROM dd_case_task WHERE (org_code = ? AND insurance_type = ? AND task_type = ? AND create_time LIKE ?)["01",1,0,"2023-06-05%"] 改造后：SELECT COUNT(1) FROM dd_case_task WHERE ( org_code = '01' AND insurance_type = 1 AND create_time >= '2023-06-05 00:00:00' AND create_time <= '2023-06-05 23:59:59' ) 2.已调度列表——使用了in，导致慢sql slow sql 3173 millis. SELECT COUNT(*) FROM dd_case_info WHERE (case_status = ? AND claim_status IN (?, ?, ?, ?, ?, ?, ?))[2,"0","1","2","3","4","5","6"] 改造方式：去掉in查询，注销案件也显示在已调度列表 3.报表统计——机构案件统计 slow sql 3008 millis. SELECT COUNT( * ) FROM dd_case_info WHERE (org_code = ? AND insurance_type = ? AND create_time LIKE ?)["03",1,"2023-06-05%"] 改造后：SELECT COUNT( * ) FROM dd_case_info WHERE (org_code = '03' AND insurance_type = 1 AND create_time >= '2023-06-05 00:00:00' AND create_time <= '2023-06-05 23:59:59') 4.WX_DP20——查询案件（视频页面自助公共池、个人池） slow SQL 3034 millis. SELECT COUNT(1) FROM dd_case_info c WHERE 1 = 1 AND c.has_commit_video = '0' AND c.video_user IS NULL AND c.org_code LIKE '03%' AND c.deal_type = '1' 改造方式：org_code 条件改成等于（案件表的org_code 字段全部都存的是二级机构） |
1.报表统计——调度情况表（机构任务数量）
slow sql 3941 millis. SELECT COUNT( * ) FROM dd_case_task WHERE (org_code = ? AND insurance_type = ? AND task_type = ? AND create_time LIKE ?)["01",1,0,"2023-06-05%"] 改造后：SELECT COUNT(1) FROM dd_case_task WHERE ( org_code = '01' AND insurance_type = 1 AND create_time >= '2023-06-05 00:00:00' AND create_time <= '2023-06-05 23:59:59' ) 2.已调度列表——使用了in，导致慢sql slow sql 3173 millis. SELECT COUNT(*) FROM dd_case_info WHERE (case_status = ? AND claim_status IN (?, ?, ?, ?, ?, ?, ?))[2,"0","1","2","3","4","5","6"] 改造方式：去掉in查询，注销案件也显示在已调度列表 3.报表统计——机构案件统计 slow sql 3008 millis. SELECT COUNT( * ) FROM dd_case_info WHERE (org_code = ? AND insurance_type = ? AND create_time LIKE ?)["03",1,"2023-06-05%"] 改造后：SELECT COUNT( * ) FROM dd_case_info WHERE (org_code = '03' AND insurance_type = 1 AND create_time >= '2023-06-05 00:00:00' AND create_time <= '2023-06-05 23:59:59') 4.WX_DP20——查询案件（视频页面自助公共池、个人池） slow SQL 3034 millis. SELECT COUNT(1) FROM dd_case_info c WHERE 1 = 1 AND c.has_commit_video = '0' AND c.video_user IS NULL AND c.org_code LIKE '03%' AND c.deal_type = '1' 改造方式：org_code 条件改成等于（案件表的org_code 字段全部都存的是二级机构）————暂时不做调整，有三级机构的视频坐席账号 |
| 描述 |
1.报表统计——调度情况表（机构任务数量）
slow sql 3941 millis. SELECT COUNT( * ) FROM dd_case_task WHERE (org_code = ? AND insurance_type = ? AND task_type = ? AND create_time LIKE ?)["01",1,0,"2023-06-05%"] 改造后：SELECT COUNT(1) FROM dd_case_task WHERE ( org_code = '01' AND insurance_type = 1 AND create_time >= '2023-06-05 00:00:00' AND create_time <= '2023-06-05 23:59:59' ) 2.已调度列表——使用了in，导致慢sql slow sql 3173 millis. SELECT COUNT(*) FROM dd_case_info WHERE (case_status = ? AND claim_status IN (?, ?, ?, ?, ?, ?, ?))[2,"0","1","2","3","4","5","6"] 改造方式：去掉in查询，注销案件也显示在已调度列表 3.报表统计——机构案件统计 slow sql 3008 millis. SELECT COUNT( * ) FROM dd_case_info WHERE (org_code = ? AND insurance_type = ? AND create_time LIKE ?)["03",1,"2023-06-05%"] 改造后：SELECT COUNT( * ) FROM dd_case_info WHERE (org_code = '03' AND insurance_type = 1 AND create_time >= '2023-06-05 00:00:00' AND create_time <= '2023-06-05 23:59:59') 4.WX_DP20——查询案件（视频页面自助公共池、个人池） slow SQL 3034 millis. SELECT COUNT(1) FROM dd_case_info c WHERE 1 = 1 AND c.has_commit_video = '0' AND c.video_user IS NULL AND c.org_code LIKE '03%' AND c.deal_type = '1' 改造方式：org_code 条件改成等于（案件表的org_code 字段全部都存的是二级机构）————暂时不做调整，有三级机构的视频坐席账号 |
1.报表统计——调度情况表（机构任务数量）
slow sql 3941 millis. SELECT COUNT( * ) FROM dd_case_task WHERE (org_code = ? AND insurance_type = ? AND task_type = ? AND create_time LIKE ?)["01",1,0,"2023-06-05%"] 改造后：SELECT COUNT(1) FROM dd_case_task WHERE ( org_code = '01' AND insurance_type = 1 AND create_time >= '2023-06-05 00:00:00' AND create_time <= '2023-06-05 23:59:59' ) 2.已调度列表——使用了in，导致慢sql slow sql 3173 millis. SELECT COUNT(*) FROM dd_case_info WHERE (case_status = ? AND claim_status IN (?, ?, ?, ?, ?, ?, ?))[2,"0","1","2","3","4","5","6"] 改造方式：去掉in查询，注销案件也显示在已调度列表 3.报表统计——机构案件统计 slow sql 3008 millis. SELECT COUNT( * ) FROM dd_case_info WHERE (org_code = ? AND insurance_type = ? AND create_time LIKE ?)["03",1,"2023-06-05%"] 改造后：SELECT COUNT( * ) FROM dd_case_info WHERE (org_code = '03' AND insurance_type = 1 AND create_time >= '2023-06-05 00:00:00' AND create_time <= '2023-06-05 23:59:59') 4.WX_DP20——查询案件（视频页面自助公共池——总数统计） slow SQL 3034 millis. SELECT COUNT(1) FROM dd_case_info c WHERE 1 = 1 AND c.has_commit_video = '0' AND c.video_user IS NULL AND c.org_code LIKE '03%' AND c.deal_type = '1' 改造方式：org_code 条件改成等于（案件表的org_code 字段全部都存的是二级机构）————暂时不做调整，有三级机构的视频坐席账号 5.WX_DP20——查询案件（视频页面自助公共池——列表数据 ） slow SQL 3270 millis. SELECT c.regist_no, c.license_no, c.insured_name, c.linker_name, c.linker_phone, c.damage_time, c.report_time, c.damage_place, c.damage_remark, c.case_status, c.subrogation, c.vip_level, c.insurance_type, c.org_code, c.org_name, c.video_user, c.video_user_name, c.is_new_policy_flag, c.is_black_list, c.black_list_type, c.black_list_type_desc, c.deal_type, c.has_commit_video FROM dd_case_info c WHERE 1 = 1 AND c.has_commit_video = '0' AND c.video_user IS NULL AND c.org_code LIKE '03%' AND c.deal_type = '1' LIMIT 0,100 |
| 描述 |
1.报表统计——调度情况表（机构任务数量）
slow sql 3941 millis. SELECT COUNT( * ) FROM dd_case_task WHERE (org_code = ? AND insurance_type = ? AND task_type = ? AND create_time LIKE ?)["01",1,0,"2023-06-05%"] 改造后：SELECT COUNT(1) FROM dd_case_task WHERE ( org_code = '01' AND insurance_type = 1 AND create_time >= '2023-06-05 00:00:00' AND create_time <= '2023-06-05 23:59:59' ) 2.已调度列表——使用了in，导致慢sql slow sql 3173 millis. SELECT COUNT(*) FROM dd_case_info WHERE (case_status = ? AND claim_status IN (?, ?, ?, ?, ?, ?, ?))[2,"0","1","2","3","4","5","6"] 改造方式：去掉in查询，注销案件也显示在已调度列表 3.报表统计——机构案件统计 slow sql 3008 millis. SELECT COUNT( * ) FROM dd_case_info WHERE (org_code = ? AND insurance_type = ? AND create_time LIKE ?)["03",1,"2023-06-05%"] 改造后：SELECT COUNT( * ) FROM dd_case_info WHERE (org_code = '03' AND insurance_type = 1 AND create_time >= '2023-06-05 00:00:00' AND create_time <= '2023-06-05 23:59:59') 4.WX_DP20——查询案件（视频页面自助公共池——总数统计） slow SQL 3034 millis. SELECT COUNT(1) FROM dd_case_info c WHERE 1 = 1 AND c.has_commit_video = '0' AND c.video_user IS NULL AND c.org_code LIKE '03%' AND c.deal_type = '1' 改造方式：org_code 条件改成等于（案件表的org_code 字段全部都存的是二级机构）————暂时不做调整，有三级机构的视频坐席账号 5.WX_DP20——查询案件（视频页面自助公共池——列表数据 ） slow SQL 3270 millis. SELECT c.regist_no, c.license_no, c.insured_name, c.linker_name, c.linker_phone, c.damage_time, c.report_time, c.damage_place, c.damage_remark, c.case_status, c.subrogation, c.vip_level, c.insurance_type, c.org_code, c.org_name, c.video_user, c.video_user_name, c.is_new_policy_flag, c.is_black_list, c.black_list_type, c.black_list_type_desc, c.deal_type, c.has_commit_video FROM dd_case_info c WHERE 1 = 1 AND c.has_commit_video = '0' AND c.video_user IS NULL AND c.org_code LIKE '03%' AND c.deal_type = '1' LIMIT 0,100 |
1.报表统计——调度情况表（机构任务数量）
slow sql 3941 millis. SELECT COUNT( * ) FROM dd_case_task WHERE (org_code = ? AND insurance_type = ? AND task_type = ? AND create_time LIKE ?)["01",1,0,"2023-06-05%"] 改造后：SELECT COUNT(1) FROM dd_case_task WHERE ( org_code = '01' AND insurance_type = 1 AND create_time >= '2023-06-05 00:00:00' AND create_time <= '2023-06-05 23:59:59' ) 2.已调度列表——使用了in，导致慢sql slow sql 3173 millis. SELECT COUNT(*) FROM dd_case_info WHERE (case_status = ? AND claim_status IN (?, ?, ?, ?, ?, ?, ?))[2,"0","1","2","3","4","5","6"] 改造方式：去掉in查询，注销案件也显示在已调度列表 3.报表统计——机构案件统计 slow sql 3008 millis. SELECT COUNT( * ) FROM dd_case_info WHERE (org_code = ? AND insurance_type = ? AND create_time LIKE ?)["03",1,"2023-06-05%"] 改造后：SELECT COUNT( * ) FROM dd_case_info WHERE (org_code = '03' AND insurance_type = 1 AND create_time >= '2023-06-05 00:00:00' AND create_time <= '2023-06-05 23:59:59') 4.WX_DP20——查询案件（视频页面自助公共池——总数统计） slow SQL 3034 millis. SELECT COUNT(1) FROM dd_case_info c WHERE 1 = 1 AND c.has_commit_video = '0' AND c.video_user IS NULL AND c.org_code LIKE '03%' AND c.deal_type = '1' 改造方式：org_code 条件改成等于（案件表的org_code 字段全部都存的是二级机构）————暂时不做调整，有三级机构的视频坐席账号 5.WX_DP20——查询案件（视频页面自助公共池——列表数据 ） slow SQL 3270 millis. SELECT c.regist_no, c.license_no, c.insured_name, c.linker_name, c.linker_phone, c.damage_time, c.report_time, c.damage_place, c.damage_remark, c.case_status, c.subrogation, c.vip_level, c.insurance_type, c.org_code, c.org_name, c.video_user, c.video_user_name, c.is_new_policy_flag, c.is_black_list, c.black_list_type, c.black_list_type_desc, c.deal_type, c.has_commit_video FROM dd_case_info c WHERE 1 = 1 AND c.has_commit_video = '0' AND c.video_user IS NULL AND c.org_code LIKE '03%' AND c.deal_type = '1' LIMIT 0,100 改造方式：org_code 条件改成等于（案件表的org_code 字段全部都存的是二级机构）————暂时不做调整，有三级机构的视频坐席账号 |
| 描述 |
1.报表统计——调度情况表（机构任务数量）
slow sql 3941 millis. SELECT COUNT( * ) FROM dd_case_task WHERE (org_code = ? AND insurance_type = ? AND task_type = ? AND create_time LIKE ?)["01",1,0,"2023-06-05%"] 改造后：SELECT COUNT(1) FROM dd_case_task WHERE ( org_code = '01' AND insurance_type = 1 AND create_time >= '2023-06-05 00:00:00' AND create_time <= '2023-06-05 23:59:59' ) 2.已调度列表——使用了in，导致慢sql slow sql 3173 millis. SELECT COUNT(*) FROM dd_case_info WHERE (case_status = ? AND claim_status IN (?, ?, ?, ?, ?, ?, ?))[2,"0","1","2","3","4","5","6"] 改造方式：去掉in查询，注销案件也显示在已调度列表 3.报表统计——机构案件统计 slow sql 3008 millis. SELECT COUNT( * ) FROM dd_case_info WHERE (org_code = ? AND insurance_type = ? AND create_time LIKE ?)["03",1,"2023-06-05%"] 改造后：SELECT COUNT( * ) FROM dd_case_info WHERE (org_code = '03' AND insurance_type = 1 AND create_time >= '2023-06-05 00:00:00' AND create_time <= '2023-06-05 23:59:59') 4.WX_DP20——查询案件（视频页面自助公共池——总数统计） slow SQL 3034 millis. SELECT COUNT(1) FROM dd_case_info c WHERE 1 = 1 AND c.has_commit_video = '0' AND c.video_user IS NULL AND c.org_code LIKE '03%' AND c.deal_type = '1' 改造方式：org_code 条件改成等于（案件表的org_code 字段全部都存的是二级机构）————暂时不做调整，有三级机构的视频坐席账号 5.WX_DP20——查询案件（视频页面自助公共池——列表数据 ） slow SQL 3270 millis. SELECT c.regist_no, c.license_no, c.insured_name, c.linker_name, c.linker_phone, c.damage_time, c.report_time, c.damage_place, c.damage_remark, c.case_status, c.subrogation, c.vip_level, c.insurance_type, c.org_code, c.org_name, c.video_user, c.video_user_name, c.is_new_policy_flag, c.is_black_list, c.black_list_type, c.black_list_type_desc, c.deal_type, c.has_commit_video FROM dd_case_info c WHERE 1 = 1 AND c.has_commit_video = '0' AND c.video_user IS NULL AND c.org_code LIKE '03%' AND c.deal_type = '1' LIMIT 0,100 改造方式：org_code 条件改成等于（案件表的org_code 字段全部都存的是二级机构）————暂时不做调整，有三级机构的视频坐席账号 |
1.报表统计——调度情况表（机构任务数量）
slow sql 3941 millis. SELECT COUNT( * ) FROM dd_case_task WHERE (org_code = ? AND insurance_type = ? AND task_type = ? AND create_time LIKE ?)["01",1,0,"2023-06-05%"] 改造后：SELECT COUNT(1) FROM dd_case_task WHERE ( org_code = '01' AND insurance_type = 1 AND create_time >= '2023-06-05 00:00:00' AND create_time <= '2023-06-05 23:59:59' ) 2.已调度列表——使用了in，导致慢sql slow sql 3173 millis. SELECT COUNT(*) FROM dd_case_info WHERE (case_status = ? AND claim_status IN (?, ?, ?, ?, ?, ?, ?))[2,"0","1","2","3","4","5","6"] 改造方式：去掉in查询，注销案件也显示在已调度列表 3.报表统计——机构案件统计 slow sql 3008 millis. SELECT COUNT( * ) FROM dd_case_info WHERE (org_code = ? AND insurance_type = ? AND create_time LIKE ?)["03",1,"2023-06-05%"] 改造后：SELECT COUNT( * ) FROM dd_case_info WHERE (org_code = '03' AND insurance_type = 1 AND create_time >= '2023-06-05 00:00:00' AND create_time <= '2023-06-05 23:59:59') 4.WX_DP20——查询案件（视频页面自助公共池——总数统计） slow SQL 3034 millis. SELECT COUNT(1) FROM dd_case_info c WHERE 1 = 1 AND c.has_commit_video = '0' AND c.video_user IS NULL AND c.org_code LIKE '03%' AND c.deal_type = '1' 改造方式：org_code 条件改成等于（案件表的org_code 字段全部都存的是二级机构）；或者查询入参增加时间范围————暂时不做调整，有三级机构的视频坐席账号 5.WX_DP20——查询案件（视频页面自助公共池——列表数据 ） slow SQL 3270 millis. SELECT c.regist_no, c.license_no, c.insured_name, c.linker_name, c.linker_phone, c.damage_time, c.report_time, c.damage_place, c.damage_remark, c.case_status, c.subrogation, c.vip_level, c.insurance_type, c.org_code, c.org_name, c.video_user, c.video_user_name, c.is_new_policy_flag, c.is_black_list, c.black_list_type, c.black_list_type_desc, c.deal_type, c.has_commit_video FROM dd_case_info c WHERE 1 = 1 AND c.has_commit_video = '0' AND c.video_user IS NULL AND c.org_code LIKE '03%' AND c.deal_type = '1' LIMIT 0,100 改造方式：org_code 条件改成等于（案件表的org_code 字段全部都存的是二级机构）；或者查询入参增加时间范围————暂时不做调整，有三级机构的视频坐席账号 |
其中4 5改造为根据机构编码=查询或者查询入参增加时间范围，查询效率在1秒以内，目前查询效率3至4秒——是否要这样改造等确认