CODEWITHSUNDEEP
.netoracle-databaseodp.netsql-loader
Adrian Brown
Adrian Brown

Reputation: 270

OracleBulkCopy vs SQL*Loader Performance

I am seeing some significant performance differences between OracleBulkCopy (ODP.NET) and SQL*Loader when the Oracle server is on another machine.

I have a very basic table in Oracle with three columns (one BINARY_FLOAT, two NUMBER(18,0)). There are no primary keys, indexes, triggers, etc. It is used as a staging table to get bulk data into the DB.

SQL*Loader takes about 27 seconds to load 4.5 million rows into the table.

OracleBulkCopy takes about 10 minutes to load just 1 million rows.

OracleBulkCopy, according to the documentation, *"...uses a direct path load approach, which is similar to, but not the same as Oracle SQL*Loader."* It might not be up there with SQL*Loader in terms of performance, but this difference is ridiculous.

After some basic analysis of the network traffic, I found the OracleBulkCopy was sending and receiving a huge number of small packets. I used Wireshark to compare the packets for each and found some interesting results.

SQL*Loader - after the initial connection handshaking - sends a series of 8 kilobyte packets (TNS protocol) and receives 60 byte ACKs in response.

OracleBulkCopy sends a series of 102 byte packets (TNS protocol) and receives a 133 byte packet (TNS protocol) in response. What the...!? It is like it is sending one row at a time!

With the OracleBulkCopy class, I am using a batch size of 100,000 and am using a custom IDataReader to read from a data file.

So, my questions are:

Any help is much appreciated.

Upvotes: 9

Views: 3218

Answers (1)

Adrian Brown
Adrian Brown

Reputation: 270

I received a response from Alex Keh, Oracle Product Manager, that Oracle "has noticed this issue as well. We're evaluating how to fix this bug."

So in answer to my own questions, I guess OracleBulkCopy does not perform well as a bulk loading tool. At least not at the moment.

I will be wrapping SQL*Loader as an alternate solution as there is no ETA on the bug fix.

Upvotes: 5

Related Questions