ASP, EF with Relations: Cannot access API after deploy due to more data causing timeout error-CodePudding

The API has no issue at the beginning, it was working fine. But when more and more data was accumulated in the database (mariadb), the event participants cannot get their workshop data, such as their attendance records, their history sessions, the API fails.

I tried to solve but did not find a solution, how to can I solve this timeout issue?

[HttpGet]
[Authorize(Roles = "Admin,Participant")]
[ValidateReferrerAttribute]
public async Task<ParticipantSessionDto> GetSessions()
{
    if (User.Identity is { IsAuthenticated: true })
    {
        var configuration = new MapperConfiguration(
            cfg => cfg.CreateProjection<Participant, ParticipantSessionDto>());

        return await _entities
            .AsNoTracking()
            .Include(x => x.CheckIns)
                .ThenInclude(x => x.Session)
            .Include(x => x.Events)
            .Include(x => x.Sessions)
                .ThenInclude(x => x.Event)
            .Include(x => x.Sessions)
                .ThenInclude(x => x.Speakers)
                .ThenInclude(x => x.Avatar)
            .Include(x => x.Sessions)
                .ThenInclude(x => x.Materials)
            .ProjectTo<ParticipantSessionDto>(configuration)
            .FirstAsync(x => x.UserName == User.Identity.Name);
    }

    return null;
}

The exception log

