ODP.NET bulk copy parallel loads feature request

[rferraton]

When i use an APPEND_VALUES hint in my Insert command and execute it using ExecuteNonQuery i receive an error message :

Message : ORA-38910: BATCH ERROR mode is not supported for this operation
StackTrace :    at OracleInternal.ServiceObjects.OracleFailoverMgrImpl.OnError(OracleConnection connection, CallHistoryRecord chr, Object mi, Exception ex, Boolean bTopLevelCall, Boolean& bCanRecordNewCall)
   at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteNonQuery()

is it possible to set off the the error verifications somewhere and if not, would it be possible to push the demand to dev team ?

[alexkeh]

You can not turn off this error. However, you can set up a try-catch block to hide it from the end user. The catch block gets triggered and just continues app operations afterwards without the user seeing the error.

[rferraton]

You can not turn off this error. However, you can set up a try-catch block to hide it from the end user. The catch block gets triggered and just continues app operations afterwards without the user seeing the error.

Well ... i do want insert data into the target, your method just avoid to throw the error but the insert with /*+ APPEND_VALUES */ hint does not work. Your method just hide the failed inserts silently

My final objective is to make direct path insert (in parallel) to a single table.

• OracleBulkCopy lock the target, so write parallelism is not currently possible with oracle with this (it is possible with postgresql, sql server and other databases....) I still don't understand why OracleBulkCopy cannot be parallel while SQL*LOADER can.
• Bind Array and conventionnal path is the only way i know with odp.net to load one oracle table in parallel. But it is cpu intensive for Oracle and does not scale well.
• I tried with /*+ SYS_DL_CURSOR */ hint, but it seam the hint is ignored (in 21 at least) and the inserts use conventional path.
• I had hope with APPEND_VALUES hint + bind arrays but the ORA-38910: BATCH ERROR mode stopped me.

[alexkeh]

Sorry, I misunderstood your original question. I thought you wanted to suppress the error only.

ODP.NET hasn't implemented bulk copy parallel loads. If there are enough votes for this feature, we will prioritize it for one of our quarterly updates,

[alexkeh]

How do SqlClient and Npgsql implement parallel bulk copy? The most straightforward implementation for ODP.NET may be emulating that method. However, it doesn't seem like there is a BulkCopy API they use to enable parallelism. Instead, the process appears to be manual.

Do you have an example of the bulk copy code you use with SQL Server or PostgreSQL that shows this parallelism?

[rferraton]

for Ngpgsql

you have method on the connection to start/stop a writer
var writer = npgsqltargetConnection.BeginTextImport(pgcopycommand)
Then you can iterate over a dbdatareder , getvalue and use the writer :

var writer = npgsqltargetConnection.BeginTextImport(pgcopycommand);
while (sdr.Read())
{
				for (int i = 0; i < sdr.FieldCount; i++)
				{		if (i > 0)
								writer.Write(CsvDelimiter);
					object value = sdr.GetValue(i);

					if (value != null)
						{
							writer.Write(CsvQuote);
							writer.Write(value.ToString());
							writer.Write(CsvQuote);
						}
				}

           writer.WriteLine();
}
writer.Flush();
writer.Close();
writer.Dispose();

This part can be parallelized in a parallel for loop and are truly parrallel (several postgres process COPY from stdin to the same target table without locks)

For SQL Server :

sbc = new SqlBulkCopy(sqlTargetConnection as SqlConnection, options, null);
sbc.DestinationTableName = $"{targetschema}.{targettable}";
sbc.BulkCopyTimeout = bulktimeout;
sbc.BatchSize = bulkbatchsize;
sbc.EnableStreaming = true;
using (DbDataReader sdr = sqr.ExecuteReader())
{
	if (mapmethod == "Name")
	{
		List<string> targetColumns = GetTargetTableColumns("msbulk", targetschema, targettable, sqlTargetConnection);
		MapColumnsByName(targetschema, targettable, sbc, targetColumns, sdr);
	}

	ccols = sdr.FieldCount;

	sbc.WriteToServer(sdr); // Will read all the sdr and push to sbc. Synchronous is faster.
	crows += sbc.RowsCopied64;
}

