采用批量处理技术提高数据写入性能-【张永康】

摘要

                 在医院信息系统这类高并发的写入密集型应用中，提高数据写入的性能可以改善系统的整体性能，同时提高系统的并发能力。批量数据处理技术通过减少数据调用和提交的次数，极大的提高了写入性能。本文根据作者在医院信息系统开发中所实践的方法，通过实验对比，展示了批量处理技术带来的性能飞跃。

关键字：批量写入、性能、优化

一、  概述

                最近，在开发临床路径系统中，多次采用了批量数据处理技术。例如：在保存一批相同性质的数据时（例如：一个表格的多行数据），传统的方式就是每一行数据调用一次存储过程，通过循环调用来处理全部数据。而批量数据处理方式，则通过收集所有数据行，通过分隔符组合成一个字符串，只调用一次存储过程，通过一个参数传入所有数据，然后在存储过程中分解字符串，还原为多行数据，并且采用Oracle的批量数据提交语法(Forall)来进行数据的写入。

                经实际使用和试验表明，这种方式能够大大提高数据处理性能，具有较广的应用场合，值得我们在产品开发中推广使用。

二、  对比实验

                下面通过一个对比实验来展现批量处理带来的性能飞跃。

                假设：

                临床路径评估保存时，有一批评估指标相关的数据需要保存到数据库的相关表中，包括：指标名称，指标类型，指标结果。为了便于对比分析，我们假设有200条记录需要写入。

                采用传统方式，存储过程如下：

Create Or Replace Procedure Zl_病人路径评估_Insert_V1

(

  路径记录id_In 病人临床路径.Id%Type,

  阶段id_In     临床路径阶段.Id%Type,

  日期_In       病人路径评估.日期%Type,

  天数_In       病人路径评估.天数%Type,

  指标名称_In   病人路径指标.评估指标%Type,

  指标结果_In   病人路径指标.指标结果%Type,

  指标类型_In   病人路径指标.指标类型%Type

) Is

Begin

  --省略部分内容 

  Insert Into 病人路径指标

    (路径记录id, 阶段id, 日期, 天数, 评估类型, 评估指标, 指标结果, 指标类型)

  Values

    (路径记录id_In, 阶段id_In, 日期_In, 天数_In, 2, 指标名称_In, 指标结果_In, 指标类型_In);

  --省略部分内容

End Zl_病人路径评估_Insert_V1;

/

                有200行记录，界面程序中就需要调用200次存储过程，分别传入每行数据的三个参数。

 

                如果采用批量数据处理方式，存储过程如下：

Create Or Replace Procedure Zl_病人路径评估_Insert_V2

(

  路径记录id_In 病人临床路径.Id%Type,

  阶段id_In     临床路径阶段.Id%Type,

  日期_In       病人路径评估.日期%Type,

  天数_In       病人路径评估.天数%Type,

  指标评估_In   Varchar2 --指标名称|指标结果|指标类型||...,末尾带||,允许为空

) Is

  v_Str   Varchar2(4000);

  v_Index Number;

  l_指标 t_Strlist := t_Strlist();

Begin

  --省略部分内容

  v_Str := 指标评估_In;

  Loop

    v_Index := Instr(v_Str, '||');

    Exit When(Nvl(v_Index, 0) = 0);

    l_指标.Extend;

    l_指标(l_指标.Count) := Substr(v_Str, 1, v_Index - 1); 

    v_Str := Substr(v_Str, v_Index + 2);

  End Loop;

 

  Forall I In 1 .. l_指标.Count 

    Insert Into 病人路径指标

      (路径记录id, 阶段id, 日期, 天数, 评估类型, 评估指标, 指标结果, 指标类型)

    Values

      (路径记录id_In, 阶段id_In, 日期_In, 天数_In, 2, Substr(l_指标(I), 1, Instr(l_指标(I), '|') - 1),

       Substr(l_指标(I), Instr(l_指标(I), '|') + 1, Instr(l_指标(I), '|', -1) - (Instr(l_指标(I), '|') + 1)),

       Substr(l_指标(I), Instr(l_指标(I), '|', -1) + 1));

  --省略部分内容

End Zl_病人路径评估_Insert_V2;

/

                说明：其中的t_Strlist 是一个预定义类型（单列记录集）

                                type t_StrList as Table of Varchar2(4000)

 

                这种方式，通过收集每行数据并以分隔符组合到一个变量中，然后把这200行数据传入，仅调用一次存储过程。在存储过程中，再通过分隔符把这个变量的内容分解成200行数据，然后采用Forall语法进行批量插入。

                如果实际开发过程中，多行数据组合后的字符串可能超过存储过程的字符参数的最大长度4000字节，则可以进行判断后，按字符串的最大长度进行组合，分多次调用存储过程。

 

                这个过程中，传入的字符串分解成行后，取每一行的字段的值时，采用了Instr和Substr，看起来比较复杂，而且在Forall语句中使用PLSQL函数，在8i版本时需要在SQL解析引擎和PLSQL解析引擎之间切换(9i以后已统一为一个解析引擎)，所以，还有一种改良的方式，详见附后。

                这种改进后的方式，先解析字符串的每一行，并将每一列的值放到不同的多行记录集中，然后Forall语句中直接取值，避免了函数的使用。

 

