How to insert faster

Hello everyone. I have a view consisting 2 tables correctly joined by MCU column. When I inserting from this view into third table it's work, but too slow. Speed ≈ 14k records per minute. I need speed ≈ 75k records per minute or faster. I really need it and there is no way to optimize program because inserting is in batch app. and can started in any time with 1.5kk records or more, so users can't wait more then 20 minutes. So i have a question...
How to insert faster?
Thanks for any answers)

That's kind of a loaded question without knowing the entire implementation. Its impossible for us to tell from your post where the bottleneck is, could be a bunch of different things. May not be the insert that is slow, but reading the source data, could be any processing that occurs between reading data and writing.

You really probably need to profile the process and identify which components are performing slowly. The Oracle provided debug log parser "Performance Workbench" is actually pretty good at this. I have used it quite a bit to identify poorly performing components in a larger process.

If the poor performing components are implemented in ER code, it could be that re-writing those components (or the whole thing for that matter) in C might speed things up. Depending on the business logic, it could be that caching certain pieces of data might help.

If it truly is the actual insert that is slow, and I mean the actual SQL insert statement NOT the ER code doing the insert command, you may simply be database constrained and then there may not be a whole lot you can do aside from dropping unneeded indexes on the target table (which will probably only net you very small performance gains and not the giant leaps in performance you are seeking)or maybe trying to figure out some sort of bulk load/insert or something like that. Maybe review any table triggers on the target table and see if those might be causing performance problems.

If it is the read that is slow then possibly re-evaluating your joins/where clause etc. might help or look at creating some secondary indexes on the source table(s) for performance.

[ QUOTE ]
If it truly is the actual insert that is slow, and I mean the actual SQL insert statement NOT the ER code doing the insert command,

[/ QUOTE ]
Thank you for such a detailed response, BOster, but it's really NER insert is so slow. Just insert without any oter code. Just inser from view. Similar SQL insert in pl/sql developer done for 5 minutes. I really think that JDE commits after inserting any record. So i thought that it's possible to do insert faster with some standart business functions...[ QUOTE ]

re-writing those components (or the whole thing for that matter) in C might speed things up.

[/ QUOTE ]I thought about that, but how to do this i don't know. Also thought how transfer some parameters and execute oracle procedure from C business function. But i do not know JDE business function API...
Do somebody solved the problem like this?

You can use buffered inserts to improve the performance of inserting hundreds or thousands of records into a single database table as long as you don't need immediate feedback if an insertion failure occurs. You cannot use buffered inserts with interactive applications but you can use them with:

Table conversions
Table I/O
Batch processes
Business functions
Buffered inserts are not available with an Access database, post-insert triggers, or multiple-table views. They are available only with these databases:

Oracle (V8 and later)
DB2/400
SQL Server
The JD Edwards EnterpriseOne database middleware delivers records to the database management system one buffer load at a time.

When you request buffering, the database records are inserted individually and the buffer is automatically flushed when it fills; that is, the JD Edwards EnterpriseOne database middleware delivers the buffer to the database management system. The buffer can also be explicitly flushed. For example, the buffer flushes automatically when you commit a transaction or when you close a table or business view. The business function, table conversion engine, or Table I/O feature can explicitly request that the buffer be flushed as well.


* Using Buffered Inserts in Table I/O

Open a batch application in RDA and access Event Rules Design.

Click the Table I/O button.

On the Insert TableIO Operation form, select Open under Advanced Operations and click Next.

On the Data Source form, select the table that you want to use and click Advanced Options.

On the Advanced Options form, select Buffer Inserts and click OK.

* Using Special Operations for Buffered Inserts

Open a batch application in RDA and access Event Rules Design.

At the point in which you want to perform either a Flush Insert Buffer or Get Error Data operation, click the Table I/O button.

On the Insert TableIO Operation form, select one of these options under Special Operations:

Flush Insert Buffer
Get Error Data

Hi all!
i've been reading about those buffered inserts, since we have do more than 4.000.000 inserts in a batch application.
I couldn't find where the inserts are finally performed...

----- ER code should look some like this: -------
open table -> select "Buffered inserts"
in ER code, create the inserts.
Flush buffer
Close Table
----

Do you guys can tell me where the inserts are performed? o how does it work?

Thanks!
Gracias!

Rodri

The inserts are performed where you put them ...

Buffered inserts are done via a batch (100 or 1000 inserts etc.) so it's imposible to check the success of each insert via code. If you are in complete control of the unique-ness it works very well.

Craig