OpenAccess: Saving lots of item using SaveChanges()

[Seabus]

Hi,

I am using OpenAccess as an ORM and it's a great product and it does very well and I'm very happy to be using it.
One issue I have is that I have a function that has to save a lot of data to the database ( as much as 100 000 items )

I'm doing something like this:

using (var openAccessContext = new OpenAccessContext() )
{
     foreach( MyObject myObj in ObjectsToSave)
           openAccessContext.Add(myObj);
    
      openAccessContext.SaveChanges();
}

if the number of objects i have to save is very big , SaveChanges() actually fails and it gives some error ( i can't remember what the error message was ).... So I just want to ask what is the best approach to save a large number of items..

Thanks.

[Jan Blessenohl]

Hello Seabus,
Maybe the exception was that the connection has already been closed? This is because we stop long running operations after 2 minutes by default. Here is the code that changes the timeout to 6 minutes and that introduces a Flush() call on your context to avoid using too much memory, you can also do a SaveChanges() from time to time. It depends on the backend and network what is the fastest way, you have to test it with your deployment scenario.

class Program
{
    static void Main(string[] args)
    {
        using (var ctx = new EntitiesModel())
        {
            for (int i = 0; i < 100000; i++)
            {
                ctx.Add(new A_type() { Id = "b" + i });
                if (i % 10000 == 0)
                    ctx.Flush();
            }
            ctx.SaveChanges();
        }
    }
}
public partial class EntitiesModel
{
    static EntitiesModel()
    {
        backend.ConnectionPool.ActiveConnectionTimeout = 360;
    }
    public void Flush()
    {
        GetScope().Transaction.Flush();
    }
}

Kind regards,
Jan Blessenohl
the Telerik team

[Seabus]

Hi ...

Thanks for you reply !

Any tips on how to improve performance when saving massive items ?
I'm saving around 30 000 items and it takes 2 minutes I just want to know if this can be improved
Should I use SQL BULK UPLOAD instead ?

Seabus

[Jan Blessenohl]

Hi Seabus,
The time it takes depends on the size of your objects. ORMs are usually bad in mass operations and I count mass import as part of the operations that gets an unnecessary overhead.
With the Q2 release we have a new direct sql api that participates in the context transaction but can execute sql statements directly. Please have a look into the context.Execute... methods.

Kind regards,
Jan Blessenohl
the Telerik team

[Gopinath]

Hello,

I have the same issue, but in my case, I have the stored procedure call within that loop. and also at the same time I wanted an option rollback or commit all the storeprocedure calls within the loop at the end of transcation.

can I still use flush after each iteration?

Thanks,
Gopi

[Jan Blessenohl]

Hi Gopi,
During Flush we pin the used connection to the context instance and reuse it until you call save or clear changes. On this connection we keep the server side transaction running all the time and based on save or clear, we commit or rollback it.
You just have to have in mind that a longer running transaction on server side may allocate a lot of resources and can slow down the server. If you logically can split the operations in several commits, this is always better.

Greetings,
Jan Blessenohl
the Telerik team

[Gopinath]

Hello,

If I try to insert 5000 records in a transaction, i find only fewer records get inserted and the number changes during every execution.

But I can debug and ascertain the procedure is called 5000 times and in stored procedure, i have inserted a temp table which has got 5000 records. But the main table has only few records.

Please advice.

Thanks,
Gopi

[Jan Blessenohl]

Hi Gopinath,
I do not fully understand your problem. You say you are executing an SP several times in a loop and do flushes inbetween. You can see that the SP is called but only write operations to a temp table are working, not to the real table you want to change.

Can you show me your loop implementation?

Kind regards,
Jan Blessenohl
the Telerik team

[Gopinath]

Hello,
I am able to fix this issue by increasing the db connection timeout. But the program doesn't throw any exception when fewer records are inserted because of timeout issue. Please let me know how do i catch this exception. below is my code.

            try
            {
             scope.TransactionProperties.AutomaticBegin = true;
             scope.Transaction.Flush();

      for each ..(5000 times)
   {
     ExecuteProcedure(scope, fullProcedure, parameters, parameterValues, issues)
   }

      scope.CommitTransaction();
            }
            catch (Exception e)
            {
                scope.RollbackTransaction();
            }

private static IQueryResult ExecuteProcedure(IObjectScope scope, string fullProcedure, string parameters, object[] parameterValues, IssueCollection issues)
        {
            IQueryResult result = null;

            try
            {
                var query = scope.GetSqlQuery(fullProcedure, null, parameters);
                result = query.Execute(parameterValues);
                // The call to count forces the procedure to be executed
                var cnt = result.Count;
            }
            catch (Exception e)
            {
                issues.AddError(e.Message);
            }
            return result;
        }

[Jan Blessenohl]

Hello Gopinath,
You code looks good. Did you say that even if an exception occurred, you find some parts stored in the database? Means that the database connection is committed? That should not happen at all. I know that we did some changes in our connection pool handling some weeks ago. Which OA version are you using?

Why do you use the IObjectScope API? We have now the sql API integrated into the context API directly, maybe you want to checkout OpenAccessContextBase.ExecuteNonQuery().

Greetings,
Jan Blessenohl
the Telerik team

[Gopinath]

Hello Jan,

2011.2.713.3. I am using this version.

I would like to correct that even if few records are stored in the database, there is no exception is raised. the transaction completes successfully but with few records.

I want an exception to be raised in this case.

I dont want to change the iobjectscope at this moment. Pls. advise.

Thanks
Gopi

[Jan Blessenohl]

Hello Gopinath,
you are catching all exceptions coming from the stored procedure call. I guess you just have to say

catch (Exception e)
             {
                 issues.AddError(e.Message);
                 throw;
             }

Kind regards,
Jan Blessenohl
the Telerik team