What is the difference between giving BatchSize and not giving BatchSize for OracleBulkCopy

ado.net c# oracle sql sqlbulkcopy

Question

Can somebody explain the differences in performance and behavior between using BatchSize and not usingBatchSize 11-ZZZ in zzzz. Right now, I'm not specific.BatchSize . MyBatchSize from 100 to 200000 is possible. Please advise the best course of action.

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;
}
1
1
12/9/2012 3:44:35 PM

Popular Answer

The ideal batch size should be between 500 and 5000.

The advantage of bulk copy is:

  • improve database insertion performance (shorten the time needed)
  • heavy insertion (many rows of insertion) manipulation on the fly

However, there are several drawbacks that we should be aware of:

  • When a row in the bulk copy encounters an issue, troubleshooting is more difficult.
  • Although the bulk copy operation was completed successfully, it is possible that some data was not put properly (dirty data), due to a problem with data type collation or conversion (unicode and non-unicode).

You may read more about it on the following website: http://docs.oracle.com/html/E10927_01/OracleBulkCopyClass.htm#CHDCDEIA\sor\sUsing.NET, bulk insert into Oracle

1
5/23/2017 11:59:25 AM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow