Our application is using LLBLGen Pro in self-servicing mode. Lately we happen to run a batch process with this one. The batch process have to process about 300,000 records. Arbitrarily I am getting timeout errors:

SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException: An exception was caught during the execution of a retrieval 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. at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.ExecuteSingleRowRetrievalQuery(IRetrievalQuery queryToExecute, ITransaction containingTransaction, IEntityFields fieldsToFill) at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.PerformFetchEntityAction(IEntity entityToFetch, ITransaction containingTransaction, IPredicateExpression selectFilter, IPrefetchPath prefetchPathToUse, Context contextToUse) at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.FetchExisting(IEntity entityToFetch, ITransaction containingTransaction, IPrefetchPath prefetchPathToUse, Context contextToUse) at CDMS.DAL.EntityClasses.EntitydataEntity.Fetch(Decimal entityDataId, IPrefetchPath prefetchPathToUse, Context contextToUse) at CDMS.DAL.EntityClasses.EntitydataEntity.Refetch() at SD.LLBLGen.Pro.ORMSupportClasses.EntityBase.CheckForRefetch() at SD.LLBLGen.Pro.ORMSupportClasses.EntityBase.GetCurrentFieldValue(Int32 fieldIndex) at CDMS.DAL.EntityClasses.EntitydataEntity.get_FlagExcludeFromPeerGroupData() at CDMS.BLMGR.EntityDataBLMGR.SaveTransformationEntityData(EntitydataEntity objUpdateEntityData, EntitymovementsEntity objUpdateEntityMovements, String applyTransformationName, Int32 applyTransformationVersion, Int32 userID, String strEntityDataChanges)

System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached. at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at CDMS.DAL.HelperClasses.DbUtils.CallActionStoredProcedure(String storedProcedureToCall, SqlParameter[] parameters, ITransaction transactionToUse) at CDMS.DAL.StoredProcedureCallerClasses.ActionProcedures.UpdateReviewFlaginEntityData(Int32 intSelectedOption, String entityId, String reportingYear, String sicCodes) at CDMS.BLMGR.EntityDataBLMGR.SaveTransformationEntityData(EntitydataEntity objUpdateEntityData, EntitymovementsEntity objUpdateEntityMovements, String applyTransformationName, Int32 applyTransformationVersion, Int32 userID, String strEntityDataChanges)

I saw a related thread: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=3964&HighLight=1

My case is very similar to this except I don't debug my application - I have deployed it in release mode.

Please help.

I forgot to mention the configuration - Ours is .NET 2.0, LLBLGen Pro 2.0 on a Windows 2003 server.

Hi Srikanth, could you please post the code snippet where you are getting this exception? Also your (app | web).config file would be useful. (If you want more confidential, you can open a thread at HelpDesk forum).

You can set the timeout in DbUtils for a command. However if you're reading 300,000 entities at once you'll likely need more than 30 seconds which is the default. I'd suggest to use paging, e.g. sets of 100 entities or 500 entities and execute the processing on these, then move on to the next batch.

Sorry, I forgot to mention the way I fetch data. I faced a different issue and I was suggested to fetch and process chunk by chunk of data (100 records at a time etc.).

See here: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=10258

Based on this I have modified the code and now the process only reads 100 records at a time and then processes it. To process 300,000 records, even if we go 100 records at a time, it would take days to complete (processing each record has several steps - so takes time).

So we implemented multi-threading - so that about 25 records are processed simultaneously (We fetch 100 records, queue it up in ThreadPool which is configured to run 25 threads at a time).

Now arbitrarily after running for an hour or so, the timeout error appears. I wish to know is this because of multi-threading + self-servicing ?? I used LLBLGen 2.0 and 2.5 as well. In both versions I am getting the above mentioned errors.

Based on this I have modified the code and now the process only reads 100 records at a time and then processes it. To process 300,000 records, even if we go 100 records at a time, it would take days to complete (processing each record has several steps - so takes time).

So we implemented multi-threading - so that about 25 records are processed simultaneously (We fetch 100 records, queue it up in ThreadPool which is configured to run 25 threads at a time).

