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

Use an insert cursor. exec sql prepare ins_stat from 'insert into...'; exec sql declare ins_rec cursor for ins_stat; exec sql open ins_rec; exec sql put ins_rec using ...; every hundred records or so (you'll have to do some research on what works best for you), exec sql flush ins_rec. That should inprove performance by at least 25% over the execute you have now. In article <8bfi4h$86t$1@news.xmission.com>, "Inge Dillen" <Inge.Dillen@orange.be> 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. > > Any suggestions are welcome! > > Thanks, > > Inge Dillen > inge.dillen@orange.be > > -- # unrm / ksh: unrm: not found # man cpio Sent via Deja.com http://www.deja.com/ Before you buy.