2 lovebosom lovebosom 于 2016.03.30 15:13 提问

请教存储过程执行很慢,如何优化?

BEGIN
DECLARE personPlaneId BIGINT;

DECLARE starttime datetime; #开始时间
DECLARE endtime datetime; #结束时间
DECLARE sumStudyTime INT; #当前学时
DECLARE targetStudyTime INT; #目标学时
DECLARE id1_count BIGINT default 0; #t_stage_info 第一阶段count
DECLARE id2_count BIGINT default 0; #t_stage_info 第二阶段count
DECLARE id3_count BIGINT default 0; #t_stage_info 第三阶段count
DECLARE firstEndTime datetime; #第一阶段结束时间
DECLARE secEndTime datetime; #第二阶段结束时间
DECLARE thirdEndTime datetime; #第三阶段结束时间
DECLARE done INT DEFAULT 0;

    #声明光标
        DECLARE cur1 CURSOR FOR
            SELECT p1.id,
            p1.start_learn_time start_time,
            p1.last_date end_time,
            p1.sum_study_time,p1.target_study_time 
            FROM t_person_plan p1 LEFT JOIN t_person_plan_record p2  
            ON p1.id = p2.person_plan_id WHERE p1.license_type = 7  
            AND p1.last_date <= CURRENT_DATE();
#t_person_plan_record p2 这个表数据大概500多万条
    #当游标到达尾部时,mysql自动设置done=1
  declare continue handler for SQLSTATE '02000' SET done = 1;    

    OPEN cur1;  
        REPEAT
             /* 移动游标并赋值 */  
    fetch cur1 into personPlaneId,startTime,endTime,sumStudyTime,targetStudyTime; 
            if NOT done  then   
                        #获取第一阶段结束时间
                        SET firstEndTime = (SELECT add_time FROM t_person_plan_record WHERE person_plan_id=personPlaneId AND chapter_id=29611);
                        #获取第二阶段结束时间
                        SET secEndTime = (SELECT add_time FROM t_person_plan_record WHERE person_plan_id=personPlaneId AND chapter_id=29625);
                        #获取第三阶段结束时间
                        SET thirdEndTime = (SELECT add_time FROM t_person_plan_record WHERE person_plan_id=personPlaneId AND chapter_id=29734);
                        #获取主键标识
                        SELECT count(1) into id1_count from t_stage_info where person_plan_id=personPlaneId AND stage=1;
                        SELECT count(1) into id2_count from t_stage_info where person_plan_id=personPlaneId AND stage=2;
                        SELECT count(1) into id3_count from t_stage_info where person_plan_id=personPlaneId AND stage=3;

                    IF sumStudyTime <= 12 THEN

                        IF id1_count=0 THEN
                            INSERT INTO t_stage_info(person_plan_id,start_time,end_time,sum_study_time,target_study_time,stage) 
                                VALUES(personPlaneId,startTime,endTime,sumStudyTime,targetStudyTime,1); 
                        ELSE
                            UPDATE t_stage_info SET end_time=endTime,sum_study_time=sumStudyTime WHERE person_plan_id=personPlaneId AND stage=1;
                        END IF;

                    #当学到第二阶段时
                    ELSEIF sumStudyTime>12 AND sumStudyTime<=14 THEN

                        #更新第一阶段数据 
                        IF id1_count=0 THEN
                            INSERT INTO t_stage_info(person_plan_id,start_time,end_time,sum_study_time,target_study_time,stage) 
                                VALUES(personPlaneId,startTime,firstEndTime,sumStudyTime,targetStudyTime,1); 
                        ELSE
                            UPDATE t_stage_info SET end_time=firstEndTime,sum_study_time=sumStudyTime WHERE person_plan_id=personPlaneId AND stage=1;
                        END IF;
                        #更新第二阶段数据
                        IF id2_count=0 THEN
                            INSERT INTO t_stage_info(person_plan_id,start_time,end_time,sum_study_time,target_study_time,stage) 
                                VALUES(personPlaneId,firstEndTime,endTime,sumStudyTime,targetStudyTime,2); 
                        ELSE
                            UPDATE t_stage_info SET end_time=endTime,sum_study_time=sumStudyTime WHERE person_plan_id=personPlaneId AND stage=2;
                        END IF;

                    #当学到第三阶段时
                    ELSEIF sumStudyTime>14 THEN

                        #如果thirdEndTime为空则取最后学习的时间
                        SET thirdEndTime = CASE WHEN thirdEndTime IS NULL THEN endTime ELSE thirdEndTime END;

                        #更新第一阶段数据
                        IF id1_count=0 THEN
                            INSERT INTO t_stage_info(person_plan_id,start_time,end_time,sum_study_time,target_study_time,stage) 
                                VALUES(personPlaneId,startTime,firstEndTime,sumStudyTime,targetStudyTime,1); 
                        ELSE
                            UPDATE t_stage_info SET end_time=firstEndTime,sum_study_time=sumStudyTime WHERE person_plan_id=personPlaneId AND stage=1;
                        END IF;
                        #更新第二阶段数据
                        IF id2_count=0 THEN
                            INSERT INTO t_stage_info(person_plan_id,start_time,end_time,sum_study_time,target_study_time,stage) 
                                VALUES(personPlaneId,firstEndTime,secEndTime,sumStudyTime,targetStudyTime,2); 
                        ELSE
                            UPDATE t_stage_info SET end_time=secEndTime,sum_study_time=sumStudyTime WHERE person_plan_id=personPlaneId AND stage=2;
                        END IF;
                        #更新第三阶段数据
                        IF id3_count=0 THEN
                            INSERT INTO t_stage_info(person_plan_id,start_time,end_time,sum_study_time,target_study_time,stage) 
                                VALUES(personPlaneId,secEndTime,thirdEndTime,sumStudyTime,targetStudyTime,3); 
                        ELSE
                            UPDATE t_stage_info SET end_time=thirdEndTime,sum_study_time=sumStudyTime WHERE person_plan_id=personPlaneId AND stage=3;
                        END IF;

                    END IF;

    end if;
        UNTIL done END REPEAT;  
    close cur1; 

