Re: ESQL/C -Insert- Performance

From: "Inge Dillen" <Inge.Dillen@orange.be> > >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 I think these are too many indexes for the loading requirements, but how about using onpload? Have your C program generate an unload file and use onpload to load the generated file? >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. Drop the indexes and create them just before they're needed again? ______________________________________________________ Get Your Private, Free Email at http://www.hotmail.com