MySqlConnector.MySqlException (0x80004005): The Command Timeout expired before the operation completed.
     ---> System.Net.Sockets.SocketException (125): Operation canceled
       at System.Net.Sockets.Socket.AwaitableSocketAsyncEventArgs.ThrowException(SocketError error, CancellationToken cancellationToken)
       at System.Net.Sockets.Socket.AwaitableSocketAsyncEventArgs.System.Threading.Tasks.Sources.IValueTaskSource<System.Int32>.GetResult(Int16 token)
       at MySqlConnector.Protocol.Serialization.SocketByteHandler.DoReadBytesAsync(Memory`1 buffer) in /_/src/MySqlConnector/Protocol/Serialization/SocketByteHandler.cs:line 109
       at MySqlConnector.Protocol.Serialization.SocketByteHandler.DoReadBytesAsync(Memory`1 buffer) in /_/src/MySqlConnector/Protocol/Serialization/SocketByteHandler.cs:line 109
       at MySqlConnector.Protocol.Serialization.BufferedByteReader.ReadBytesAsync(IByteHandler byteHandler, ArraySegment`1 buffer, Int32 totalBytesToRead, IOBehavior ioBehavior) in /_/src/MySqlConnector/Protocol/Serialization/BufferedByteReader.cs:line 34
       at MySqlConnector.Protocol.Serialization.ProtocolUtility.<ReadPacketAfterHeader>g__AddContinuation|2_0(ValueTask`1 payloadBytesTask, Int32 payloadLength, ProtocolErrorBehavior protocolErrorBehavior, Exception packetOutOfOrderException) in /_/src/MySqlConnector/Protocol/Serialization/ProtocolUtility.cs:line 434
       at MySqlConnector.Protocol.Serialization.ProtocolUtility.<DoReadPayloadAsync>g__AddContinuation|5_0(ValueTask`1 readPacketTask, BufferedByteReader bufferedByteReader, IByteHandler byteHandler, Func`1 getNextSequenceNumber, ArraySegmentHolder`1 previousPayloads, ProtocolErrorBehavior protocolErrorBehavior, IOBehavior ioBehavior) in /_/src/MySqlConnector/Protocol/Serialization/ProtocolUtility.cs:line 480
       at MySqlConnector.Core.ServerSession.ReceiveReplyAsyncAwaited(ValueTask`1 task) in /_/src/MySqlConnector/Core/ServerSession.cs:line 956
       at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in /_/src/MySqlConnector/Core/ResultSet.cs:line 133
       at MySqlConnector.MySqlDataReader.ActivateResultSet(CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 108
       at MySqlConnector.MySqlDataReader.CreateAsync(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, IDictionary`2 cachedProcedures, IMySqlCommand command, CommandBehavior behavior, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 456
       at MySqlConnector.Core.CommandExecutor.ExecuteReaderAsync(IReadOnlyList`1 commands, ICommandPayloadCreator payloadCreator, CommandBehavior behavior, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/CommandExecutor.cs:line 56
       at MySqlConnector.MySqlCommand.ExecuteReaderAsync(CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlCommand.cs:line 331
       at MySqlConnector.MySqlCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlCommand.cs:line 323
       at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
       at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
       at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)
       at Pomelo.EntityFrameworkCore.MySql.Storage.Internal.MySqlExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
       at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
       at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.SingleAsync[TSource](IAsyncEnumerable`1 asyncEnumerable, CancellationToken cancellationToken)
       at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.SingleAsync[TSource](IAsyncEnumerable`1 asyncEnumerable, CancellationToken cancellationToken)
       at EventManager.Server.Controllers.ParticipantController.GetSessions() in C:\Users\CMSB\Desktop\EventManager\EventManager.Server\Controllers\ParticipantController.cs:line 173

Edit: I also have an issue querying the dashboard API. It used to work at the beginning, but then when I deploy the server, after a while, now even the dasboard I cannot retrieve.

This is the Get request for dashboard:

[HttpGet]
[Authorize(Roles = "Admin")]
[ValidateReferrerAttribute]
public async Task<Event> GetForDashboard(Guid id)
{
    if (await _entities.AnyAsync(x => x.EventId == id))
    {
        var data = await _entities
            .AsNoTracking()
            .Include(x => x.CheckIns)
                .ThenInclude(x => x.Participant)
            .Include(x => x.Participants)
                .ThenInclude(x => x.Feedbacks)
            .Include(x => x.Sessions)
                .ThenInclude(x => x.Speakers)
            .Include(x => x.Sessions)
                .ThenInclude(x => x.Participants)
            .Include(x => x.Sessions)
                .ThenInclude(x => x.CheckIns)
                .ThenInclude(x => x.Participant)
            .FirstAsync(x => x.EventId == id);

        return data;
    }

    return null;
}

CodePudding user response：

Maybe you can try this. Execute to get the user first (to get the user Id). the point here is to use the Id to query. By convention, the Id column is often indexed, so query by the Id has a better performance. It should solve the time-out issue

var user = _entities
            .FirstOrDefaultAsync(x => x.UserName == User.Identity.Name);

if(user != null) 
{
   return await _entities
            .AsNoTracking()
            .Include(x => x.CheckIns)
                .ThenInclude(x => x.Session)
            .Include(x => x.Events)
            .Include(x => x.Sessions)
                .ThenInclude(x => x.Event)
            .Include(x => x.Sessions)
                .ThenInclude(x => x.Speakers)
                .ThenInclude(x => x.Avatar)
            .Include(x => x.Sessions)
                .ThenInclude(x => x.Materials)
            .ProjectTo<ParticipantSessionDto>(configuration)
            .FirstAsync(x => x.Id == user.Id);
}

Updated to optimize the Include query Maybe the problems come from several Include expressions. You can optimized the query by using the Entity Framework Core Plus Include Optimize

return await _entities
            .AsNoTracking()
            .IncludeOptimized(x => x.CheckIns)
            .IncludeOptimized(x => x.CheckIns.Select(y => y.Session))
            .IncludeOptimized(x => x.Events)
            .IncludeOptimized(x => x.Sessions)
            .IncludeOptimized(x => x.Sessions.Select(y => y.Event))
            // And so on
            .ProjectTo<ParticipantSessionDto>(configuration)
            .FirstAsync(x => x.Id == user.Id);

CodePudding user response：

When including multiple collections you can try using .AsSplitQuery() to improve performance. Documentation. As always, profile to see if it really improves your case..