Setting fetch size in entity framework

Question

I am converting ado.net code to use EF. In my ado.net code i set dataReader.FetchSize = command.RowSize * 1000 and that dramatically improves performance over the default fetch size . When I convert my code to EF, the performance is on par to ado.net code where I didn't specify fetch size, i.e. it's very slow over large records.

Any way I could specify fetch size for retrieving records in EF?

Answer 1

You can set ODP.NET FetchSize in the Registry or the .NET config files when using Entity Framework. That will standardize the FetchSize across all your ODP.NET instances (in the case of the Registry) or across your application (in the case of app/web.config).

http://docs.oracle.com/cd/E48297_01/doc/win.121/e41125/featConfig.htm

Christian Shay

Oracle

Answer 2

I was running into a similar problem, but don't want to change the overall FetchSize, instead I want to change the FetchSize per query.

Here is the solution I came up with, maybe this helps someone. It basically uses the CallContext to pass arguments to a DbInterceptor. The interceptor will override the needed properties on the query commands.

Thread safe with support for nesting scopes.

This can be as well used to modify other properties of commands executed through Entity Framework queries for a defined scope.

Usage:

using (var context = new MyDbContext())
{
    using (new OracleCommandContext(fetchSize: 1024 * 128))
    {
        // your query here
    }
}

Properties to override:

public class OracleCommandProperties
{
    public long FetchSize { get; set; } = 524288; // oracle default value
}

The call context:

public class OracleCommandContext : IDisposable
{
    private static readonly object sync = new object();
    private readonly OracleCommandProperties previousCommandProperties;

    private bool isDisposed;

    static OracleCommandContext()
    {
        DbInterception.Add(new OracleCommandInterceptor());
    }

    public OracleCommandContext(long fetchSize)
    {
        lock (sync)
        {
            var commandProperties = new OracleCommandProperties();

            if (TryGetProperties(out var previousProperties))
            {
                // when using nested OracleCommandContext, escalate the properties
                previousCommandProperties = previousProperties;
                commandProperties.FetchSize = Math.Max(previousProperties.FetchSize, fetchSize);
            }
            else
            {
                commandProperties.FetchSize = fetchSize;
            }

            CallContext.LogicalSetData(nameof(OracleCommandProperties), commandProperties);
        }
    }

    public void Dispose()
    {
        Dispose(true);
        GC.SuppressFinalize(this);
    }

    ~OracleCommandContext()
    {
        Dispose(false);
    }

    private void Dispose(bool disposing)
    {
        if (disposing)
        {
            if (!isDisposed)
            {
                lock (sync)
                {
                    CallContext.LogicalSetData(nameof(OracleCommandProperties), previousCommandProperties);
                }

                isDisposed = true;
            }
        }
    }

    public static bool TryGetProperties(out OracleCommandProperties properties)
    {
        lock(sync)
        {
            if (CallContext.LogicalGetData(nameof(OracleCommandProperties)) is OracleCommandProperties oracleReaderProperties)
            {
                properties = oracleReaderProperties;
                return true;
            }

            properties = null;
            return false;
        }
    }
}

The interceptor doing the actual work:

public class OracleCommandInterceptor : IDbCommandInterceptor
{
    public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
    }

    public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
        AdjustCommand(command);
    }

    public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
    }

    public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
        AdjustCommand(command);
    }

    public void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    {
    }

    public void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    {
        AdjustCommand(command);
    }

    private static void AdjustCommand(DbCommand command)
    {
        if (command is OracleCommand oracleCommand)
        {
            if (OracleCommandContext.TryGetProperties(out var properties))
            {
                oracleCommand.FetchSize = properties.FetchSize;
            }
        }
    }
}