This is a migrated thread and some comments may be shown as answers.
OpenAccess: Saving lots of item using SaveChanges()
11 Answers 244 Views
This is a migrated thread and some comments may be shown as answers.
This question is locked. New answers and comments are not allowed.
Seabus
Top achievements
Rank 1
Seabus asked on 13 Jul 2011, 11:26 PM
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.


11 Answers, 1 is accepted

Sort by
0
Jan Blessenohl
Telerik team
answered on 14 Jul 2011, 02:23 PM
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

Register for the Q2 2011 What's New Webinar Week. Mark your calendar for the week starting July 18th and book your seat for a walk through of all the exciting stuff we will ship with the new release!

0
Seabus
Top achievements
Rank 1
answered on 14 Jul 2011, 06:46 PM
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
0
Jan Blessenohl
Telerik team
answered on 15 Jul 2011, 08:21 AM
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

Register for the Q2 2011 What's New Webinar Week. Mark your calendar for the week starting July 18th and book your seat for a walk through of all the exciting stuff we will ship with the new release!

0
Gopinath
Top achievements
Rank 1
answered on 20 Apr 2012, 04:11 PM
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
0
Jan Blessenohl
Telerik team
answered on 23 Apr 2012, 10:11 AM
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
If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the OpenAccess ORM, subscribe to their blog feed now.
0
Gopinath
Top achievements
Rank 1
answered on 24 May 2012, 03:08 PM
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
0
Jan Blessenohl
Telerik team
answered on 24 May 2012, 03:52 PM
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
Follow @OpenAccessORM Twitter channel to be the first one to get the latest updates on new releases, tips and tricks and sneak peeks at our product labs!
0
Gopinath
Top achievements
Rank 1
answered on 05 Jul 2012, 12:13 PM
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;
        }

0
Jan Blessenohl
Telerik team
answered on 09 Jul 2012, 10:57 AM
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
OpenAccess ORM Q2'12 Now Available! Get your hands on all the new stuff.
0
Gopinath
Top achievements
Rank 1
answered on 09 Jul 2012, 04:42 PM
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
0
Jan Blessenohl
Telerik team
answered on 09 Jul 2012, 05:04 PM
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
OpenAccess ORM Q2'12 Now Available! Get your hands on all the new stuff.
Tags
General Discussions
Asked by
Seabus
Top achievements
Rank 1
Answers by
Jan Blessenohl
Telerik team
Seabus
Top achievements
Rank 1
Gopinath
Top achievements
Rank 1
Share this question
or