Can anybody give the performance & behavior difference between using BatchSize
and without using BatchSize
in OracleBulkCopy
. Currently I don't specify BatchSize
. My BatchSize
may vary from 100 to 200000. Please suggest the best approach.
try
{
var columnMapping =
from table in orgTable.Columns.Cast<DataColumn>()
select
new OracleBulkCopyColumnMapping(table.ColumnName, table.ColumnName);
using (var bulkcopy
= new OracleBulkCopy(ConnectionString, OracleBulkCopyOptions.Default))
{
bulkcopy.DestinationTableName = GoldOrgTable;
foreach (var mapping in columnMapping)
bulkcopy.ColumnMappings.Add(mapping);
bulkcopy.BulkCopyTimeout = TimeOut.Value;
bulkcopy.WriteToServer(orgTable);
orgTable.Dispose();
}
return true;
}
Optimum batchsize should be set around 500~5000.
The pro of using bulk copy is :
However it also have some cons that we need to concern:
Further discussion you can find it on the following website: http://docs.oracle.com/html/E10927_01/OracleBulkCopyClass.htm#CHDCDEIA or Bulk Insert to Oracle using .NET