Click here to Skip to main content
15,843,200 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more: , +
"Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding." in distributed sql server cache

My Method to call in controller

public async Task<IEnumerable<NormalDeposit_DepositDetails>> GetAllNormalDeposit_DepositDetails_Async()
    {
        if (IsCacheEnable == true)
        {
            IEnumerable<NormalDeposit_DepositDetails> NormalDeposit_DepositDetails_AllList;
            var KeyHere = KeyCache.GenCacheKey(CacheKeyList.NormalDeposit_DepositDetails_CacheSecurityKeyString, SecurityKey);
            var GetCachehere = await distributedCache.GetStringAsync(KeyHere);
            if (string.IsNullOrEmpty(GetCachehere))
            {
                context.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;
                NormalDeposit_DepositDetails_AllList = await context.NormalDeposit_DepositDetails.AsNoTracking().ToListAsync();


                DistributedCacheEntryOptions options = new DistributedCacheEntryOptions();
                options.AbsoluteExpiration = DateTime.Now.AddHours(12);
                options.SlidingExpiration = TimeSpan.FromMinutes(30);
                var Value = JsonConvert.SerializeObject(NormalDeposit_DepositDetails_AllList);
               await distributedCache.SetStringAsync(KeyHere, Value, options);
                return NormalDeposit_DepositDetails_AllList;
            }
            else
            {
                return JsonConvert.DeserializeObject<IEnumerable<NormalDeposit_DepositDetails>>(GetCachehere);
            }
        }
        else
        {
            IEnumerable<NormalDeposit_DepositDetails> NormalDeposit_DepositDetails_AllList;
            context.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;
            NormalDeposit_DepositDetails_AllList = await context.NormalDeposit_DepositDetails.AsNoTracking().ToListAsync();
            return NormalDeposit_DepositDetails_AllList;
        }
    }



My controller method
[AllowAnonymous]
[HttpPost]
public async Task<IActionResult> GetDataHere()
{
    var ItemsHere = await normalDeposit_DepositDetailsInterface.GetAllNormalDeposit_DepositDetails_Async();
    decimal NormalDeposit_Deposit_AfterDeposit = ItemsHere.Where(a => a.Status == true ).Sum(s => s.Amount);

    return View("Index");
}


App.json method
"ConnectionStrings": {
   "ConnectionVar": "Server=DESKTOP-IACJQY;Database=$aZ1*,z.ahbz.@!78z)c;User Id=*&zkaUI190%$User{]z!Z,a;password=+1z)9.,!aEL%ZKI9@$MnaL><f;Trusted_Connection=False;MultipleActiveResultSets=true;Connection Timeout=1800;"
 }


Startup.cs

services.AddDistributedSqlServerCache(options =>
        {
            options.ConnectionString = _config.GetConnectionString("ConnectionVar");
            options.SchemaName = "dbo";
            options.TableName = "SQLSessions";
            options.DefaultSlidingExpiration = TimeSpan.FromMinutes(30);
            options.ExpiredItemsDeletionInterval = TimeSpan.FromMinutes(30);
        });


My problems:-

1) {"Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding."} in distributed sql server cache

When debug hits the line await distributedCache.SetStringAsync(KeyHere, Value, options); . the record is more then 3000 rows.

Please help if i am missing something. or need to be added in code. Thanks in advance for support and concern.

What I have tried:

I have tried Async method to wait it it complete, but still it appear have same problem. I have increase the connection timeout but nothing works.

Connection Timeout=1800;
Posted
Updated 12-Oct-22 23:59pm

Connection Timeout controls how long the code waits to establish the initial connection to SQL. Your error message seems to be a timeout executing a SQL query, which would be controlled by the CommandTimeout on the DbCommand you're executing.

You'll need to debug your code to see which command is timing out, and then find a way to increase the command timeout for it.
  • For Entity Framework 6, set the context.Database.CommandTimeout property.
  • For Entity Framework Core, use the context.Database.SetCommandTimeout extension method, or set the options in the UseSqlServer method for the DbContextOptionsBuilder.
  • For your distributed cache, it will depend on which framework you're using.

Better yet, once you know the query that's timing out, try to optimise it to reduce the length of time it takes to execute.
 
Share this answer
 
Just to expand on Richard's answer - you can put the command timeout in the connection string in the same way that you were putting the connection timeout in, so this is a valid connection string (with the latest EF Core 6 packages):

Server=MyServer;Database=MyDatabase;Trusted_Connection=True;Command Timeout=600;App=MyApp


.. this is required as it appears to be impossible to get a reference to the DbContext used by IDistributedCache in order to affect its CommandTimeout property.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900