Disposing objects in memory in each iteration

Question

I am writing an enhancement to an existing webjob that takes records from one table (outer ring) and pushes it to another (inner ring). There are about 75 million records in the outer ring table and I want to be very selective about the data that I want to push to the inner ring table. I am using a stored procedure to take the data, page it and return it back to be pushed into the inner ring.

    public async Task ExecuteAsync(
        AMS360DbContext outerRingDb
        , MiddleTierCoreDbContext innerRingDb
        , SyncSupportDbContext syncDb
        , ILogger logger
        , CancellationToken cancellationToken)
    {
        var log = logger.CoverageSync(this);

        TelemetryClient telemetry = new TelemetryClient();
        log.SyncStarted("Coverages");
        var sw = Stopwatch.StartNew();

        var transformer = new AmsPolicyCoveragesTransformer();


        const string innerRingTable = InnerRingTables.PolicyCoverages;
        const string sourceSchema = "ams360";
        const string sourceStoredProcedure = "GetPolicyCoverages";

        try
        {
            const int pageSize = 50_000;
            var page = 0;
            bool hasMoreRecords;

            var rowVersion = await syncDb.GetInnerRingRowVersionAsync(innerRingTable, sourceSchema, sourceStoredProcedure);

            do
            {
                if (cancellationToken.IsCancellationRequested)
                    return;

                var index = page * pageSize;

                log.SyncInProgress(index, index + pageSize - 1);

                var rowVersionParam = new SqlParameter()
                {
                    ParameterName = "@RowVersion",
                    SqlDbType = SqlDbType.Timestamp,
                    Direction = ParameterDirection.Input,
                    Value = rowVersion != null ? BitConverter.GetBytes(Convert.ToUInt64(rowVersion)).Reverse().ToArray() : (object)DBNull.Value
                };

                var prms = new SqlParameter[]
                {
                    new SqlParameter("@PageStart", index),
                    new SqlParameter("@PageSize", pageSize),
                    rowVersionParam
                };

                var outerRingCoverages = await outerRingDb.Set<SpPolicyCoverages>()
                                                    .FromSqlRaw("EXEC ams360.GetPolicyCoverages @PageStart, @PageSize, @RowVersion", prms)
                                                    .ToListAsync(cancellationToken);

                page++;

                var transformed = transformer.Transform(outerRingCoverages).ToList();

                if (transformed.Any())
                {
                    await MergeToInnerRingAsync(innerRingDb, transformed, cancellationToken);

                    var latestVersion = outerRingCoverages.Max(x => x.RowVersion);

                    telemetry.TrackEvent("Inner Ring Sync - Coverages Success");

                    await syncDb.UpdateInnerRingSyncRowVersionAsync(innerRingTable, sourceSchema, sourceStoredProcedure, latestVersion.Value, cancellationToken);

                }

                hasMoreRecords = (outerRingCoverages.Count == pageSize);

            } while (hasMoreRecords);
        }
        catch (Exception ex)
        {
            log.SyncError("Coverages", ex);
            telemetry.TrackEvent("Inner Ring Sync - Coverages Error");
            throw;
        }

        log.SyncFinished("Coverages", sw.Elapsed);


    }

The problem that I am having with this code is that at some point during the iteration, I am running into an OutofMemory exception. So I was looking at the Diagnostic tools and I am surprised to see that when I get 50000 records from the table for SpPolicyCoverages, there are 50,000 objects created in heap in memory and in the next iteration, there are 100,000 objects created in memory and as this continues accumulating in the memory until it runs into the OutofMemory exception. What is the best way to dispose the object(s) (SpPolicyCoverages) in each iteration such that i don't run into an OutofMemory exception? Please advise..Thanks in advance.

Answer 1

Is SpPolicyCoverages an Entity Type? If so this might be an issue because ChangeTracking isn't disabled on the database context. Change tracking isn't any different just because you're using FromSqlRaw. Try disabling Change Tracking on the context or use .AsNoTracking():

var outerRingCoverages = await outerRingDb.Set<SpPolicyCoverages>()
    .FromSqlRaw("EXEC ams360.GetPolicyCoverages @PageStart, @PageSize, @RowVersion", prms)
    .AsNoTracking() // <--
    .ToListAsync(cancellationToken);