END
现在执行了2小时才有了10几万数据,
我在t_person_plan_record 表里对‘person_plan_id ’建了索引,
在表t_stage_info 对person_plan_id ’建立了索引,其他我就不知道怎么优化了,
请问大神,还能怎么优化比较好呢?

4个回答

qq978450288
qq978450288   2016.03.30 15:18
已采纳

CURSOR 有游标就不可能快的起来~! 换换思路 看能不能通过批量update 解决你现在用游标在解决的事情

lovebosom
lovebosom 回复qq978450288: 嗯,这倒是个好思路。采纳你了哈。
2 年多之前 回复
qq978450288
qq978450288 回复Mr_猩猩之火: 我没看你现在的存储过程 太长了 我是根据你的描述 提供思路。 你可以看看这个思路行不行 批量插入 和批量修改的 例子网上多的是
2 年多之前 回复
qq978450288
qq978450288 回复Mr_猩猩之火: 让2个表关联的数据组成一个视图 或者临时表 然后left join a 如果 能连不到a(a.id is null) 这部分信息 批量插入 如果能(a.id is not null) 批量更新
2 年多之前 回复
lovebosom
lovebosom 你好,需求是这样:在游标对两个表的关联结果集遍历的时候,对另一个表a操作,如果a有这条数据,就update,没有就insert,我不清楚能不能批量操作。
2 年多之前 回复
CSDNXIAON
CSDNXIAON   2016.03.30 15:23

存储过程执行很慢,请求优化
----------------------同志你好,我是CSDN问答机器人小N,奉组织之命为你提供参考答案,编程尚未成功,同志仍需努力!

iamadk
iamadk   2016.03.30 16:24

你可以打开mysql的慢查询记录,查看一下执行慢的语句是哪个,然后再具体定位优化

lovebosom
lovebosom 我吧其中的sql语句拿出来,单独运行,时间都不长,也许是数据太大了,百万数据,我在看看吧
2 年多之前 回复
lovebosom
lovebosom   2016.03.30 15:18

如果我 想看哪一些sql语句执行慢,怎么操作 能看到花多长时间呢?

