C#•11mo ago

"Execution Timeout Expired. The timeout period elapsed prior to completion of the operation"

Im have a API to get all clients but the information need to fetch is needed some navigational properties. I use SingleQuery, SplitQuery and AsNoTracking. But I'm getting Execution Timeout Expired". Any suggestion and advice are well much appreciated! https://paste.mod.gg/otgeydlycylz/0

BlazeBin - otgeydlycylz

A tool for sharing your source code with the world!

24 Replies

Pobiega•11mo ago

Snippet from the pastebin:

var regularClients = _context.Clients
                .AsSplitQuery()
                .Include(mop => mop.ClientModeOfPayment)
                .AsSplitQuery()
                .Include(abu => abu.AddedByUser)
                .AsSplitQuery()
                .Include(rq => rq.Request)
                .ThenInclude(user => user.Requestor)
                .AsSplitQuery()
                .Include(rq => rq.Request)
                .ThenInclude(ah => ah.UpdateRequestTrails)
                .AsSplitQuery()
                .Include(rq => rq.Request)
                .ThenInclude(ap => ap.Approvals)
                .ThenInclude(cap => cap.Approver)
                .AsSplitQuery()
                .Include(st => st.StoreType)
                .AsSplitQuery()
                .Include(fd => fd.FixedDiscounts)
                .AsSplitQuery()
                .Include(to => to.Term)
                .ThenInclude(tt => tt.Terms)
                .AsSplitQuery()
                .Include(to => to.Term)
                .ThenInclude(td => td.TermDays)
                .AsSplitQuery()
                .Include(ba => ba.BusinessAddress)
                .AsSplitQuery()
                .Include(oa => oa.OwnersAddress)
                .AsSplitQuery()
                .Include(bc => bc.BookingCoverages)
                .AsSplitQuery()
                .Include(fr => fr.FreebiesRequests)
                .ThenInclude(fi => fi.FreebieItems)
                .ThenInclude(item => item.Items)
                .ThenInclude(uom => uom.Uom)
                .AsSplitQuery()
                .Include(lf => lf.ListingFees)
                .ThenInclude(li => li.ListingFeeItems)
                .ThenInclude(item => item.Item)
                .ThenInclude(uom => uom.Uom)
                .AsSplitQuery()
                .Include(cd => cd.ClientDocuments)
                .AsSingleQuery()
                .AsNoTracking();

var regularClients = _context.Clients
                .AsSplitQuery()
                .Include(mop => mop.ClientModeOfPayment)
                .AsSplitQuery()
                .Include(abu => abu.AddedByUser)
                .AsSplitQuery()
                .Include(rq => rq.Request)
                .ThenInclude(user => user.Requestor)
                .AsSplitQuery()
                .Include(rq => rq.Request)
                .ThenInclude(ah => ah.UpdateRequestTrails)
                .AsSplitQuery()
                .Include(rq => rq.Request)
                .ThenInclude(ap => ap.Approvals)
                .ThenInclude(cap => cap.Approver)
                .AsSplitQuery()
                .Include(st => st.StoreType)
                .AsSplitQuery()
                .Include(fd => fd.FixedDiscounts)
                .AsSplitQuery()
                .Include(to => to.Term)
                .ThenInclude(tt => tt.Terms)
                .AsSplitQuery()
                .Include(to => to.Term)
                .ThenInclude(td => td.TermDays)
                .AsSplitQuery()
                .Include(ba => ba.BusinessAddress)
                .AsSplitQuery()
                .Include(oa => oa.OwnersAddress)
                .AsSplitQuery()
                .Include(bc => bc.BookingCoverages)
                .AsSplitQuery()
                .Include(fr => fr.FreebiesRequests)
                .ThenInclude(fi => fi.FreebieItems)
                .ThenInclude(item => item.Items)
                .ThenInclude(uom => uom.Uom)
                .AsSplitQuery()
                .Include(lf => lf.ListingFees)
                .ThenInclude(li => li.ListingFeeItems)
                .ThenInclude(item => item.Item)
                .ThenInclude(uom => uom.Uom)
                .AsSplitQuery()
                .Include(cd => cd.ClientDocuments)
                .AsSingleQuery()
                .AsNoTracking();

I'm not surprised this times out 😛

_vegabyte_OP•11mo ago

Hahaha. I need all the data for that. Like all data is needed. Is there any way or other approach?

Pobiega•11mo ago

I don't doubt it. But it tells me someone failed hard when designing your database. Thats 27 relational navigations in a single query Its an insane amount. Anything above 5 should be a red flag.

_vegabyte_OP•11mo ago

I'll try to create a diagram for this one. For more information about my database.

Unknown User•11mo ago

Message Not Public

Pobiega•11mo ago

have you generated the execution plan for this query?

_vegabyte_OP•11mo ago

It's like I'm getting all the information in a single query. Like Approvals, Request, Items and other information.

Pobiega•11mo ago

that should be your first step when "debugging" a query

Unknown User•11mo ago

Message Not Public

_vegabyte_OP•11mo ago

I don't have excution plans for this query.

Pobiega•11mo ago

Then go do that 🙂 have EF dump the SQL query, take that query to SSMS or similar, generate the execution plan

_vegabyte_OP•11mo ago

The query is filtered based on the role of the User then other condition will apply based the requirements supplied on the request. But since this is a client all the information will be fetch. I will follow your instructions. I will use Entity Framework to generate the SQL query and then take that query to SQL Server Management Studio (SSMS) analyze the execution plan. If I encounter any issues or have further questions, I'll reach out to you for guidance.

Pobiega•11mo ago

Im assuming this is for work. Do you have a db admin or senior dev or anyone else to talk to about this?

_vegabyte_OP•11mo ago

Currently we don't have any db admin. All the works for the system is done by only two devs.

Pobiega•11mo ago

If I had code that looked like this, I'd probably call an all-hands type meeting to discuss the looming catastrophy and discuss a database redesign

Unknown User•11mo ago

Message Not Public

_vegabyte_OP•11mo ago

The database design is just created by us because we don't have anyone to design it.

_vegabyte_OP•11mo ago

This query is already on a pagination and has filter based on requirements.

_vegabyte_OP•11mo ago

The whole query has all the information here.

_vegabyte_OP•11mo ago

Is there any way to get those information without compromising the performance?

Unknown User•11mo ago

Message Not Public

_vegabyte_OP•11mo ago

so In the same query I need a filter based on tab?

Unknown User•11mo ago

Message Not Public

_vegabyte_OP•11mo ago

No, I can retrieve the data without the other data from tab 1. But they are all related Question. I have only one DTO for this query. Is it okay to leave the other data null, or should i only return what is needed?

Gaming

Programming

"Execution Timeout Expired. The timeout period elapsed prior to completion of the operation"