RE: ESQL/C -Insert- Performance

---- you wrote: > 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. I am not too clear on this Dude. Are you saying that every 2 hours, they should drop all indexes, load 75000 records, then create 5 indexes on over 8 million records, and all in under 2 hours? > -----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 > AB ---------------------------------------------------------------- Get your free email from AltaVista at http://altavista.iname.com