La query con una clausola WHERE di grandi dimensioni causa l'eccezione di timeout in EF6 con npgsql

Question

La query con una clausola WHERE di grandi dimensioni causa l'eccezione di timeout in EF6 con npgsql

c# entity-framework entity-framework-6 npgsql

Domanda

Ho una query che assomiglia a qualcosa del genere:

private static IQueryable<MultiframeModule> WhereAllFramesProperties(this IQueryable<MultiframeModule> query, ICollection<Frame> frames)
{
    return frames.Aggregate(query, (q, frame) =>
    {
        return q.Where(p => p.Frames.Any(i => i.FrameData.ShaHash == frame.FrameData.ShaHash));
    });
}

MultiframeModule e Frame hanno una relazione molti-a-molti.

Con quella query voglio trovare un MultiframeModule che contenga tutti i frame all'interno della collezione di frames ho inviato come parametro, per questo controllo il parametro ShaHash .

Se i frames contengono 2 frame, allora l'SQL generato sarebbe qualcosa del genere:

SELECT
   "Extent1"."MultiframeModuleId",
   "Extent1"."FrameIncrementPointer",
   "Extent1"."PageNumberVector" 
FROM
   "public"."MultiframeModule" AS "Extent1" 
WHERE
   EXISTS 
   (
      SELECT
         1 AS "C1" 
      FROM
         "public"."Frame" AS "Extent2" 
         INNER JOIN
            "public"."FrameData" AS "Extent3" 
            ON "Extent2"."FrameData_FrameDataId" = "Extent3"."FrameDataId" 
      WHERE
         "Extent1"."MultiframeModuleId" = "Extent2"."MultiframeModule_MultiframeModuleId" 
         AND "Extent3"."ShaHash" = @p__linq__0
   )
   AND EXISTS 
   (
      SELECT
         1 AS "C1" 
      FROM
         "public"."Frame" AS "Extent4" 
         INNER JOIN
            "public"."FrameData" AS "Extent5" 
            ON "Extent4"."FrameData_FrameDataId" = "Extent5"."FrameDataId" 
      WHERE
         "Extent1"."MultiframeModuleId" = "Extent4"."MultiframeModule_MultiframeModuleId" 
         AND "Extent5"."ShaHash" = @p__linq__1
   )
   LIMIT 2

-- p__linq__0: '0' (Type = Int32, IsNullable = false)

-- p__linq__1: '0' (Type = Int32, IsNullable = false)

Ma, se ho più frame, ad esempio 200, la chiamata genererebbe un'eccezione:

Unable to read data from the transport connection: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.

Con stacktrace:

   at Npgsql.ReadBuffer.<Ensure>d__27.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Npgsql.NpgsqlConnector.<DoReadMessage>d__157.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.ValueTaskAwaiter`1.GetResult()
   at Npgsql.NpgsqlConnector.<ReadMessage>d__156.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.ValueTaskAwaiter`1.GetResult()
   at Npgsql.NpgsqlConnector.<ReadExpecting>d__163`1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.ValueTaskAwaiter`1.GetResult()
   at Npgsql.NpgsqlDataReader.<NextResult>d__32.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Npgsql.NpgsqlDataReader.NextResult()
   at Npgsql.NpgsqlCommand.<Execute>d__71.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.ValueTaskAwaiter`1.GetResult()
   at Npgsql.NpgsqlCommand.<ExecuteDbDataReader>d__92.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.ValueTaskAwaiter`1.GetResult()
   at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
   at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext)
   at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)

Quindi, c'è qualche ovvia ragione per cui la mia query sta fallendo? E come posso migliorarlo per poter eseguire correttamente la query?

Sassa

Risposta popolare

Hai una clausola any all'interno di un where , forse dovresti provare ad ottimizzarlo

return frames.Aggregate(query, (q, frame) =>
        {
            return q.Frames.Any(i => i.FrameData.ShaHash == frame.FrameData.ShaHash));
        });

MrMins

Vedi di più su Stack Overflow

