在上篇《java 并发：多线程锁计数器》中提到过，在工作中碰到一个较大数据量的处理模块。在上篇中使用多线程解决了数据导入的问题，而在数据导入后碰到了较大数据量的逻辑处理和更新问题，为了在尽量短的时间内完成数据处理，我们开发组将整个数据处理过程拆分成两个存储过程和一段java代码。
  我负责其中一个存储过程，整个存储过程的逻辑就是通过数据的Code字段规则，分析数据与数据之间是否存在父子关系，并将这种关系通过Parent_Id实体化。
  存储过程完成后速度依然很慢在4分钟左右，在不断的探索和测试后终于将时间缩短到2-3s内。在此过程中学到了很多，在此记录。

存储过程之优化

存储过程核心Code

  --遍历WBS编码所有长度，即所有层级
  for i in code_length loop
  
    m_num := i.clength - s_code;
    for frecord1 in (select *
                       from sgt_wbs
                      where PLAN_ID = planId
                        and length(CODE) = i.clength) loop
    
      -- 如果为顶级编码则只更新层级
      if (i.clength = min_code) then
        update sgt_wbs t
           set t.node_level = n_level
         where length(code) = i.clength;
        exit;
      end if;
    
      --更新WBS节点的parent_id
      update sgt_wbs t
         set t.parent_id  = (select id
                               from sgt_wbs
                              where code = substr(frecord1.code,
                                                  0,
                                                  i.clength - m_num)
                                and plan_id = planId),
             t.node_level = n_level
       where t.CODE = frecord1.code
         and t.plan_id = planId;
    
    end loop;
  
    commit;
  
    s_code  := i.clength;
    n_level := n_level + 1;
  
  end loop;

存储过程测试及运行

1. 存储过程运行

begin
  -- Call the procedure
  sgt_update_pid(planid => :planid);
end;                    

2. 存储过程的测试

  整个过程使用的数据库连接工具为PL/SQL

• 首先在存储过程上右击选中添加调试信息，然后再次选中测试

  
  
  image.png

• 然后添加变量数据，并执行

  
  
  image.png

• 顶部工具栏中常用工具
    常用的就两个按钮：按钮1和按钮2 ，按钮1为直接运行存储过程，按钮2为单步执行存储过程，类似java的debug。按钮1 可以直接测试存储过程是否能够执行成功，而按钮2一般为在存储过程出错的过程中进行单步调试

  
  
  image.png

• 单步调试
    在调试过程中可以添加观察变量，可以观察变量在运行过程中值得变化，便于错误排查和调试

  
  
  image.png

3. 存储过程概览图

  在存储过程中想要查看整个代码用时情况需要使用概览图

• 选中概览图按钮
    如果想要查看存储过程的时间分布是无法同时单步调试的，需要直接执行

  
  
  image.png

• 执行
    在使用概览图的功能时是不能测试的，需要直接运行

  
  
  image.png

• 查看执行计划
    在执行后，可以在顶部选择概览图查看整个存储过程中时间分部，并对耗时部分进行针对的优化

  
  
  image.png

存储过程优化

  在测试通过并使用概览图分析后发现了影响时间的code

image.png

1.分析耗时code

  我们可以发现存储过程中大部分时间都消耗在更新语句中，在单独对此语句做分析，并获取该语句的执行计划(F5)

• 可优化代码

      update sgt_wbs t
         set t.parent_id  = (select id
                               from sgt_wbs
                              where code = substr(frecord1.code,
                                                  0,
                                                  i.clength - m_num)
                                and plan_id = planId),
             t.node_level = n_level
       where t.CODE = frecord1.code
         and t.plan_id = planId;

• 执行计划

  
  
  image.png
  
  

    通过执行计划，我们可以发现更新过程中 子查询是最耗时的操作，所以我们优化的重点放在了这个子查询中。
    但是子查询语句很简单，并没有优化空间，所以这里我们为查询字段创建了索引来加快查询速度

2.创建索引并测试

  当创建索引后，再次测试存储过程，原来需要360多秒的时间，缩短到了2-3秒钟，说明优化是有效的，并解决了数据处理时间过长的问题

多线程的限制

  在上篇文章中，使用多线程优化数据导入后，还想要在加快数据导入速度，最直接的反应是提高线程数。所以在此思想上做了一些列的测试

• 数据量16000条

  通过比对测试发现线程数的增多在无数据校验和变量锁的情况下，多线程会提升导入速度，减少时间，但是一旦有了变量锁和校验，一倍多的线程速度反而慢了下来。
  测试直观的表明，线程的确能提升导入效率,但是是有极限的，一旦超过最优线程数量，数据导入速度反而会出现下降的情况。可见线程是有限制的，一定要将线程数定在一个合理的范围内，否则过多的线程不但会拖慢运行速度还会消耗服务器资源，导致整个应用响应变慢