RE: ESQL/C -Insert- Performance

Just to make sure, how many records were in the table when you didnt have indexes? Growth of the file_name table could cause this kind of performance. If that is the case, there are paths you might follow to speed that up. Will >===== Original Message From "Inge Dillen" <Inge.Dillen@orange.be> ===== >Hi all, > >Does anyone know some tricks to improve the performance of an insert into a big >table. > >Here is some info: >We run Informix Dynamic Server Version 7.31.UC3 >The table has currently 8214357 rows in it. >Row size=265 >The table is fragmented. >Indexes: >Index name Owner Type Cluster Columns > >pstidx_1 informix dupls No a_sub_num > >pstidx_2 informix dupls No a_sub_imsi > >pstidx_3 informix dupls No a_sub_imei > >pstidx_4 informix dupls No b_sub_num > b_sub_imsi > >b_sub_imei > >pstidx_5 informix dupls No ch_start_time > > >A C-program runs every two hours. At each run about 75000 records are inserted >into the table. This amount will increase significantly in future. >The C-program gets a file_name passed through by a Unix shell script. It >connects to the database and checks the file name in a table with only 2 fields >(file_name and status). If it finds the file_name the data from that file is not >been loaded again. If it is a new file the file_name is inserted in the >file_name table. Each run loads data from 32 files. >The data from the flatfile is read into a C-structure. The MAXSIZE of the >structure is set to 10000. >The ESQL/C variables are filled from the structure. >A statement is prepared: > sprintf(insert_query," INSERT INTO %s VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? >,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",table_name); > > EXEC SQL PREPARE insert_cdr FROM :insert_query; > EXEC SQL EXECUTE insert_cdr USING > :record_type, > :a_sub_num, > :a_sub_imsi, > ................ >When the amount of records > MAXSIZE the program stays in the loop until it >encounters EOF . > >Before the indexes were created the program took about 10 minutes to run. With >the indexes it runs to slow to be acceptable. > >Any suggestions are welcome! > >Thanks, > > >Inge Dillen >inge.dillen@orange.be ------------------------------------------------------------ This e-mail has been sent to you courtesy of OperaMail, as a free service from Opera Software, makers of the award-winning Web Browser, Opera. Visit us at http://www.opera.com/ or our portal at: http://www.myopera.com/ Your free e-mail account is waiting at: http://www.operamail.com/ ------------------------------------------------------------