Please post a code snippet.

        /// <summary>
        /// Applies transformation for the given set of peergroups and years
        /// </summary>
        public bool applyTransformation(int applyTransformationUID, string selectedDatasets, string transformationYears, string transformationpeerGroups, int userID)
        {
            // Retrieves Transformation version
            TransformationversionCollection objTransformationVersionCol = this.getTransformation(applyTransformationUID);

            // Find the maximum threads to run
            int maxThread = 0;
            maxThread = Convert.ToInt32(ConfigurationSettings.AppSettings["TransformationMaximumThread"].ToString());

            ThreadPool.SetMaxThreads(maxThread, 0);

            // Loop on selected years
            for (int j = 0; j < selectedYears.Length; j++)
            {
                // Loop on peer group details from table.
                for (int i = 0; i < selectedPeerGroups.Length; i++)
                {
                    // instantiate transformation data
                    TransformationData objDT = new TransformationData(selectedPeerGroups[i], selectedYears[j], selectedDatasets, applyTransformationUID, applyTransformationVersion, applyTransformationName, userID, transformationversionID);

                    // instantiate threadpool wait object
                    ThreadPoolWait.WorkTracker job = new ThreadPoolWait.WorkTracker(new WaitCallback(this.TransformationWorker));

                    // Queue !
                    ThreadPool.QueueUserWorkItem(job, objDT);
                }

                // Wait for all peergroups in this iteration to finish
                ThreadPoolWait.WorkTracker.WaitForAll();
            }
        }

        /// <summary>
        /// Worker wrapper function
        /// </summary>
        private void transformationWorker(object state)
        {
            TransformationData objTD = (TransformationData)state;

            // Invoke calculation
            this.processTransformation(objTD.selectedPeerGroupID, objTD.selectedYear, objTD.selectedDatasets, objTD.applyTransformationUID, objTD.applyTransformationVersion, objTD.applyTransformationName, objTD.userID, objTD.transformationversionID);
        }

        /// <summary>
        /// Processing calculation 
        /// </summary>
        /// <returns></returns>
        private bool processTransformation(string selectedPeerGroupID, string selectedYear, string selectedDatasets, int applyTransformationUID, int applyTransformationVersion, string applyTransformationName, int userID, string transformationversionID)
        {
            try
            {
                VwtransformationentitydatalistTypedView objEntityDataList = this.GetEntityDataListForPeerGroupYear(selectedPeerGroupID, selectedYear, selectedDatasets, applyTransformationUID);

                this.processEntityData(objEntityDataList, applyTransformationUID, applyTransformationVersion, applyTransformationName, userID, transformationversionID);
            }
            catch (Exception ex)
            {               
                 // Logging
            }
            return true;
        }

Let me explain the usage of the methods: **applyTransformation ** - This method is invoked from an .NET Windows service. There are two loops in this method - the outer loop is for the number of years chosen for processing - The maximum value could be 7 (2000 - 2007).
The inner loop is on a collection of 'peergroup' (our application specific) objects - which could be at the maximum 100 - 120. Inside the inner loop, we queue each 'peergroup' objects as a thread (in .NET ThreadPool) for processing. Each 'peergroup' object will have a collection of other data objects ranging from 100 - 10000 - each one of these are processed one by one within the threadpool thread.

We have used code from here: http://staff.develop.com/igriffiths/ThreadPoolWait/WorkTracker.cs to wait for the threads in the pool to complete their work.

transformationWorker - This is just a wrapper method that calls the actual method which has the logic for processing.

processTransformation - This method retrieves data based on the parameters and calls other methods for processing.

I have reduced the code for understanding. Our LLBLGen Pro usage is very simple - as we are only using Self-servicing components - there is not much tweaking there.

Please get back to me if you want me to clarify anything. I would try to develop a sample soon.

What could be a reason is that after a while a query takes just longer than 30 seconds and then triggers the timeout because it has to wait for the others (because the db server runs out of tempdb space or other resources and has to swap something out). Have you tried increasing the timeout value in the DbUtils class ?

Also, in v2.5 we introduced excluded fields support. It might be you can exclude some fields in the entities you're fetching which are probably not needed, so this could lead to less data being fetched.