Re: ESQL/C -Insert- Performance

---- you wrote: > 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. Take a look at insert cursors Dude. Also, make sure you don't prepare the insert inside that loop. Did you put the indexes in another dbspace and not allow them to be fragmented? You could also run a different program for each fragment so that you can insert in parallel. At least that is what I would do in 4GL, but perhaps you can use multiple threads in ESQL? AB ---------------------------------------------------------------- Get your free email from AltaVista at http://altavista.iname.com