DevOps, CI/CD and Automation

First off, sorry if this question is in the wrong place - but here goes.

I am using Oracle XE and for the first time I am working on an application that has what are for me very large datasets. A single update might insert 10k to 30k records. Consequently, I reasoned that using some sort of batch update would be more efficient than sending all those SQL insert statements to the database through ADO. But I was wrong...

As a test I created a table with two fields in it ( NUMERIC(10,6) ) and inserted 10000 records into it using the UpdateBatch recordset method. It took about 50 seconds. By contrast, I inserted 10000 records into the same table using discrete SQL statements fed to the database through the connection execute method. That took about 10 seconds.

So I did a bit of digging and found some references that the problem might be caused by the lack of a primary key in my test table, so I dropped and recreated the table adding a primary key field ( NUMERIC(28) ) and reran the tests. The UpdateBatch now takes about 35 seconds and the discrete inserts took about 11 second - a definite improvement, but still not what I was expecting. BTW, I did some troubleshooting and determined that 99% of the long update time is in the UpdateBatch Method execution, adding the records to the recordset takes a few seconds.

The big question: Is this to be expected or am I doing something wrong? If the problem is mine where should I start looking?

Mike...

PS: I have used Oracle for some time, but this is getting into a bunch of stuff that I haven't messed with before. Also, I am quite active on a forum for another product and I understand the frustration where someone gets on and simply says: "My code is broken, how do I fix it?", so please also tell me what information you might need to answer my questions.