CODEWITHSUNDEEP
c#oracle-databasebulkinsertodac
user1274347
user1274347

Reputation:

Bulk Insert performance in Oracle

I tried lot of solutions but I still don't understand something: i got a simple insert (with 40 columns of different kind (blob included) and i try to insert this kind of data passing (I use Odac from c#) arrays of values for each parameter.

The result is a single invoke of ExecuteNonQuery for 4000rows more or less, the problem is that this insert with no so much of element last 3hours.

Any suggestion? the data source is from the software so can't be linked on db, its memory data and all I do is put them in arrays and pass them as input parameters of my oraclecommand that I then run.

Will in this case use a storedprocedure make any difference? why?

The insert actually is in a transaction but there's no index at all in the table i try to insert into, the db its a regular installation of Oracle XE with 100 tables, transaction includes just this insert and few other actions not so big. Total data i try to insert are 100 Mb max... I dont know what should I check again :(

Again: the transaction seems not affecting performance and the speed isnt linear, ie 1000elements =10minutes, 4000element=3hours..all with cpu 100% and a huge use of memory

Upvotes: 0

Views: 1260

Answers (1)

zmbq
zmbq

Reputation: 39013

Inserting 4,000 rows takes 3 hours? Is that the problem? Unless each row is a several GBs long, there's no real excuse for such performance - you need to look at the database (or have your DBA do it), and not your C# code.

Upvotes: 1

Related Questions