RE: ESQL/C -Insert- Performance

Try dropping some or all of your indexes and then recreate them after the inserts have finished. You may find that this is quicker. You may also have to run an update statistics on your table after you've re-created your indexes. Cheers Rekaish -----Original Message----- From: Inge Dillen [mailto:Inge.Dillen@orange.be] Sent: 24 March 2000 10:25 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