Csdn user default icon
上传中...
上传图片
插入图片
准确详细的回答,更有利于被提问者采纳,从而获得C币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!
其他相关推荐
oracle存储过程处理数据过慢解决方法之一
Oracle调度程序慢,数据量大,可以以下两种解决方法,你试一下: 1、清理回收站,purge dba_recyclebin,后续也可以关闭回收站,z注意的是系统不属于OLTP系统,使用此方法才不会有影响 关闭回收站不影响系统使用(alter system set recyclebin=off deferred); 2、查看这个程序中执行到哪一步花费时间比较多,对执行花费时间较长段的表进行统
DB2中存储过程执行慢问题故障处理
其实这个问题是以前同一个客户遇见的问题,当时一个工程师解决后记录的过程如下: 应用同事反映但是对应到执行存储过程,执行了2,3个小时了,还没出来结果。 存储过程主要是执行一条update sql语句,单独将语句拿出来,clp命令行执行很快,2-3s即可执行完成。   执行的SP: call pdw.P_OCS_ACTIVE_UPDATE('20120304',?)   存储过程主
mysql 方法或者存储过程执行慢的调试方法
第一步:修改/etc/my.cnf文件,找到[mysqld] 里面加入 #执行的sql log=/tmp/logs/mysqld.log #记录sql执行超过下面设置时间的sql log-slow-queries = /tmp/mysqlslowquery.log #执行时间大于等于1秒 long_query_time = 1 然后你可以tail -f /tmp/logs/my
客户端直接执行存储过程正常但代码调用慢的问题
JAVA调用SQL后台存储过程时,有时突然就变得很慢,在后台直接执行存储过程没问题,但在前台调用存储过程时就是很慢,而且在前台调用成功后,再次调用还是一样的慢,但更新一下存储过程再调用就很快了。但这始终不能彻底解决问题,过段时间又会出来同样的问题。   解决办法: 简单方法一:在可能比较耗时的语句后面加上option(recompile)   方法二:新编译存储过程
mysql优化存储过程中批量插入的速度
第一次操作,每一条数据执行一次插入操作。 一万条数据花费34s,太慢。 CREATE PROCEDURE insertPro(in sum INT) BEGIN DECLARE count INT DEFAULT 0; DECLARE i INT DEFAULT 0; set count=0; set i = rand() * 10000; while count<sum do inse
解决存储过程执行快,但程序调用则执行慢的问题
这两天遇到一个问题令人比较郁闷,一个大概120行左右的存储过程在SQL Server2012的查询分析器里面执行, 速度非常理想,1秒不到,即可筛选抓取到大概500条数据记录。 但在C#程序代码里调用,就提示连接超时。把CommandTimeout设置为300,就要3分钟左右时间才能显示出来, 检查了几遍代码也没有发现错误。问题依旧。 原因分析: 1、由于在查询分析器里执行速度很快,
【原】SQL存储过程调用慢,但是重新编译一下存储过程就很快
.NET调用存储过程时,有时候会变的很慢甚至会timeout,但是再执行一下存储过程,再调用就会很快。   解决办法: 1. 在可能比较耗时的语句后面加上option(recompile) 2. 创建存储过程时在其定义中指定 WITH RECOMPILE 选项,表明 SQL Server 将不对该存储过程计划进行高速缓存;该存储过程将在每次执行时都重新编译。当存储过程的参数值在各次执行间都
[PL/SQL] oracle sql语句 存储过程执行慢,单独执行快
1、执行计划情况 当存储过程挂住的时候,看看V$SESSION里面的 SQL_ID, SQL_CHILD_NUMBER 再根据这两个信息用DBMS_XPLAN.DISPLAY_CURSOR把计划拿出来看看。 2、锁表情况 Oracle: select  ao.owner,ao.object_name,lo.LOCKED_MODE,lo.OS_USE
SQL优化(四) PostgreSQL存储过程
本文介绍了存储过程的概念,优势,并结合实例讲解了存储过程在PostgreSQL中的实现,注意事项
oracle 存储过程中执行 update 很慢
如题,造成这个现象可能的原因 是表被锁了 执行下列sql语句 select sess.sid, sess.serial#, lo.oracle_username, lo.os_user_name, ao.object_name, lo.locked_mode from v$locked_object lo, dba_objects ao, v$session sess where ao.obj