Informix load performance

[nverma34]

We are currently having a severe performance problem with informix c++ api. Your help is really appreciated. We are trying to load approx. 3 million rows in a c++ stl map data structure. This operation takes close to 50 minutes. We have determined that the bulk of the time is spent in looping. The following is the relevant code fragment:

ITRow *row;
int colIndex = 0;

while( row = cursor.NextRow() )
{

/* if we comment out everything which happens within the loop, still it takes a long time */

}

The client machine is not co-located with the informix database server. We have tried various parameters such as FET_BUF_SIZE and PDQ_PRIORITY and these were of limited help.

Could you please point us to a fast data loading API? Thanks and greatly appreciate your help.

Navneet

 

[vpshriyan]

Hi,

I may not be much helpful in debugging your c++ api source code. I feel there may be some issues with the logic or type and kind of manipulation done in the code. However, you may think about the following alternates.

If you have the data file ready according the structure of the table where you intend to insert, you may use one of the backend informix utility "dbload" to do this job with more efficiency.

Syntax and major options:
dbload -d dbname -c cfilname -l logfile -e errnum -n nnum [-r | -k]
-d database name
-c command file name
-l bad row(s) log file
-e bad row(s) # before abort
-n # of row(s) before commit
-r loading without locking table
-k loading with exclusive lock on table(s)

Command file layout:
FILE 'datafile' DELIMITER '|' num_of_columns;
INSERT INTO table_name ;

dbload example:
dbload -d testdb -c cust.cmd -l cust.err -e 50 -n 100 -k

command file example:
FILE 'cust.dat' DELIMITER '|' 8;
INSERT INTO customer ;

IDS also supports a utility called High Performance Loader (HPL) which need to be configured before making it active. You can have more info from the following link:

HPL onpladm Utility:

http://www-3.ibm.com/software/data/informix/pubs/library/92ids/onpladm/index.html

High-Performance Loader User's Guide:

http://www-3.ibm.com/software/data/informix/pubs/library/interim/ct1t3na-pdf.html

Regards,
Shriyan
"The hidden flaw never remains hidden."