Exception Saving Large Dataset

5 posts, 1 answers
  1. Lee Weisenberger
    Lee Weisenberger avatar
    72 posts
    Member since:
    Nov 2009

    Posted 26 Oct 2012 Link to this post

    I'm having problems with Telerik ORM when I'm trying import a large amount of data (16252 records, 45 columns) from a text file into a SQLServer table.

    Here is a snippet of the code I am using:
    using (EntitiesModel dataContext = new EntitiesModel())
    {
        foreach (JITDump_Model item in jitRecords)
        {
            JIT_Dump dataRecord = new JIT_Dump();
            dataRecord.AddChangeDeleteFlag = item.AddChangeDeleteFlag;
            dataRecord.CarrierArrivalTime = item.CarrierArrival;
            dataRecord.CarrierCode = item.CarrierCode;
     
           ...
     
            dataContext.Add(dataRecord);
     
        }
     
        dataContext.SaveChanges();
    }

    After adding the 16000+ records to the data context, I call dataContext.SaveChanges().  It blocks on the SaveChanges for about 4 minutes before throwing the following exception:

    System.InvalidOperationException: Invalid attempt to call Read when reader is closed.
       at Telerik.OpenAccess.RT.Adonet2Generic.Impl.ResultSetImp.next()
       at OpenAccessRuntime.Relational.sql.MsSqlDriver.getAutoIncColumnValue(RelationalTable classTable, Connection con, Statement stat)
       at Telerik.OpenAccess.Runtime.KeyGenerator.AutoIncRelationalKeyGenerator.generatePrimaryKeyPost(String className, RelationalTable classTable, Object[] data, Connection con, Statement stat)
       at OpenAccessRuntime.Relational.RelationalStorageManager.generateInserts(NewObjectOID oid, Int32 index, ClassMetaData cmd, PersistGraph graph, Int32[] fieldNos, CharBuf s, Object[] oidData, IntArray toUpdateIndexes)
       at OpenAccessRuntime.Relational.RelationalStorageManager.persistPass1(PersistGraph graph)
       at OpenAccessRuntime.Relational.RelationalStorageManager.doUpdates(StatesToStore toStore, StateContainer container, Boolean retainValues)
       at OpenAccessRuntime.Relational.RelationalStorageManager.store(StatesToStore toStore, DeletePacket toDelete, Boolean returnFieldsUpdatedBySM, Int32 storeOption, Boolean evictClasses) 


     My question is, what is causing this exception, and is there a better way to use ORM to import large amounts of data to a SQLServer database?

    In the past, when trying to basically do the same type of large data import, I've used BulkCopy.  But SQLBulkCopy is a bit of a pain and I was hoping to use ORM to simplify my work.

    Any help you can provide would be greatly appreciated.
  2. Answer
    Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 29 Oct 2012 Link to this post

    I think this could come from our ActiveConnectionTimout. (see http://www.telerik.com/help/openaccess-orm/feature-ref-api-context-api-backend-config-connectionpool-config.html for more details). In order to protect the server resources we close connections that have been in use a considerable time, which is what might happen naturally here in your use case. So please configure the timeout to zero.

    Kind regards,
    Thomas
    the Telerik team
    Telerik OpenAccess ORM Meets ASP.NET Web API. Read more.
  3. DevCraft banner
  4. XXXX
    XXXX avatar
    57 posts
    Member since:
    Jun 2008

    Posted 05 Nov 2012 Link to this post

    I'm doing something similar and I found out that adding either datacontext.Flush() or datacontext.SaveChanges() for every 8000 records improved over all performance. Do some testing to find what number of records gives the best results.

  5. Lee Weisenberger
    Lee Weisenberger avatar
    72 posts
    Member since:
    Nov 2009

    Posted 05 Nov 2012 Link to this post

    Thanks for the help.  I actually tried to do something similar but it negatively impacted performance.  Although a performance hit is better than a non-working app, that's for sure.
  6. Lee Weisenberger
    Lee Weisenberger avatar
    72 posts
    Member since:
    Nov 2009

    Posted 05 Nov 2012 Link to this post

    I've tried the ActiveConnectionTimeout suggestion and it seemed to help.  The problem has almost gone away, but I still get the error on occasion.  Using this suggestion and a couple of other tweaks, I've made the issue more manageable.
Back to Top
DevCraft banner