When the database is updated in batches, the performance of update function is better than execute function.

For example, source and target are two different data sources. Callrecorda is the physical table in source, with 10000 records. Callrecordb is the physical table in target, without records. Now, you need to synchronize the data in callrecorda to callrecordb.

When the execute function is used for batch update, it can be found that the performance is not ideal. The code is as follows:

In fact, the above algorithm executes insert statements in loop, and does not make full use of JDBC's batch update mechanism, so its performance is poor. Moreover, the above algorithm only inserts data. If there are both inserts and modifications, it will be a lot of trouble to process.

After using the update function, you can see that the performance has improved significantly. The code is as follows:

The function update uses the executebatch mechanism of JDBC for batch update, so the performance is better. The @i option means that only insert statements need to be generated, @u means that only update statements need to be generated. If there is no option, it means that there is both insert and update, that is, the target table has data, and the primary keys of the source table and the target table need to be compared. If a primary key exists in the source table and does not exist in the target table, an insert statement will be generated. If a primary key exists in both the source table and the target table, an update statement will be generated.

 The database usually provides synchronization / import tools, which usually have better performance and also support command-line calls. SPL executes the command line, so you can call these tools. For example, to import data with Oracle's sqlldr, you can use the following code:

A1-A3：Export the table in source to a text file with a separator of |. If the text file already exists, this step can be omitted.

A4：Execute the command line and call sqlldr. The command format should meet the official requirements of sqlldr. Note that callrecordb.ctl is the control file required by sqlldr. The format is as follows:

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 supports parallel computing, including parallel execution of synchronization/import tools, so you can split single text into multiple texts and import multiple files at the same time to achieve better performance. Different synchronization/import tools have different parallel requirements. The common requirement is not to lock the table, and there is no unique index. For example, to execute sqlldr in parallel, you can use the following code: