0

I am building an app that will store stock tick data. one of my methods needs to take an array of items, currently c. 100,000 items but will be over 1,000,000 at a time, and add them to an entity database. this is my current add logic:-

var tickContext = new DataAccess();

            // array is created, add it to the entity database
            for (int j = 0; j < tickDataArray.Length; j++)
            {
                MarketTickData temp2 = new MarketTickData();
                if(j > 1)
                    temp2 = tickDataArray[j-1];

                MarketTickData temp = tickDataArray[j]; 

                tickContext.TickBarData.Add(tickDataArray[j]);
                tickContext.Configuration.AutoDetectChangesEnabled = false;
                tickContext.Configuration.ValidateOnSaveEnabled = false;

                if (j % 200 == 0)
                {
                    tickContext.SaveChanges();
                    tickContext.Dispose();
                    tickContext = new DataAccess();
                }
            }

            // add remaining items to database
            tickContext.SaveChanges();

            tickContext.Configuration.AutoDetectChangesEnabled = true;
            tickContext.Configuration.ValidateOnSaveEnabled = true;

When I test my logic on various sizes of tickContext before saving I am not seeing huge improvments in preformance. At a Context size of 50 my adds are taking 47 Seconds, 1000 is 54 Seconds and the best is 200 items taking 44 seconds. But it is still relatively slow.

Is there a way to improve this ?

Gordon
  • 59
  • 7
  • Use SqlBulkCopy, not Entity Framework for this – ErikEJ Mar 23 '16 at 10:00
  • Try to place your call to `AutoDetectChangedEnabled = false` immediately after instantiating your contexts (in both places). – OJ Raqueño Mar 23 '16 at 11:15
  • Thx Raqueno. I noticed that I he'd them in the wrong place and changed it as you mentioned , it didn't make much difference. I am going to try the sqlbulkcopy. Thx for the help guys – Gordon Mar 23 '16 at 13:06
  • Possible duplicate of [Fastest Way of Inserting in Entity Framework](http://stackoverflow.com/questions/5940225/fastest-way-of-inserting-in-entity-framework) – Richard Mar 23 '16 at 14:52
  • Thanks ErikEJ, using SqlBulkCopy I got the time down to 3 seconds ! from over 40 Seconds. – Gordon Mar 24 '16 at 16:44

0 Answers0