LinqCommandExecutionException on larger MaxBatchSize

Discussion of open issues, suggestions and bugs regarding LinqConnect – Devart's LINQ to SQL compatible ORM
Post Reply
SFI
Posts: 1
Joined: Fri 20 May 2011 22:04

LinqCommandExecutionException on larger MaxBatchSize

Post by SFI » Fri 20 May 2011 22:20

Hello,

I'm trying to put a large amount (~500,000) of small datasets into my Oracle DB (just for testing) via LinqConnect.

To speed things up, I tried different values for MaxBatchSize. I started with 500 and everything went fine, but slow (~5 minutes).

In the next step I increased MaxBatchSize to 10000, but then I got the exception below.

Here's my code (not pretty, but working):

Code: Select all

Random r = new Random();

MyDataContext context = new MyDataContext();
context.MaxBatchSize = 5000;

Debug.WriteLine("***** Start {0:hh-mm-ss:fff}", DateTime.Now);

long subId = 100;

for (int i = 100; i http://msdn.microsoft.com/TraceCodes/System/ActivityTracing/2004/07/Reliability/Exception/UnhandledUnhandled exceptionConsoleApplication1.vshost.exeDevart.Data.Linq.LinqCommandExecutionException, Devart.Data.Linq, Version=2.50.23.0, Culture=neutral, PublicKeyToken=09af7300eec23701Error on executing DbCommand.   at Devart.Data.Linq.LinqCommandExecutionException.CanThrowLinqCommandExecutionException(String message, Exception e)
   at Devart.Data.Linq.Provider.DataProvider.ExecuteQuery(String commandText, List`1 resultsetParameters, IList`1 parameters, Boolean isBatch, IDbCommand& dbCommand)
   at Devart.Data.Linq.h.a(SubmitCommand A_0, SubmitCommandBuilder A_1)
   at Devart.Data.Linq.q.a(SubmitCommandBuilder A_0)
   at Devart.Data.Linq.q.a(SubmitedObject A_0, SubmitCommand A_1, SubmitCommandBuilder A_2)
   at Devart.Data.Linq.r.a(k A_0, ac A_1, Object A_2, Boolean A_3)
   at Devart.Data.Linq.x.a(r A_0, ac A_1, Boolean A_2)
   at Devart.Data.Linq.x.a(DataContext A_0, ConflictMode A_1)
   at Devart.Data.Linq.DataContext.SubmitChanges(ConflictMode failureMode)
   at Devart.Data.Linq.DataContext.SubmitChanges()
   at ConsoleApplication1.Program.Fill() in C:\Projects\Test\ConsoleApplication1\Program.cs:line 72
   at ConsoleApplication1.Program.Main(String[] args) in C:\Projects\Test\ConsoleApplication1\Program.cs:line 14
   at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
   at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
   at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
   at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.ThreadHelper.ThreadStart()Devart.Data.Linq.LinqCommandExecutionException: Error on executing DbCommand. ---> Devart.Data.Oracle.OracleException: ORA-00060: Deadlock beim Warten auf Ressource festgestellt
ORA-06512: in Zeile 1
   at Devart.Data.Oracle.am.b(Int32 A_0)
   at Devart.Data.Oracle.ao.a(Int32 A_0, f A_1)
   at Devart.Data.Oracle.OracleCommand.InternalExecute(CommandBehavior behavior, IDisposable disposable, Int32 startRecord, Int32 maxRecords, Boolean nonQuery)
   at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
   at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
   at Devart.Data.Linq.Provider.DataProvider.ExecuteQuery(String commandText, List`1 resultsetParameters, IList`1 parameters, Boolean isBatch, IDbCommand& dbCommand)
   --- End of inner exception stack trace ---
   at Devart.Data.Linq.LinqCommandExecutionException.CanThrowLinqCommandExecutionException(String message, Exception e)
   at Devart.Data.Linq.Provider.DataProvider.ExecuteQuery(String commandText, List`1 resultsetParameters, IList`1 parameters, Boolean isBatch, IDbCommand& dbCommand)
   at Devart.Data.Linq.h.a(SubmitCommand A_0, SubmitCommandBuilder A_1)
   at Devart.Data.Linq.q.a(SubmitCommandBuilder A_0)
   at Devart.Data.Linq.q.a(SubmitedObject A_0, SubmitCommand A_1, SubmitCommandBuilder A_2)
   at Devart.Data.Linq.r.a(k A_0, ac A_1, Object A_2, Boolean A_3)
   at Devart.Data.Linq.x.a(r A_0, ac A_1, Boolean A_2)
   at Devart.Data.Linq.x.a(DataContext A_0, ConflictMode A_1)
   at Devart.Data.Linq.DataContext.SubmitChanges(ConflictMode failureMode)
   at Devart.Data.Linq.DataContext.SubmitChanges()
   at ConsoleApplication1.Program.Fill() in C:\Projects\Test\ConsoleApplication1\Program.cs:line 72
   at ConsoleApplication1.Program.Main(String[] args) in C:\Projects\Test\ConsoleApplication1\Program.cs:line 14
   at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
   at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
   at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
   at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.ThreadHelper.ThreadStart()Devart.Data.Oracle.OracleException, Devart.Data.Oracle, Version=6.30.160.0, Culture=neutral, PublicKeyToken=09af7300eec23701ORA-00060: Deadlock beim Warten auf Ressource festgestellt
ORA-06512: in Zeile 1   at Devart.Data.Oracle.am.b(Int32 A_0)
   at Devart.Data.Oracle.ao.a(Int32 A_0, f A_1)
   at Devart.Data.Oracle.OracleCommand.InternalExecute(CommandBehavior behavior, IDisposable disposable, Int32 startRecord, Int32 maxRecords, Boolean nonQuery)
   at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
   at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
   at Devart.Data.Linq.Provider.DataProvider.ExecuteQuery(String commandText, List`1 resultsetParameters, IList`1 parameters, Boolean isBatch, IDbCommand& dbCommand)Devart.Data.Oracle.OracleException (0x80004005): ORA-00060: Deadlock beim Warten auf Ressource festgestellt
ORA-06512: in Zeile 1
   at Devart.Data.Oracle.am.b(Int32 A_0)
   at Devart.Data.Oracle.ao.a(Int32 A_0, f A_1)
   at Devart.Data.Oracle.OracleCommand.InternalExecute(CommandBehavior behavior, IDisposable disposable, Int32 startRecord, Int32 maxRecords, Boolean nonQuery)
   at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
   at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
   at Devart.Data.Linq.Provider.DataProvider.ExecuteQuery(String commandText, List`1 resultsetParameters, IList`1 parameters, Boolean isBatch, IDbCommand& dbCommand)
[/code]
Top
StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Mon 23 May 2011 12:59

Oracle has certain limitations on the size of PL/SQL blocks; please refer, e.g., to
http://www.orafaq.com/wiki/PL/SQL_FAQ#I ... L_block.3F
http://download.oracle.com/docs/cd/B283 ... limits.htm
Because of these limitations, it is not recommended to use large batches for update operations.

To improve performance of data insertion, you can use the OracleLoader component. However, it is available in the Professional and Developer editions of dotConnect for Oracle only.
Top
Post Reply

Return to “LinqConnect (LINQ to SQL support)”