Hi again,
I have released my modified code, which prevents the, in my case, unnecessary SELECT (described above) by forcing the fields collection's Fetched state.
I had hoped that this would resolve a series of timeouts I was getting from an InsertEntity() call , by reducing row and page locks caused by the subsequent selects.
(The code is running on every page hit, for tracking purposes, so its relatively high-volume. The target table is currently around 12 m rows)
But - no luck.
I know this is sub-optimal,but as a desparate measure, I also implemented this 'fix':
SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine.UseNoLockHintOnSelects = true;
But I'm still getting the errors from time-to-time (typically in batches)
The error text is below:
SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException: An exception was caught during the execution of an action query: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception. ---> System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at SD.LLBLGen.Pro.ORMSupportClasses.ActionQuery.Execute() --- End of inner exception stack trace --- at SD.LLBLGen.Pro.ORMSupportClasses.ActionQuery.Execute() at SD.LLBLGen.Pro.ORMSupportClasses.BatchActionQuery.Execute() at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.ExecuteActionQuery(IActionQuery queryToExecute, ITransaction containingTransaction) at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.AddNew(IEntityFields fields, ITransaction containingTransaction) at SN.DL.EntityClasses.SourcedPageHitEntity.InsertEntity() at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.PersistQueue(List`1 queueToPersist, Boolean insertActions, ITransaction transactionToUse, Int32& totalAmountSaved) at SD.LLBLGen.Pro.ORMSupportClasses.EntityBase.Save(IPredicate updateRestriction, Boolean recurse) at SN.BL.SourcedPageHit.LogSourcedHitMidSession(HttpContext cont)
The generated SQL is (with schema and paramater names obfuscated):
declare @p3 int
set @p3=1884367
exec sp_executesql N'INSERT INTO [dbName].[dbo].[tblName] ([intParam1], [varcharParam], [intparam2], [dateTimeParam]) VALUES (@intParam1, @varcharParam, @intparam2, @dateTimeParam);SELECT
@outputId=SCOPE_IDENTITY()',N'@outputId int output,@intParam1 smallint,@varcharParam varchar(100),@intparam2 int,@dateTimeParam datetime',@outputId=@p3
output,@intParam1=10,@varcharParam='',@intparam2=1927668,@dateTimeParam=''2008-03-14 14:25:44:543''
select @p3
The table is indexed OK, I think (or the performance tuning wizard thinks so). I'm a little baffled. Effectively this is a simple insert into a table, with an integer primary key - It should be quick.
Any collective wisdom very welcome.