This part can be parallelized in a parallel for loop and are truly parrallel (several sql server session process bulk insert from the dbdatareader to the same target table without locks)

For Oracle

It is quite similar to SQL Server, but it failed when running in parallel. I think it would be beneficial to implement a parallel load feature in ODP.NET to achieve SQL*Loader parallel load capability. The faster you can integrate data, the easier it will be for people to move data to Oracle instead of from Oracle.

[alexkeh]

I've created ER 38517030 to track this enhancement request.

[rferraton]

If you need i will be able to make some tests when the feature will be in preview, just ping and ask me

[alexkeh]

@rferraton Can you provide the complete SqlClient (SQL Server) sample? The behavior we see in our testing is not the same as you are observing.

[rferraton]

@alexkeh : what are you're DDL of the target table and what sbc.options do you use ?

If you want to try parallel load to SQL Server with our tool fasttransfer, feel free to download free trial on https://fasttransfer.arpe.io/

The Run this postgreSQL source and a MSSQL target

./FastTransfer \
  --sourceconnectiontype "pgsql" \
  --sourceserver "your-source-server" \
  --sourceuser "your-username" \
  --sourcepassword "your-password" \
  --sourcedatabase "your-source-db" \
  --sourceschema "your_schema" \
  --sourcetable "your_table" \
  --targetconnectiontype "msbulk" \
  --targetserver "your-target-server" \
  --targetuser "your-username" \
  --targetpassword "your-password" \
  --targetdatabase "your-target-db" \
  --targetschema "your_table" \
  --targettable "your_table" \
  --parallelmethod "Ctid"  \
  --degree -2  \
  --loadmode "Truncate"

You can the retry with an oracle target with this , and see that parallelism is not a real parallel load

./FastTransfer \
  --sourceconnectiontype "pgsql" \
  --sourceserver "your-source-server" \
  --sourceuser "your-username" \
  --sourcepassword "your-password" \
  --sourcedatabase "your-source-db" \
  --sourceschema "your_schema" \
  --sourcetable "your_table" \
  --targetconnectiontype "orabulk" \
  --targetserver "your-target-server" \
  --targetuser "your-username" \
  --targetpassword "your-password" \
  --targetdatabase "your-target-db" \
  --targetschema "your_table" \
  --targettable "your_table" \
  --parallelmethod "Ctid"  \
  --degree -2  \
  --loadmode "Truncate"

I cannot give the secret sauce sorry ...

[alexkeh]

@rferraton We tried running BulkCopy testcase against SQL Server. When applying parallelization, we observed a decrease in performance of 140%.

We created a destination table "Employee_dst" with 2 columns of type "string" and "int". We inserted 10 million records in the table with "DataTable" as source using a single bulk copy operation and a parallel BulkCopy operation (with 4 threads).

Using a single BulkCopy operation against SQL Server, it took 57.25 sec.
Using a parallel BulkCopy operation against SQL Server, it took 2 min and 17.31 sec.

I can share the test cases if you'd like to review them. Below are the sbc.options we're using:
sbc.DestinationTableName = "Employee_dst";
sbc.EnableStreaming = true;
sbc.BulkCopyTimeout = 0; // Infinite timeout

The parallel BulkCopy test used 4 parallel threads, each inserting 2.5M records.

Any recommended configuration options that would demonstrate parallelization performance benefits?

[rferraton]

@alexkeh : you can try to add SqlBulkCopyOptions.TableLock and SqlBulkCopyOptions.UseInternalTransaction

[rferraton]

@alexkeh : any news ?

[rferraton]

Here an exemple of serial load in sql server

and a parallel load using fasttransfer