OpenAccess: Saving lots of item using SaveChanges()

12 posts, 0 answers
  1. Seabus
    Seabus avatar
    33 posts
    Member since:
    Jun 2011

    Posted 13 Jul 2011 Link to this post

    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.


  2. Jan Blessenohl
    Admin
    Jan Blessenohl avatar
    707 posts

    Posted 14 Jul 2011 Link to this post

    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!

  3. DevCraft banner
  4. Seabus
    Seabus avatar
    33 posts
    Member since:
    Jun 2011

    Posted 14 Jul 2011 Link to this post

    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
  5. Jan Blessenohl
    Admin
    Jan Blessenohl avatar
    707 posts

    Posted 15 Jul 2011 Link to this post

    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!

  6. Gopinath
    Gopinath avatar
    21 posts
    Member since:
    Dec 2011

    Posted 20 Apr 2012 Link to this post

    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
  7. Jan Blessenohl
    Admin
    Jan Blessenohl avatar
    707 posts

    Posted 23 Apr 2012 Link to this post

    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.
  8. Gopinath
    Gopinath avatar
    21 posts
    Member since:
    Dec 2011

    Posted 24 May 2012 Link to this post

    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
  9. Jan Blessenohl
    Admin
    Jan Blessenohl avatar
    707 posts

    Posted 24 May 2012 Link to this post

    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!
  10. Gopinath
    Gopinath avatar
    21 posts
    Member since:
    Dec 2011

    Posted 05 Jul 2012 Link to this post

    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;
            }

  11. Jan Blessenohl
    Admin
    Jan Blessenohl avatar
    707 posts

    Posted 09 Jul 2012 Link to this post

    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.
  12. Gopinath
    Gopinath avatar
    21 posts
    Member since:
    Dec 2011

    Posted 09 Jul 2012 Link to this post

    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
  13. Jan Blessenohl
    Admin
    Jan Blessenohl avatar
    707 posts

    Posted 09 Jul 2012 Link to this post

    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.
Back to Top
DevCraft banner