用mysql数据库的存储过程使用游标效率低下怎么解决

水上冰石 2015-07-03 05:43:35
在存储过程中使用游标进行关联删除,每张表数据量在10-100w之前不等,代码如下:
BEGIN

DECLARE hprocessInstanceId bigint DEFAULT 0; -- 历史流程实例id
DECLARE hprocessInstanceIdStarttime CHAR default ''; -- 历史流程实例启动时间
DECLARE hprocessInstanceIdEndtime CHAR default ''; -- 历史流程实例结束时间

DECLARE hactinstId BIGINT default 0; -- 历史活动实例id

DECLARE htaskId BIGINT default 0; -- 历史人工任务id

DECLARE hvarId BIGINT default 0; -- 历史流程变量id

DECLARE rexecutionId bigint default 0; -- 正在执行流程实例id

DECLARE rvarId bigint default 0; -- 正在执行流程变量id

DECLARE rtaskId bigint default 0; -- 正在执行人工任务id

DECLARE rswinmlanceId bigint DEFAULT 0; -- 泳道id,为了删除partation表记录,本项目无

记录

DECLARE processCompleteFlag int default 0; -- 流程是否结束标识
DECLARE taskCompleteFlag int default 0; -- 任务是否结束标识
DECLARE doneFlag INT DEFAULT 0; -- 完成标识,0:未完成;1:已完成
DECLARE notfound INT DEFAULT 0;-- 是否未找到数据 标记
-- 启动事物
-- START TRANSACTION;
-- ALTER TABLE jbpm4_hist_var DROP FOREIGN KEY 'FK_HVAR_HPROCI';
-- ALTER TABLE jbpm4_hist_var ADD CONSTRAINT 'FK_HVAR_HPROCI' FOREIGN KEY ('HPROCI_')

REFERENCES jbpm4_hist_procinst('DBID_') ON DELETE CASCADE ;


/* 声明历史流程实例的游标 */
DECLARE hprocessInstanceRS CURSOR FOR SELECT dbid_,START_,END_ FROM

jbpm4_hist_procinst where START_>="2014-00-00 00:00:00" and START_<"2015-00-00 00:00:00";

/* 声明历史活动实例的游标 */
DECLARE hactinstRS CURSOR FOR SELECT dbid_,HTASK_ FROM jbpm4_hist_actinst where

HPROCI_=hprocessInstanceId;

/* 声明历史活动实例的游标 */
DECLARE htaskRS CURSOR FOR SELECT dbid_ FROM jbpm4_hist_task where DBID_=hactinstId;

/* 声明历史活动实例的游标 */
DECLARE hvarRS CURSOR FOR SELECT dbid_ FROM jbpm4_hist_var where HTASK_=rtaskId;

/** 声明正在执行流程实例的游标(历史表中因为各种原因未完成的) **/
DECLARE rexecutionRS CURSOR FOR SELECT dbid_ FROM jbpm4_execution where

DBID_=hprocessInstanceId;

/** 声明正在执行流程变量的游标(只删除2014年条件下由于各种原因未完成的流程实例所对应的

流程变量) */
DECLARE rvarRS CURSOR FOR SELECT dbid_ FROM jbpm4_variable where

EXECUTION_=hprocessInstanceId;

/** 声明正在执行的人工任务的游标(只是2014年开始的流程实例所对应的) **/
DECLARE rtaskRS CURSOR FOR SELECT dbid_ FROM jbpm4_task where DBID_=rtaskId;

/** 声明泳道的结果集游标,为了删除paritation表,该项目没有记录,实际删除条数为0 **/
DECLARE rswinmlanceRS CURSOR FOR SELECT dbid_ FROM jbpm4_swimlane where

dbid_=rswinmlanceId;

/* 异常处理 */
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET doneFlag = 1;

/** 删除s,使用嵌套循环..... **/
SET FOREIGN_KEY_CHECKS = 0;

OPEN hprocessInstanceRS;
FETCH hprocessInstanceRS INTO

hprocessInstanceId,hprocessInstanceIdStarttime,hprocessInstanceIdEndtime;-- 获取历史流程实例表的数据数



REPEAT
IF hprocessInstanceIdEndtime='' THEN
-- 没有结束,执行删除正在执行的流程实例表
/** 1.查询正在执行的流程实例记录s **/
SET rexecutionId=hprocessInstanceId; -- 未完成的流程实

例与正在执行的流程实例id做对应
OPEN rexecutionRS;
FETCH rexecutionRS INTO rexecutionId;
REPEAT
/** 2.查询该流程实例下的所有正在执行的

流程变量记录s 2**/
OPEN hvarRS;
FETCH hvarRS INTO rvarId;
REPEAT
/** 3.删除正在执行的流

程变量所对应的人工任务记录s 3**/
delete from jbpm4_task

where dbid_=rvarId;
/** 3.删除正在执行的流

程变量所对应的人工任务记录e 3**/
delete from

jbpm4_variable where dbid_=rvarId; -- 单条删除流程变量记录
FETCH hvarRS INTO

rvarId;
UNTIL doneFlag END REPEAT;
CLOSE hvarRS;
/** 2.查询该流程实例下的所有正在执行的

流程变量记录e 2**/
delete from jbpm4_execution where

dbid_=rexecutionId; -- 单条删除流程对象记录
FETCH rexecutionRS INTO rexecutionId;
UNTIL doneFlag END REPEAT;
CLOSE rexecutionRS;

END IF;
/*** ======删除历史流程记录表相关数据===== **/

/** 1.查询活动实例表 s **/
OPEN hactinstRS;
FETCH hactinstRS INTO hactinstId,htaskId;

REPEAT
/** 2.查询历史人工活动表记录s **/
OPEN htaskRS;
FETCH htaskRS INTO htaskId;
REPEAT
/** 3.删除历史人工任务

**/
delete from

jbpm4_hist_task where dbid_=htaskId;
FETCH htaskRS INTO

htaskId;
UNTIL doneFlag END REPEAT;
CLOSE htaskRS;
/** 2.查询历史人工活动表记录s **/
FETCH hactinstRS INTO hactinstId,htaskId;
UNTIL doneFlag END REPEAT;
CLOSE hactinstRS;

/** 1.查询活动实例表 e **/
/*** ======删除历史流程记录表相关数据===== **/

/** 删除历史活动实例表 **/
delete from jbpm4_hist_actinst where

HPROCI_=hprocessInstanceId;
/** 删除历史流程变量表 **/
delete from jbpm4_hist_var where HPROCI_=hprocessInstanceId;

SET doneFlag=0;
FETCH hprocessInstanceRS INTO

hprocessInstanceId,hprocessInstanceIdStarttime,hprocessInstanceIdEndtime;-- 获取历史流程实例表的数据数



UNTIL doneFlag END REPEAT;
CLOSE hprocessInstanceRS;
/** 删除历史流程实例记录 **/
delete from jbpm4_hist_procinst where START_>='2014-00-00 00:00:00' and

START_<'2015-00-00 00:00:00';
SET FOREIGN_KEY_CHECKS = 1;
END

功能基本能实现,但是效率很低,求解答
...全文
2022 1 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
1 条回复
切换为时间正序
请发表友善的回复…
发表回复
rick-he 2015-07-11
  • 打赏
  • 举报
回复
效率低,可以把这个拆分来优化啊,可能是那一块慢而已

8,030

社区成员

发帖
与我相关
我的任务
社区描述
高性能数据库开发
社区管理员
  • 高性能数据库开发社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