Linq to sql parallel for submit changes

Question

Given an entity List, of updated objects, is it safe to instantiate a new context per iteration in a Parallel.For or foreach loop, and call SubmitChanges() on every of (let's say) 10 000 iterations?

Is it safe performing bulk updates this way? What are the possible drawbacks?

Answer 1

This may be a scenerio where parallelism should be avoided. Instantiating a new DataContext per an iteration would mean that within the iteration a connection would be acquired from the connection pool, opened and a single entity written to the database before returning the connection to pool. Do this every iteration is a comparitively expensive operation so the generating a overhead that outweighs the advantages of parallelism. Where as adding entities to the data context and writing them to the database as a single action is more efficent.

Using the following as a benchmark for the Parallel insertions:

private static TimeSpan RunInParallel(int inserts)
{
    Stopwatch watch = new Stopwatch();
    watch.Start();

    Parallel.For(0, inserts, new ParallelOptions() { MaxDegreeOfParallelism = 100 },
        (i) =>
        {
            using (var context = new DataClasses1DataContext())
            {
                context.Tables.InsertOnSubmit(new Table() { Number = i });
                context.SubmitChanges();
            }
        }
    );

    watch.Stop();
    return watch.Elapsed;
}

For serial insertions:

private static TimeSpan RunInSerial(int inserts)
{
    Stopwatch watch = new Stopwatch();
    watch.Start();
    using (var ctx = new DataClasses1DataContext())
    {
        for (int i = 0; i < inserts; i++)
        {
            ctx.Tables.InsertOnSubmit(new Table() { Number = i });
        }
        ctx.SubmitChanges();
    }
    watch.Stop();
    return watch.Elapsed;
}

Where the DataClasses1DataContext classes are an automatically generated DataContext for:

When run on a first generation Intel i7 (8 logical cores) the following results were obtained:

10 inserts:
Average time elapsed for a 100 runs in parallel: 00:00:00.0202820
Average time elapsed for a 100 runs in serial:   00:00:00.0108694

100 inserts:
Average time elapsed for a 100 runs in parallel: 00:00:00.2269799
Average time elapsed for a 100 runs in serial:   00:00:00.1434693

1000 inserts:
Average time elapsed for a 100 runs in parallel: 00:00:02.1647577
Average time elapsed for a 100 runs in serial:   00:00:00.8163786

10000 inserts:
Average time elapsed for a 10 runs in parallel:  00:00:22.7436584
Average time elapsed for a 10 runs in serial:    00:00:07.7273398

In general, when run in parallel the insertions take approximately twice as long to execute as when run without parallelism.

UPDATE: If you can implement some batching scheme for the data, it might be beneficial to use parallel insertions.

When using batches, the size of the batch will affect the insertion performance so some optimal ratio between the number of entries per batch and number of batches inserted will have to be determined. To demonstrate this the following method was used to batch 10000 inserts into groups of 1 (10000 batches, same as the initial parallel approach), 10 (1000 batches), 100 (100 batches), 1000 (10 batches), 10000 (1 batch, same as the serial insertion approach) then insert each batch in parallel:

private static TimeSpan RunAsParallelBatches(int inserts, int batchSize)
{
    Stopwatch watch = new Stopwatch();
    watch.Start();

    // batch the data to be inserted 
    List<List<int>> batches = new List<List<int>>();
    for (int g = 0; g < inserts / batchSize; g++)
    {
        List<int> numbers = new List<int>();
        int start = g * batchSize;
        int end = start + batchSize;
        for (int i = start; i < end; i++)
        {
            numbers.Add(i);
        }
        batches.Add(numbers);
    }

    // insert each batch in parallel
    Parallel.ForEach(batches,
        (batch) =>
        {
            using (DataClasses1DataContext ctx = new DataClasses1DataContext())
            {
                foreach (int number in batch)
                {
                    ctx.Tables.InsertOnSubmit(new Table() { Number = number });
                }
                ctx.SubmitChanges();
            }
        }
    );

    watch.Stop();
    return watch.Elapsed;
}

taking the average time for 10 runs of 10000 insertions generates the following results:

10000 inserts repeated 10 times
Average time for initial parallel insertion approach:                 00:00:22.7436584
Average time in parallel using batches of 1 entity (10000 batches):   00:00:23.1088289
Average time in parallel using batches of 10 entities (1000 batches): 00:00:07.1443220
Average time in parallel using batches of 100 entities (100 batches): 00:00:04.3111268
Average time in parallel using batches of 1000 entities (10 batches): 00:00:04.0668334
Average time in parallel using batches of 10000 entities (1 batch):   00:00:08.2820498
Average time for serial insertion approach:                           00:00:07.7273398

So by batching the insertions into groups, an performance increase can be gained so long as enough work is performed with in the iteration to outweigh the overhead of setting up the DataContext and performing the batch insertions. In this case by batching the insertions into groups of 1000, the parallel insertion managed to out perform the serial by ~2x on this system.

Answer 2

This can be done safely and will yield better performance. You need to make sure that:

• you are not ever accessing the same datacontext concurrently
• your are inserting batches of rows (maybe 100 to 10000 at a time). This will keep the overhead of instantiating the datacontext and opening connections low.