In our current development we had the problem that our document upload into the oracle database took very long. It was completly beyond the pale.
With our Oracle Specialist Carl Reitschuster we discovered that this must be a problem of the ADO .NET implementation of Oracle. The upload with the PL Developer took only about 2 seconds, but with our C# code it took 46 seconds for the same document upload.
We found out that the ADO .NET Driver for Oracle is very slow when the upload buffer is set at once.
/// <summary>
/// Examples the of slow upload. (about 46 seconds)
/// </summary>
public void ExampleOfSlowUpload ()
{
startTime = DateTime.Now;
transaction = oracle.BeginTransaction();
OracleCommand command = new OracleCommand (uploadSQL, oracle);
OracleParameter uploadParameter = new OracleParameter(":blob", OracleDbType.Blob, ParameterDirection.Input);
uploadParameter.Value = uploadBuffer;
command.Parameters.Add(uploadParameter);
command.ExecuteNonQuery();
transaction.Commit();
Console.WriteLine("Slow upload took : " + new TimeSpan(DateTime.Now.Ticks - startTime.Ticks).TotalSeconds + " seconds");
}
After a long term of search we discovered a solution that splits the upload buffer to small chunks. With this hint we could speed up the upload from 46 seconds to 4 seconds at all.
/// <summary>
/// Examples the of fast upload. (about 4 seconds)
/// </summary>
public void ExampleOfFastUpload ()
{
startTime = DateTime.Now;
transaction = oracle.BeginTransaction();
OracleCommand command = new OracleCommand (uploadSQL, oracle);
OracleParameter uploadParameter = new OracleParameter(":blob", OracleDbType.Blob, ParameterDirection.Input);
OracleBlob blob = new OracleBlob(oracle);
int startOffset = 0;
int writeBytes = 0;
/*
* ... now load the buffer in small chunks to Oracle
*/
blob.BeginChunkWrite();
do
{
writeBytes = startOffset+BUFFER_SIZE>uploadBuffer.Length? uploadBuffer.Length-startOffset : BUFFER_SIZE;
blob.Write(uploadBuffer, startOffset, writeBytes);
startOffset += writeBytes;
} while (startOffset < uploadBuffer.Length);
blob.EndChunkWrite();
uploadParameter.Value = blob;
command.Parameters.Add(uploadParameter);
command.ExecuteNonQuery();
transaction.Commit();
Console.WriteLine("Fast upload took : " + new TimeSpan(DateTime.Now.Ticks - startTime.Ticks).TotalSeconds + " seconds");
}
The complete demo solution can be downloaded from here:
OracleUploadProblem.zip
