Import data, batch size

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
kamen
Posts: 6
Joined: Wed 25 Nov 2015 12:27

Import data, batch size

Post by kamen » Wed 02 Dec 2015 13:23

Hello.

We use DML arrays for import of data to Oracle DB. We limit array size to 2000 when calling ExecuteArray. No matter how many columns target table has nor what size of them it is. Just to avoid Out of Memory exception. We want to improve performance of our code. So constant 2000 should be replaced by some precalculated value. We can calculate/estimate table's row length. Then multiplied by row count we get "batch size". Can you advice some hint/recommendation? Which value or how to calculate ...

Another thing is Oracle "packet-size" which is then used for communication client to server. If such value exists we could use it to justify batch size to by multiple. Just to optimize communication. Is there such an value? Can be retrieved from OracleConnection?

Regards,
Petr.

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: Import data, batch size

Post by Pinturiccio » Fri 04 Dec 2015 15:06

When you use DML arrays, the only thing you can change is array size. You need to test different values, check the RAM amounts application uses and the amount of free RAM, and choose the best array size value for your needs.

We have also written in the post http://forums.devart.com/viewtopic.php?t=32825 that you can disable triggers for your table and use OracleLoader. OracleLoader with direct path is used in the OCI mode, which is faster than DML arrays. You can also set the BufferSize property for OracleLoader. Try using several different values for this property in order to choose the optimal one.

Post Reply