RE: ESQL/C -Insert- Performance

Have you tried fragmenting the table and using parallel processing ? Round robin is the best policy for this sort of thing. > N. Daniel Bonesana > SIEMENS-ITRON BUSINESS SERVICES S.A. > Balcarce 683, (C1064AAM) Buenos Aires, Argentina. > * 54 (11) 4320-5456 * dbones@siemens-itron.com.ar > > -----Original Message----- From: Inge Dillen [mailto:Inge.Dillen@orange.be] Sent: Friday, March 24, 2000 7:25 AM To: informix-list@iiug.org Subject: ESQL/C -Insert- Performance 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