Improving Performance When Inserting Records

The Mertech Drivers allow to improve performance when inserting records by using the SQL_SAVE_BUFFER macro command. You can specify a block of records, that will be submitted in batch to the server, instead of having each record be saved at a time.

You can set the block size based on the DF_FILE_RECORDS_USED attribute from the source table or knowing the number of records that will be inserted and, for example, multiplying the number by 0.10 to get save batches of 10% of the table size.

This example initializes the buffer with 100 extended buffers and the command will save 1,000 records in the table SALESP from VDF Order Example. The 1,000 records will be recorded in blocks of 100 records each (10% of the total).

SQL_SAVE_BUFFER_END is called to indicate that the saving of the extended buffer has finished.

SQL_COMMIT_STMT is called to commit the transaction. Instead of using SQL_COMMIT, saving may also be done within a regular BEGIN_TRANSACTION / END_TRANSACTION block.

Example:

 Use Mertech.inc
 Integer iCount
 Login "ServerName" "UserName" "Password" "ORA_DRV"

Open "SALESP.int" as SALESP

SQL_START_TRANSACTION_STMT
SQL_SAVE_BUFFER_BEGIN OF SALESP TO 100

For iCount From 1 to 1000
Move iCount to SALESP.ID
Move (“Name” + Trim(iCount)) to SALESP.NAME

SQL_SAVE_BUFFER OF SALESP
Showln 'Saving Record: ' iCount
Loop

SQL_SAVE_BUFFER_END of SALESP
SQL_COMMIT_STMT

Showln 'Finished ...'
Inkey PageEnd

The SQL_SAVE_BUFFER macro command are an easy way to give a insert-heavy batch processes a boost!