Fast inserts with ADO and Oracle

I am working with ADO 2.5 and Oracle 8. My project requires many thousands of changes to be made to the database at a user's request and at reasonable times. And UpdateBatch doesn't seem to do the trick.

That's the preface. Now the details.

[Quoted] The program creates very large hierarchical recordsets (using shape provider), with new data in them. Which means a lot of insert statements. I tried all kinds of things - UpdateBatch and regular Update, different cursor types, local processing (on the server that runs the database) and a remote client.
I compared the results I recieved to building a dynamic anonymous PL/SQL block with all the inserts in it:

begin

    insert into t_test values (1);
    insert into t_test values (2);
.
.

end;

That was about 10 times faster than UpdateBatch for the same amout of rows. The PLSQL has a downside, it is limited in size, and Oracle documentation pretty much states that there is no sure way to determine how big can that block be.

Anyway, I need to flush a very large hierarchical recordset, and I need to do it as fast as possible.
It can probably be done in OCI, but I have a feeling I'm missing something.

Any help is greatly appriciated. Received on Mon Nov 06 2000 - 19:42:49 CET