0

I have written a class to upload a very large set of data into a database. The foreach loop seems to be slow, taking around 16ms per loop iteration. In what ways could I increase the speed of this operation? I have 41,000 rows to be added into a table that already has around 162,000 rows. The aditional 41k rows is taking 10min every time the method is called. I am retrieving this data from a large collection of thousands of files that are being parsed so you could imagine that this will be approximately 40 thousand rows of data per file times thousands of files. At 10 minutes each, I will be waiting an unreasonably long time to upload.

My theories are that perhaps the Convert.ToInt32() is costly but can that really be the cause of my slowness? The issue here is within my loop. 

class Uploader
{
    public void beginUpload(List<WaterData> data)
    {
        using (var db = new RTIDBContext())
        {
            var waterData = db.Set<water_data>();

            foreach (var day in data)
            {
                var rowdata = new water_data { measurment_date = day.date.ToString(), cond = Convert.ToInt32(day.waterConductivity), temp = null };
                waterData.Add(rowdata);
            }
            db.SaveChanges();          
        }
    }
}

Also, here is my WaterData class: 

class WaterData
{
    public DateTime date { get; set; }
    public double waterConductivity { get; set; }
}
Hooplator15
  • 1,540
  • 7
  • 31
  • 58
  • Why are you calling `var list = db.Set().SqlQuery("Select * from water_data").ToArray();`? – Steve Jan 15 '16 at 02:29
  • Ohh sorry, that was from me debugging.... I will remove that, it has no relevance. – Hooplator15 Jan 15 '16 at 02:30
  • Your entity class seem to be very small. Have you though about concatenating values and passing them in to a StoredProc? Then you can get the stored proc to split the values and save? I have not tested this. But I expect this method to be much faster than saving via EF. You can use EF to call the SP. – Kosala W Jan 15 '16 at 02:37

1 Answers1

0

Please use AddRange instead to avoid looping to every entry. See link

EX:

waterData.AddRange(data);
John Ephraim Tugado
  • 757
  • 7
  • 15
  • 1
    don't answer duplicates, extremely common things have in all these years been asked at least dozens a times if not more. –  Jan 15 '16 at 02:31