Saving through Entity Frameworks taking too much time

Question

I am saving the records in an Observable collection to a table using Entity Frameworks 6.0.2 below is the code

   foreach(var item in obcollection)
   { 
       cid=InsertClaimsInfoes;
   }


    private long InsertClaimsInfoes()
    {
            using (OptimusEntities opt = new OptimusEntities())
            {
                var x = cc.Provider.Split(',')[1];
                var patf = cc.PatientName.Split(',')[1];
                var patl = cc.PatientName.Split(',')[0];
                var z = opt.Providers.Where(i => i.ProviderFirstName.ToLower().Trim() == x.ToLower().Trim()).FirstOrDefault().Id;


                ClaimsInfo _claimInfo = new ClaimsInfo();
                {
                    _claimInfo.Id = Convert.ToInt64(opt.ClaimsInfoes.Max(i => i.Id) + 1);
                    _claimInfo.BatchID = 1;
                    _claimInfo.ClientID = 122;
                    _claimInfo.PayorID = 0;

                    _claimInfo.ReceiverProviderId = opt.Providers.Where(i => 
                i.ProviderFirstName.ToLower().Trim() == x.ToLower().Trim()).FirstOrDefault().Id;

                    _claimInfo.ServiceProviderId = _claimInfo.ReceiverProviderId;
                    _claimInfo.SubscriberId = _claimInfo.ReceiverProviderId;

                    _claimInfo.PatientId = opt.Patients.Any(i => i.FirstName.ToLower().Trim() == patf.ToLower().Trim()) ? opt.Patients.Where(j => 
                j.FirstName.ToLower().Trim() == patf.ToLower().Trim()).FirstOrDefault().Id : InsertPatient(patf, patl, cc.DOB, 122, cc.Insurance);

                    _claimInfo.InsuranceName = cc.CarrierName;
                    _claimInfo.SelfClaim = true;
                    _claimInfo.DOSFrom = Convert.ToDateTime(cc.DOS);
                    _claimInfo.DOSTo = Convert.ToDateTime(cc.DOE);
                    _claimInfo.BillingDate = Convert.ToDateTime(cc.DOE);
                    _claimInfo.ClaimNum = cc.Insurance;
                    _claimInfo.SubmitAmount = Convert.ToDecimal(cc.BillValue);
                }
                //opt.Configuration.ProxyCreationEnabled = false;
                opt.ClaimsInfoes.Add(_claimInfo);

                opt.SaveChanges();

                return _claimInfo.Id;

        }

    }

It is taking 4-5 seconds to save a record. Size of ObservableCollection is more than 1000. It is taking 30mins aprox to save all the Observable Collection. Am I doing something wrong here that is taking more time. Is there anything I can do to improve performance.

For every new item you make query more than 2 queries to DB, it is not very efficient. Use caching. And what about to call `SaveChanges` when about 100 elements are processed, not only one. — Tony, Feb 04 '14 at 10:07
[Disable change tracking, save in smaller batches, recreate context](http://stackoverflow.com/a/5942176/1180426) or (maybe most importantly) don't use EF for batch inserts and prefer `SqlBulkCopy` instead... :) And on each new entry you're doing at least 2 database trips, that will be costly. — Patryk Ćwiek, Feb 04 '14 at 10:07
Thanks.. I try that out and let you know the change in performance — ElectricRouge, Feb 04 '14 at 10:17

James · Accepted Answer · 2014-02-04T10:13:37.530

3

You are creating a new connection to the database per record and saving each record individually. SaveChanges by nature is transactional, put your for loop inside the context and make a single call to SaveChanges i.e.

using (OptimusEntities opt = new OptimusEntities())
{
    foreach(var item in obcollection)
    {
        ...
    }
    opt.SaveChanges();
}

Also you appear to be making various queries before you do the insert which results in 1 trip to the DB per request. If you plan on doing batch inserts I would recommend using the SqlBulkCopy class for this as it's far more efficient.

edited Feb 04 '14 at 10:13

answered Feb 04 '14 at 10:07

James

80,725
18
167
237

Thanks.. I try that out and let you know the change in performance – ElectricRouge Feb 04 '14 at 10:16
@ElectricRouge You should find it's significantly faster overall, however, trying to batch commit *1000s* of records in one go using EF will never be super quick, better to use `SqlBulkCopy` or commit in smaller batches. – James Feb 04 '14 at 10:49
I made changes to my code as per your and Patryk Ćwiek suggestion. Now it has come down to 7mins aprox. I haven't tried `SqlBulkCopy` yet. I know 7mins still too much. the database calls I am making to fetch few field are taking time. Is there any other way to do that. – ElectricRouge Feb 04 '14 at 13:16
@ElectricRouge `SqlBulkCopy` will give you the sort of performance targets you are after. – James Feb 04 '14 at 13:19
Other than `SqlBulkCopy` will it be faster if I wrote stored procedure to save and call it instead of saving through EF – ElectricRouge Feb 04 '14 at 13:23
Most definitely, remember EF has to interpret/translate your query to SQL and then pass that over the wire. SP's are pre-compiled and already on the server side so you would see a big improvement. – James Feb 04 '14 at 13:35

Saving through Entity Frameworks taking too much time

1 Answers1