Failed executing DbCommand because timeout expired .net core

Question

My goal is to provide simple API to retrieve data from Payments (~400 rows) table which consists 5 columns.

Payment: Id (int),
PaymentsNumber (tinyint),
Rate (decimal(18,2)),
ProductType (tinyint),
ClientClubType (tinyint).

Users can make posts request with with the request params of (should return ~12 rows):

PaymentsRequest 
{
    public int? PaymentsNumber { get; set; }
    public byte? ProductType { get; set; }
    public byte? ClientClubType { get; set; }
}

Using EF-Core:

services.AddDbContext<MyContext>(cfg => cfg.UseSqlServer(Configuration.GetConnectionString(...),optionsBuilder => optionsBuilder.CommandTimeout(60)));

public async Task<IEnumerable<Payments>> GetPaymentsAsync(PaymentsRequest request)
{
    IQueryable<Payments> query = this._context.Set<Payments>();
    query = query.Where(filter => 
                        (request.ClientClubType == null || filter.ClientClubType == request.ClientClubType) &&
                        (request.ProductType == null || filter.ProductType == request.ProductType) &&
                        (request.PaymentsNumber == null || filter.PaymentsNumber == request.PaymentsNumber));

    return await query.ToListAsync();
}

On azure application insights I can see 2 consecutive logs, caused by the same exception:

Log1: Failed executing DbCommand.
Log2: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

The Log1 is (while there is no need to write here the log2):

Failed executing DbCommand (65,238ms) [Parameters=[@__request_ClientClubType_0='?' (Size = 1) (DbType = Byte), @__request_ProductType_1='?' (Size = 1) (DbType = Byte)], CommandType='Text', CommandTimeout='60']

SELECT [p].[Id], [p].[ClientClubType], [p].[PaymentsNumber], [p].[ProductType], [p].[Rate] FROM [Payments] AS [p] WHERE (([p].[ClientClubType] = @__request_ClientClubType_0) AND @__request_ClientClubType_0 IS NOT NULL) AND (([p].[ProductType] = @__request_ProductType_1) AND @__request_ProductType_1 IS NOT NULL)

My application is a .net core 3.0 application deployed on azure linux webapp.

The issue only occurs from production not every time and I can not reconstruct the issue from MSSMS. Any idea?

UPDATE:

After @panagiotis-kanavos commented, I've updated my code to:

services.AddDbContextPool<MyContext>(cfg => cfg.UseSqlServer(Configuration.GetConnectionString(...),optionsBuilder => optionsBuilder.CommandTimeout(60)));

public async Task<IEnumerable<Payments>> GetPaymentsAsync(PaymentsRequest request)
{
    IQueryable<Payments> query = this._context.Payments;
    query = query.Where(filter => 
                        (filter.ClientClubType == request.ClientClubType) &&
                        (filter.ProductType == request.ProductType) &&
                        (filter.PaymentsNumber == request.PaymentsNumber));

    return await query.ToListAsync();
}

As for why this takes so long, it could be missing indexes, concurrency conflicts with other queries, problems with the query itself (catch-all queries are bad and not needed at all with LINQ). Most likely, the query locks too many rows because of those NULLs, causing conflicts with other queries that try to touch the same table. Perhaps a long-running transaction too? — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jan 30, 2020 at 11:06
You can add conditions dynamically with if(someParam!=null){ query=query.Where(r=>r.field==param);}. This sequence of Wheres is equivalent to an AND. If you don't need a parameter, just don't add the condition — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jan 30, 2020 at 11:07
That table scan now, takes S(hared) locks on all rows. That means that any query that wants to update, has to wait for the other transaction to complete. If your code opens and holds a transaction explicitly (which it shouldn't do with any ORM), the locks may remain for a long time. This increases conflicts and blocking a lot — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jan 30, 2020 at 11:11
If you use database transactions to implement "Business transactions per request" or Unit-Of-Work, which you don't need to, your queries end up blocking each other. You don't need to because ORMs cache changes and only persist them atomically, in a single batch, when a call to SaveChanges is made. You already have a UoW, you don't need DB transactions to implement it (which can't do so anyway) — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jan 30, 2020 at 11:13
can you install sp_whoisactive and check how many transactions you've got while your query runs? — timur
– timur, Commented Feb 12, 2020 at 9:42

snj · Accepted Answer · 2021-11-04 23:06:53Z

2

Your timeout is 60 seconds this could be increased. It's usually not a good idea to do this as it will hide other issues.
If the table has lots of writes, or something has a long running transaction, it can block/contend with your query.
If you query is part of a larger begin transaction - end transaction sequence of SQL operations, it may be blocked by other open transactions
Same as last one - If multiple calls are made at the same time/nearly the same time to this query it could slow down processing of each query. I've seen this where a web front end populates a screen of 20 lines of data where each line is an different call to the same web-api endpoint for the same type of data. For example, getting the monthly transaction dollar total for each of the last 12 months.

answered Nov 4, 2021 at 23:06

snj

1001 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Failed executing DbCommand because timeout expired .net core

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related