Improve foreach of List and save more data into database

Question

I m building a code to save more data into SQL database, using Entity Framework 5.0

I m build this code:

                foreach (EcgDTO.HrvData data in u.hrvData)
                {
                    if (data.hrv != null && data.hrv.Count > 0)
                    {

                        foreach (String d in data.hrv)
                        {
                            ECG ecg = new ECG();
                            ecg.Id = id++;
                            ecg.IdPerson = idPatient;
                            ecg.y = Decimal.Parse(d);
                            ecg.timestamp = data.createdDate;
                            dbData.ECG.Add(ecg);
                        }

                    }
                }
                int savedData = dbData.SaveChanges();

This is ECG Class

 [DataContract]
    public class EcgDTO
    {

        [DataMember(Name = "hrvData")]
        public List<HrvData> hrvData { get; set; }



        public class HrvData
        {
            [DataMember(Name = "patientId")]
            public String patientId { get; set; }
            [DataMember(Name = "hrv")]
            public List<String> hrv { get; set; }
            [DataMember(Name = "createdDate")]
            public DateTime createdDate { get; set; }
            [DataMember(Name = "createdBy")]
            public String createdBy { get; set; }
            [DataMember(Name = "modifiedBy")]
            public String modifiedBy { get; set; }

            [DataMember(Name = "modifiedDate")]
            public DateTime modifiedDate { get; set; }


        }
    }

Now my input List can have 227 items, and HRV list have 67 values

Is there a way to improve the speed of scrolling of the two lists and saving on the database?

Even 15209 iterations is not that much if it is occurring in memory. Are you having performance problems? If so have you profiled your application to figure out where? Perhaps it is database side? — Igor
– Igor, Commented Feb 27, 2020 at 14:16
If by "scrolling" you mean looping, the code can be cleaned up by using LINQ — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Feb 27, 2020 at 14:21
The only thing I see in your code that could be a performance issue is calling dbData.ECG.Add(ecg); in the loop. This is fine for a small number of records but calling dbData.ECG.AddRange is much more performant. You can create a new list of entities, List<T>, add to this list in the loop, and then call AddRange and pass this list. I am not saying that will fix whatever problem you have, you first have to do some profiling to figure out why your code execution is slow. — Igor
– Igor, Commented Feb 27, 2020 at 14:21
@PanagiotisKanavos could you please explain me better ? I want to improve the performazione of my Rest Web Service. I m using TSQL as databas — bircastri
– bircastri, Commented Feb 27, 2020 at 14:22
Which could be due to any kind of problems. Is the call to SaveChanges slow though? Are there any other queries locking that table? Do you keep connections or transactions open for long? Did you actually check what's going on in the database? In SQL Server, open SSMS and click on Activity Monitor. Is the server busy? Any expensive queries? Any blocked connections? — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Feb 27, 2020 at 14:39

Steve Py · Accepted Answer · 2020-02-27 22:12:37Z

When inserting large numbers of rows a performance factor is the DbContext and its tracking cache. The more entities a DbContext is tracking, the longer it will take to commit changes and attempt to review and update all associations. For variably large sets (10's of thousands or more) you should split up the sets into more manageable blocks. In your case this might not be necessary, but I would check that your DbContext is as short-lived as possible to avoid dealing with all other tracked entities while performing this operation. For a start:

using(var insertContext = new MyAppContext())
{
    foreach (EcgDTO.HrvData data in u.hrvData)
    {
        if (data.hrv == null && data.hrv.Count == 0)
            continue;

        foreach (String d in data.hrv)
        {
            ECG ecg = new ECG
            {
                Id = id++;
                IdPerson = idPatient;
                y = Decimal.Parse(d);
                timestamp = data.createdDate;
            };
            insertContext.ECG.Add(ecg);
        }
    }
    int savedData = insertContext.SaveChanges();
}

You could build the ECG entities using Linq, but I don't think that would be as easy to understand. The key suggestion here is not to use the main DbContext, but instead perform the inserts using a dedicated DbContext instance that is scoped just to this operation.

Logic like Id = id++; is a bit of a concern as this would require only one instance of this operation to be running ever at any particular point in time. Ideally the ID assignment for new rows should be handed at the DB level using Identity columns or Sequences/generation methods where EF is configured to treat them as Identity columns. This way you don't have to worry about multiple instances attempting to insert the same ID ranges. For instance if id is set to something like SELECT MAX(Id) From ECG, if two instances start this operation around the same time, both instances would get the same MAX(Id) value from the database and would attempt to insert the same incremented IDs. Whichever saved first would succeed while the 2nd would raise exceptions about duplicate PKs.

Collectives™ on Stack Overflow

Improve foreach of List and save more data into database

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related