To increase the performance of INSERT

[Gangadhar_1]

Hi all,
I got to insert 140000 records into an already exsting table, and it has to be done every day.
One constraint is, i can use either shell script or a 4GL code here.

Plz suggest me some tips to increase the speed of this operation.

Thanks in advance,
Gangadhar

[Peter Godron]

Hi,
can you please provide a bit more info:
1. Insert into oracle database ?
2. Is the table indexed ? If yes, how ?
3. What format is the input in (cvs,...)?
4. Is the data assumed to be 'clean' (without duplicate keys etc.)?
5. Are you adding 140K records and keeping them? i.e. After a year you have 51100K records ?

My original suggestion would be to run sqlload into a temporary table, then clean the data and then transfer into main table via insert into , with constraints etc switched off.

[Steven E. Protter]

Shalom,

In general, raid 1 or raid 10 storage increases the performance on writes.

SEP

[Gangadhar_1]

Hi Peter,

1. The database is Informix/4GL.
2. The table is having one unique index and 6 dupls indexes.
3. Input is nothing but the rows from other tables. So I have to select records from different tables first.
4. The data is clear, without any duplicate values.
5. And ya, i am going to add 140000 records daily, but only for one or two months.

Plz let me know the different ways to do the same efficiently.

Thanks,
Gangadhar

[Peter Godron]

Hi,
we mostly work with Oracle here, Informix person is suggesting looking at Parallel Database Query (PDQ), which apparently speeds up due to parallel inserts.

All I could find for ref:
http://docs.rinet.ru/InforSmes/ch19/ch19.htm
Sorry.

[Gangadhar_1]

Its ok Peter, no need to say sorry. I'll try to find a way out.

And thanks a lot for that link, i think its gonna help me a lot.

If anybody has better idea, then plz let me know ASAP.

Thanks,
Gangadhar

[Steve Lewis]

Alternatives:
1. High Performance Loader (HPL), can use parallel streams.
2. Parallel usage of dbload commands.
3. Parallel programs or dbaccess commands inserting the data.

If you have exclusive access to the table, then consider the time you might save by dropping the indexes, changing the table to non-logged mode, then changing it back again and re-creating the indexes.

If you only have 1 or 2 cpus, then consider the CopperEye datablade.

[Yogeeraj_1]

Hi gangadhar,

parallel insert would be the way out.

The other method would be to insert the rows in a temporary table and do a bulk/merge operation.


please specify the database you are using

kind regards
yogeeraj