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.

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