Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
General
cancel
Showing results for 
Search instead for 
Did you mean: 

To increase the performance of INSERT

Gangadhar_1
Occasional Advisor

To increase the performance of INSERT

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
7 REPLIES
Peter Godron
Honored Contributor

Re: To increase the performance of INSERT

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
Exalted Contributor

Re: To increase the performance of INSERT

Shalom,

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

SEP
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
Gangadhar_1
Occasional Advisor

Re: To increase the performance of INSERT

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
Honored Contributor

Re: To increase the performance of INSERT

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
Occasional Advisor

Re: To increase the performance of INSERT

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
Honored Contributor

Re: To increase the performance of INSERT

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
Honored Contributor

Re: To increase the performance of INSERT

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)