I’m not sure if this should be treated as a bug or just an inquiry about how the CommandTimeOut property works but I’ll post this here anyway.

We are using the LLBLGenPro 5.3 on our web application and the database is Azure SQL Server.

In the system we have a reporting section where users can get tabular data in the system from database tables that are quite large or around (7-15 million rows). Instead of using conditional boundaries to narrow the search criteria as the amount of data differs heavily between clients, we would like to instead use a time out value that stops the query after a specific amount of time and notifies the users and encourages him to narrow the search criteria.

This brings me back to my topic that I’ve tried using the CommandTimeOut property for this purpose but it’s not working consistently. Sometime is works and sometimes it doesn’t. In our case we have the limit set to 90 seconds, but I’ve seen our system hang for over 10 minutes on the same query making the system almost unusable as the DTU level on Azure is at max.

This is the get method we’re using:

public EntityCollection<K> GetAll(IRelationPredicateBucket filter, IPrefetchPath2 prefetchPath, ISortExpression sorter, ExcludeIncludeFieldsList excludeIncludeFieldsList)
        {
            EntityCollection<K> entityCollection = new EntityCollection<K>();

            try
            {
                using (DataAccessAdapter adapter = new DataAccessAdapter())
                {
                    SetLLBGenConfigurations(adapter);
                    adapter.FetchEntityCollection(entityCollection, filter, 0, sorter, prefetchPath, excludeIncludeFieldsList);
                } // using
            }
            catch (Exception ex)
            {
                _logger.Error("GetAll Error", ex);
            }

            return entityCollection;
        }

 protected void SetLLBGenConfigurations(DataAccessAdapter adapter)
        {
            RecoveryDelay delay = new RecoveryDelay(new TimeSpan(0, 0, LLBGenStrategyRecoveryDelayInSeconds), 2, RecoveryStrategyDelayType.Linear);
            var strategy = new SqlAzureRecoveryStrategy(LLBGenStrategyMaximumNumberOfRetries, delay);
            adapter.ActiveRecoveryStrategy = strategy;
            adapter.CommandTimeOut = 90;
        }

Is it not my correct understanding that this CommandTimeOut should be working consistently and stopping the query after 90 seconds ?

Hi there,

What I can see is that you want the query stops at 90 seconds, while you are setting a retry strategy. The retry strategy is used to recover from transient errors, and in the case of SqlAzureRecoveryStrategy, the timeout is seen as a transient error. So maybe you have to remove that strategy configuration as you don't want retries, you want the query stops and fail at the timeout.

daelmo wrote:

Hi there,

What I can see is that you want the query stops at 90 seconds, while you are setting a retry strategy. The retry strategy is used to recover from transient errors, and in the case of SqlAzureRecoveryStrategy, the timeout is seen as a transient error. So maybe you have to remove that strategy configuration as you don't want retries, you want the query stops and fail at the timeout.

Hi, sorry my bad for not mentioning it in the original post but I'm actually aware of this retry strategy and both the LLBGenStrategyMaximumNumberOfRetries and LLBGenStrategyRecoveryDelayInSeconds variables are set to 0.

Could you please try without a A Recovery Strategy?

Also, please test it with the strategy in place but using the following values:

LLBGenStrategyMaximumNumberOfRetries = 3

LLBGenStrategyRecoveryDelayInSeconds = 90 (same as command time out).

Timeouts can occur at different levels, and on Azure you can get a connection timeout simply because the network disconnects (which results in a transient error, and which makes the recovery strategy to retry, which means everything starts from the beginning). I think you sometimes run into this but also sometimes you run into the timeout you've set yourself, hence the inconsistency. Sadly connecting to Azure SQL is notorious unreliable so it's not something you can factor out when using a set timeout on the query itself: it might very well be the connection dies before that and that will make the recovery strategy start over. So Walaa's suggestions might help get you where you want to be, tho due to the nature of how unreliable Azure SQL's connections are, it might be you'll never fully get what you are striving for.

Thank you for this.

I suspected that this might be the case.

Do you know if there's any alternative method to achieve this? Like using async calls and the CancellationToken for example? Or are you eventually going to run into the same problem due to the inconsistency of the Azure SQL server.?

I've been using LLBLGen with Azure for years, maybe 9, and not had any problems. I have this recovery strategy:

        protected override bool IsTransientException(Exception toCheck)
        {
            var txEx = toCheck as TransactionException;
            if (txEx?.InnerException is TransactionPromotionException)
            {
                Log.WarnFormat("transient error, will retry {0}", ExceptionHelper.GetErrorText(txEx));
                return true; // this can happen when opening the connection
            }

            // exclude deadlocks
            var toCheckAsSqlException = toCheck as SqlException;
            if (toCheckAsSqlException == null)
            {
                return false;
            }
            if ((from SqlError error in toCheckAsSqlException.Errors select error.Number).Any(number => number == 1205))
            {
                return false;
            }
            var isTransient = base.IsTransientException(toCheck);
            if(isTransient)
            {
                Log.WarnFormat("transient error, base indicates will retry {0}", ExceptionHelper.GetErrorText(toCheck));
            }
            return isTransient;
        }

kj_flyware wrote:

Do you know if there's any alternative method to achieve this? Like using async calls and the CancellationToken for example? Or are you eventually going to run into the same problem due to the inconsistency of the Azure SQL server.?

You can't avoid the transient errors when using azure sql, so these will pop up. As you want to stop everything after a specific amount of time, you might want to look into not using a recovery strategy at all, or change the timeout perhaps with the time it took since the last transient error, but it stays problematic. I personally think having a timeout as a way of flow control (similar to exceptions deciding where to go next) might not be the best approach. If a query takes a long time, perhaps change the query so it doesn't take a long time (e.g. paging or other changes might help)

@yowl thanks for sharing

Thank you for this.

It might be the best practice to use as you mentioned more optimized SQL queries and paging.

We're currently reevaluating this.

Thank you for your help.