Devart Oracle Entity Framework 4.1 performance

I want to know why Code fragment 1 is faster than Code 2 using POCO's with Devart DotConnect for Oracle.

I tried it over 100000 records and Code 1 is way faster than 2. Why? I thought "SaveChanges" would clear t开发者_Go百科he buffer making it faster as there is only 1 connection. Am I wrong?

Code 1:

        for (var i = 0; i < 100000; i++)
        {
            using (var ctx = new MyDbContext())
            {
                MyObj obj = new MyObj();
                obj.Id = i;
                obj.Name = "Foo " + i;
                ctx.MyObjects.Add(obj);
                ctx.SaveChanges();
            }
        }

Code 2:

        using (var ctx = new MyDbContext())
        {
            for (var i = 0; i < 100000; i++)
            {
                MyObj obj = new MyObj();
                obj.Id = i;
                obj.Name = "Foo " + i;
                ctx.MyObjects.Add(obj);
                ctx.SaveChanges();
            }
        }

The first code snippet works faster as the same connection is taken from the pool every time, so there are no performance losses on its re-opening.

In the second case 100000 objects gradually are added to the context. A slow snapshot-based tracking is used (if no dynamic proxy). This leads to the detection if any changes in any of cached objects occured on each SaveChanges(). More and more time is spent by each subsequent iteration.

We recommend you to try the following approach. It should have a better performance than the mentioned ones:

using (var ctx = new MyDbContext())
    {
        for (var i = 0; i < 100000; i++)
        {
            MyObj obj = new MyObj();
            obj.Id = i;
            obj.Name = "Foo " + i;
            ctx.MyObjects.Add(obj);                
        }
        ctx.SaveChanges();
    }

EDIT

If you use an approach with executing large number of operations within one SaveChanges(), it will be useful to configure additionally the Entity Framework behaviour of Devart dotConnect for Oracle provider:

// Turn on the Batch Updates mode:
var config = OracleEntityProviderConfig.Instance;
config.DmlOptions.BatchUpdates.Enabled = true;

// If necessary, enable the mode of re-using parameters with the same values:
config.DmlOptions.ReuseParameters = true;

// If object has a lot of nullable properties, and significant part of them are not set (i.e., nulls), omitting explicit insert of NULL-values will decrease greatly the size of generated SQL:
config.DmlOptions.InsertNullBehaviour = InsertNullBehaviour.Omit;

Only some options are mentioned here. The full list of them is available in our article: http://www.devart.com/blogs/dotconnect/index.php/new-features-of-entity-framework-support-in-dotconnect-providers.html

Am I wrong to assume that when SaveChanges() is called, all the objects in cache are stored to DB and the cache is cleared, so each loop is independent?

SaveChanges() sends and commits all changes to database, but change tracking is continued for all entities which are attached to the context. And new SaveChanges, if snapshot-based change tracking is used, will start a long process of checking (changed or not?) the values of each property for each object.