用集算器更新数据库的技巧
【摘要】
对数据库进行批量更新时，使用 update 函数要比 execute 函数性能更好。数据库通常会提供同步 / 导入工具，这些工具通常具备更好的性能，也都支持命令行调用，SPL 可以调用这些工具。SPL 支持并行计算，包括并行执行同步 / 导入工具，所以可以将单文本拆成多个文本，同时导入多个文件，以此获得更高性能。点击用集算器更新数据库的技巧了解详情。
对数据库进行批量更新时，使用update函数要比execute函数性能更好。
比如，source和target是两个不同的数据源，callrecordA是source中的物理表，有1万条记录，callrecordB是target中的物理表，无记录，现在需要将callrecordA中的数据同步到callrecordB中。
当使用execute函数进行批量更新，可以发现性能不够理想，代码如下：
A |
B |
|
1 |
=connect("source") |
/连接source |
2 |
=A1.query@x("select * from callrecordA") |
/取callrecordA |
3 |
||
4 |
=connect("target") |
/连接target |
5 |
=A4.execute("truncate table callrecordB") |
/为方便重复测试，先清空callrecordB |
6 |
=now() |
/记录测试时间 |
7 |
=A2.(A4.execute("insert into callrecordB values(?,?,?,?,?,?)",~.SERIALNUMBER,~.OUTID,~.INID,~.CALLTIME,~.DURATION,~.CHARGE)) |
/批量更新 |
8 |
=A4.close() |
|
9 |
=interval@ms(A6,now()) |
/SQL execute语句，8251ms |
上述算法实际是循环执行insert语句，并没有充分利用JDBC的批量更新机制，因此性能较差。而且上述算法只是插入数据，如果既有插入又有修改，处理起来会麻烦许多。
使用update函数后，可以看到性能提升明显，代码如下：
11 |
=connect("target") |
/连接target |
12 |
=A11.execute("truncate table callrecordB") |
/为方便重复测试，先清空callrecordB |
13 |
=now() |
/记录测试时间 |
14 |
=A11.update@i(A2,callrecord;SERIALNUMBER) |
/批量更新 |
15 |
=A11.close() |
|
16 |
=interval@ms(A13,now()) |
/SPL update函数，2075ms |
函数update利用JDBC的executeBatch机制进行批量更新，因此性能较好。选项@i表示只需生成insert语句，@u表示只生成update语句。无选项时表示既有insert又有update，即目标表已有数据，需比对源表和目标表的主键，如果某主键在源表存在，在目标表不存在，则生成insert语句，如果某主键在源表和目标表都存在，则生成update语句。
数据库通常会提供同步/导入工具，这些工具通常具备更好的性能，也都支持命令行调用。SPL可执行命令行，因此可以调用这些工具。比如用oracle的sqlldr导入数据，可使用如下代码：
A |
B |
|
1 |
=connect("source") |
/连接source |
2 |
=A1.query@x("select * from callrecordA") |
/取callrecordA |
3 |
=file("d:\\temp\\callrecordB.txt").export(A2;"|") |
/生成文本/csv文件，分隔符为| |
4 |
=system("cmd /C sqlldr system/runqian@orcl control=d:\\temp\\callrecordB.ctl data=d:\\temp\\callrecordB.txt log=log.log bad=bad.log errors=5000 rows=1000 bindsize=10485760") |
/执行命令行 |
A1-A3：将source中的表导出成文本文件，分隔符为|。文本文件如果已存在，则可省略本步骤。
A4：执行命令行，调用sqlldr，命令格式要符合sqlldr的官方要求。注意callrecordB.ctl 是sqlldr要求的控制文件，格式如下：
load data CHARACTERSET UTF8 append into table callrecordB fields terminated by '|' trailing nullcols ( SERIALNUMBER INTEGER EXTERNAL, OUTID INTEGER EXTERNAL, INID INTEGER EXTERNAL, CALLTIME date "yyyy-mm-dd hh24:mi:ss", DURATION INTEGER EXTERNAL, CHARGE INTEGER EXTERNAL ) |
SPL支持并行计算，包括并行执行同步/导入工具，所以可以将单文本拆成多个文本，同时导入多个文件，以此获得更高性能。不同的同步/导入工具，有不同的并行要求，通常的要求是不锁表，且无唯一索引。比如并行执行sqlldr，可用如下代码：
A |
B |
C |
|
1 |
=file("d:\\temp\\callrecordB.txt") |
/打开单文件 |
|
2 |
=2.(file(concat("d:\\temp\\callrecordB",~,".txt")).export(A1.cursor(;~:2,"|");"|")) |
/拆分成多个文件 |
|
3 |
fork to(2) |
=system(concat("cmd /C sqlldr system/runqian@orcl control=d:\\temp\\callrecordB.ctl data=d:\\temp\\callrecordB",A3,".txt direct=y parallel=true log=log.log bad=bad.log errors=5000 bindsize=10485760")) |
/多文件并行导入 |
英文版