Poor Update Performance using OracleDataAdapter

Poor Update Performance using OracleDataAdapter

Postby TheOneTrueAnt » Fri 18 Oct 2013 15:52

Hi there,

I'm finding that I get extremely low update performance from dotConnect for oracle when performing a large number of updates (about 5500 rows of about 15-20 columns). The update takes around 100 seconds to complete whereas performing exactly the same process using ODP.net takes around 10!

After doing some digging I have determined that both ODP and dotConnect are issuing nearly identical DML statements so it doesn't appear to be Database or Oracle Client-related.

There does appear to be something odd in that when I call the OracleDataAdapter.Update(DataTable) method to apply my changes there occur many "handled" ArgumentExceptions originating in DevArt & Framework code for every row of data. This doesn't happen for ODP and I assume is where there is a large performance penalty.

This does seem to have been raised before and I've encountered it in both 7.8 and 8.0 of dotConnect for Oracle.

Any Ideas?

-Antony

Here are a couple of examples:

Code: Select all
Message: "Input String was not in correct format"

Stak:
   mscorlib.dll!System.Number.StringToNumber   
   mscorlib.dll!System.Number.ParseInt32   
   mscorlib.dll!string.System.IConvertible.ToInt32   
   mscorlib.dll!System.Convert.ChangeType   
   Devart.Data.Oracle.dll!Devart.Data.Oracle.OracleUtils.a   
   Devart.Data.Oracle.dll!Devart.Data.Oracle.OracleParameter.OracleDbType.set   
   Devart.Data.Oracle.dll!Devart.Data.Oracle.OracleParameter.DbType.set   
   System.Data.dll!System.Data.Common.DbCommandBuilder.CreateParameterForNullTest   
   System.Data.dll!System.Data.Common.DbCommandBuilder.BuildWhereClause   
   System.Data.dll!System.Data.Common.DbCommandBuilder.BuildUpdateCommand   
   System.Data.dll!System.Data.Common.DbCommandBuilder.RowUpdatingHandlerBuilder   
   System.Data.dll!System.Data.Common.DbCommandBuilder.RowUpdatingHandler   
   Devart.Data.dll!Devart.Common.DbCommandBuilder.RowUpdatingHandler   
   Devart.Data.Oracle.dll!Devart.Data.Oracle.OracleCommandBuilder.a   
   Devart.Data.Oracle.dll!Devart.Data.Oracle.OracleDataAdapter.OnRowUpdating   
   System.Data.dll!System.Data.Common.DbDataAdapter.Update   
   System.Data.dll!System.Data.Common.DbDataAdapter.UpdateFromDataTable   
   System.Data.dll!System.Data.Common.DbDataAdapter.Update   

Message: Invalid cast from 'DateTime' to 'Int32'

Stack:
   mscorlib.dll!System.DateTime.System.IConvertible.ToInt32   
   mscorlib.dll!System.Convert.ChangeType   
   Devart.Data.Oracle.dll!Devart.Data.Oracle.OracleUtils.a   
   Devart.Data.Oracle.dll!Devart.Data.Oracle.OracleParameter.OracleDbType.set   
   Devart.Data.Oracle.dll!Devart.Data.Oracle.OracleParameter.DbType.set   
   System.Data.dll!System.Data.Common.DbCommandBuilder.CreateParameterForNullTest   
   System.Data.dll!System.Data.Common.DbCommandBuilder.BuildWhereClause   
   System.Data.dll!System.Data.Common.DbCommandBuilder.BuildUpdateCommand   
   System.Data.dll!System.Data.Common.DbCommandBuilder.RowUpdatingHandlerBuilder   
   System.Data.dll!System.Data.Common.DbCommandBuilder.RowUpdatingHandler   
   Devart.Data.dll!Devart.Common.DbCommandBuilder.RowUpdatingHandler   
   Devart.Data.Oracle.dll!Devart.Data.Oracle.OracleCommandBuilder.a   
   Devart.Data.Oracle.dll!Devart.Data.Oracle.OracleDataAdapter.OnRowUpdating   
   System.Data.dll!System.Data.Common.DbDataAdapter.Update   
   System.Data.dll!System.Data.Common.DbDataAdapter.UpdateFromDataTable   
   System.Data.dll!System.Data.Common.DbDataAdapter.Update   

TheOneTrueAnt
 
Posts: 2
Joined: Fri 18 Oct 2013 15:38

Re: Poor Update Performance using OracleDataAdapter

Postby Pinturiccio » Mon 21 Oct 2013 15:19

We have reproduced the issue with performance, but without generating an exception. Please Send us the following for reproducing the issue:
1. DDL script of your table;
2. DML script of some rows;
3. The snippet of code where you create connection with connection string (roughly, without credentials). Creating command and OracleDataAdapter. A small test project will be appreciated.
Pinturiccio
Devart Team
 
Posts: 1978
Joined: Wed 02 Nov 2011 09:44

Re: Poor Update Performance using OracleDataAdapter

Postby TheOneTrueAnt » Tue 22 Oct 2013 16:12

I'll try to get something put together as soon as I can!
TheOneTrueAnt
 
Posts: 2
Joined: Fri 18 Oct 2013 15:38

Re: Poor Update Performance using OracleDataAdapter

Postby Pinturiccio » Wed 30 Oct 2013 15:31

We have fixed the bug with setting OracleDataAdapter.UpdateBatchSize=0 during creation of an OracleDataAdapter instance by some constructors. We will notify you the corresponding build of dotConnect for Oracle is available for download.
Pinturiccio
Devart Team
 
Posts: 1978
Joined: Wed 02 Nov 2011 09:44

Re: Poor Update Performance using OracleDataAdapter

Postby Pinturiccio » Fri 01 Nov 2013 10:47

New version of dotConnect for Oracle 8.1 is released.
It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with active subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?t=28241.

Does this version fix the issue?
Pinturiccio
Devart Team
 
Posts: 1978
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for Oracle