Devart Forums

Posted: **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

Posted: **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.

Posted: **Tue 22 Oct 2013 16:12**

I'll try to get something put together as soon as I can!

Posted: **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.

Posted: **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?

Devart Forums

Poor Update Performance using OracleDataAdapter

Poor Update Performance using OracleDataAdapter

Re: Poor Update Performance using OracleDataAdapter

Re: Poor Update Performance using OracleDataAdapter

Re: Poor Update Performance using OracleDataAdapter

Re: Poor Update Performance using OracleDataAdapter