Ivan Stoev · Accepted Answer · 4/22/2018 2:32:29 PM

Per quanto posso dire, il problema è causato da troppe subquery nella query SQL generata.

Nel mio ambiente di test, SqlServer (LocalDB) si rifiuta semplicemente di eseguire la query generata con la ragione come troppo complessa . PostgreSQL è stato in grado di eseguirlo (dopo aver impostato CommandTimeout su 0) in ~ 4min.

La soluzione è trovare un costrutto equivalente che non generi molte subquery. Quello che di solito uso in questo caso nel conteggio delle partite distinte e confrontandolo con l' approccio di conteggio dei criteri .

Può essere implementato in due modi.

(1) Questo è applicabile solo per condizioni di tipo property == valueN . In tal caso, il conteggio delle corrispondenze distinte può essere fatto in questo modo (in pseudo codice):

obj.Collection
   .Select(elem => elem.Property)
   .Distinct()
   .Count(value => values.Contains(values))

Applicandolo al tuo campione:

private static IQueryable<MultiframeModule> WhereAllFramesProperties(this IQueryable<MultiframeModule> query, ICollection<Frame> frames)
{
    var values = frames.Select(e => e.FrameData.ShaHash);
    var count = frames.Count();
    return query.Where(p => p.Frames.Select(e => e.FrameData.ShaHash)
        .Distinct().Count(v => values.Contains(v)) == count);
}

(2) Questo è applicabile per qualsiasi tipo di condizione. In questo caso la corrispondenza è identificata dal suo indice, che richiede di costruire dinamicamente un'espressione selettore come questa:

Condition0 ? 0 : Condition1 ? 1 : ... ConditionN-1 ? N - 1 : -1

e il conteggio delle partite distinto sarebbe

obj.Collection
   .Select(selector)
   .Distinct()
   .Count(i => i >= 0)

Applicandolo al tuo campione:

private static IQueryable<MultiframeModule> WhereAllFramesProperties(this IQueryable<MultiframeModule> query, ICollection<Frame> frames)
{
    var parameter = Expression.Parameter(typeof(MultiframeModuleFrame), "e");
    var body = frames.Select((frame, index) =>
    {
        Expression<Func<Frame, bool>> predicate = e => e.FrameData.ShaHash == frame.FrameData.ShaHash;
        return new
        {
            Condition = predicate.Body.ReplaceParameter(predicate.Parameters[0], parameter),
            Value = Expression.Constant(index)
        };
    })
    .Reverse()
    .Aggregate((Expression)Expression.Constant(-1), (next, item) =>
        Expression.Condition(item.Condition, item.Value, next));
    var selector = Expression.Lambda<Func<Frame, int>>(body, parameter);
    var count = frames.Count();
    return query.Where(p => p.Frames.AsQueryable().Select(selector)
        .Distinct().Count(i => i >= 0) == count);
}

dove ReplaceParameter è il seguente metodo di estensione personalizzato:

public static partial class ExpressionUtils
{
    public static Expression ReplaceParameter(this Expression expression, ParameterExpression source, Expression target)
    {
        return new ParameterReplacer { Source = source, Target = target }.Visit(expression);
    }

    class ParameterReplacer : ExpressionVisitor
    {
        public ParameterExpression Source;
        public Expression Target;
        protected override Expression VisitParameter(ParameterExpression node)
        {
            return node == Source ? Target : base.VisitParameter(node);
        }
    }
}

L'SQL generato contiene un'enorme espressione CASE WHEN (sfortunatamente raddoppiata nella clausola WHERE ), ma una singola sottoquery, ed è accettata ed eseguita con successo sia in SqlServer che in PostgreSQL (nel caso successivo in meno ~ 2 sec nelle stesse condizioni del test originale - record 1K in entrambe le tabelle, collegamenti 1M, 200 condizioni).

La query con una clausola WHERE di grandi dimensioni causa l'eccezione di timeout in EF6 con npgsql

Domanda

Fastest Entity Framework Extensions

Risposta accettata

Risposta popolare

Performance

Expression Evaluator

Related