这三种方式分别插入相同的200行数据，试验结果如下：

单位（毫秒）	第1次	第2次	第3次
第1种	515	421	468
第2种	32	47	109
第3种	15	16	31

                由此可见，批量处理方式与传统方式的写入速度是一个数量级的差别，要快30倍左右。并且，因为测试是在单机运行，如果是在网络环境下执行，加上网络往返的时间消耗，传统方式还会更慢。

 

 

 

 

 

通过以下的SQL查询写入前后的日志量，得到产生的日志大小。

Select a.Name, b.Value

From V$statname A, V$mystat B

Where a.Statistic# = b.Statistic# And Lower(a.Name) Like '%redo size%'

产生的日志数据对比：

                第1种方式，产生155KB的日志；

                第2、3种方式，仅产生32KB的日志；

                数据表明，又是一个数量级的差别。由于日志量的减少，可大大提高并发环境下的事务性能。

 

三、  分析与总结

                为什么批量数据处理能够带来这么大的性能提升？根据实际结果的数据，再从理论方面进行分析，主要有三方面的原因。

1.       因为多次调用改为一次调用，减少了网络往返的时间消耗。

2.       由于多次调用需要多次进行SQL解析，虽然是软解析，但解析SQL是消耗CPU成本很高的操作。这一点是最主要的原因。

3.       采用forall方式批量处理数据时，Oracle采用了批量绑定和解析，并且所有数据作为一个事务原子，只产生一次回滚数据，所以，产生的日志量也很小。              

               

                批量数据处理还可用于数据转移，大表更新（更新字段值或类型），大表删除（按条件删除）等多种应用场合，提供高效的处理。

                所以，批量数据处理相对于传统单次处理方式，能够数十百甚至上百倍地提高数据处理性能，具有较广的应用场合，值得我们在产品开发中推广使用。

 

参考文献

Thomas Kyte . Oracle 9i 10g编程艺术（第9章第5小节）：人民邮电出版社，2006.

               

 

 

附：

第3种方式，改进后的批量处理过程

Create Or Replace Procedure Zl_病人路径评估_Insert_v3

(

  路径记录id_In 病人临床路径.Id%Type,

  阶段id_In     临床路径阶段.Id%Type,

  日期_In       病人路径评估.日期%Type,

  天数_In       病人路径评估.天数%Type,

  指标评估_In   Varchar2 --指标名称|指标结果|指标类型||...,末尾带||,允许为空

) Is

  v_Str   Varchar2(4000);

  v_Tmp   Varchar2(1000);

  v_Index Number;

  I       Number(5) := 1;

 

  l_指标名称 t_Strlist := t_Strlist();

  l_指标结果 t_Strlist := t_Strlist();

  l_指标类型 t_Numlist := t_Numlist();

Begin

  --省略部分内容

  v_Str := 指标评估_In;

  Loop

    v_Index := Instr(v_Str, '||');

    Exit When(Nvl(v_Index, 0) = 0);

    l_指标名称.Extend;

    l_指标结果.Extend;

    l_指标类型.Extend;

 

    v_Tmp := Substr(v_Str, 1, v_Index - 1);

    l_指标名称(I) := Substr(v_Tmp, 1, Instr(v_Tmp, '|') - 1);

    v_Tmp := Substr(v_Tmp, Instr(v_Tmp, '|') + 1);

    l_指标结果(I) := Trim(Substr(v_Tmp, 1, Instr(v_Tmp, '|') - 1));

    v_Tmp := Substr(v_Tmp, Instr(v_Tmp, '|') + 1);

    l_指标类型(I) := To_Number(v_Tmp);

 

    v_Str := Substr(v_Str, v_Index + 2);

    I     := I + 1;

  End Loop;

 

  Forall I In 1 .. l_指标名称.Count 

    Insert Into 病人路径指标

      (路径记录id, 阶段id, 日期, 天数, 评估类型, 评估指标, 指标结果, 指标类型)

    Values

      (路径记录id_In, 阶段id_In, 日期_In, 天数_In, 2, l_指标名称(I), l_指标结果(I), l_指标类型(I));

  --省略部分内容

End Zl_病人路径评估_Insert_v3;

/

 

                另外，随着数据行数的增大，例如：200条增加到2000条，性能差别将更加明显，主要是由于每次调用的SQL解析所消耗的CPU会大大增加，而批量处理时，增加很少。以前优化过的结帐处理程序后，由十几分钟或几十分钟缩短到几十秒或几分钟。