RE: ESQL/C -Insert- Performance

Hi Run your loads in parallel, check PDQ, or One way to get your program to load faster is to let it fork say +-10 children that will do the inserts. Your Parent program will break up {calculate} the load file into 10 begin/end bits. Now fork 10 children passing the start stop values as parameters to the children. Your program will run +- 10 times faster. I used to load big tables like this with Online 5 {Before HPL}, but be aware that you will use just about 100% of all the resources on a 4 CPU server. You can play with the number of child's to fork. This is the best way to load data fast on a multi CPU server with Online 7. I am not to sure if you will get the same improvement with IDS 7x+ I Have the source code {SCO UNIXWARE SYS V} available at my previous Client. If you need it, email me. Hope they still have it, but not to difficult to reproduce once U know how write code to fork a child in UNIX. There is some extra compiler switches to use when U do multi threading in ESQLC. Hope It helps. Hannes Visagie Systems Engineer MIBCO -----Original Message----- From: Inge Dillen [SMTP:Inge.Dillen@orange.be] Sent: Friday, March 24, 2000 12:25 PM 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