Insert huge data from csv file in .Net framework 4.7.2

Question

I need to insert about 500k - 1 million data lines from a CSV file. The time of getting the reports list and insert to database by using SqlBulkCopy is very good (10k ~200ms). However, the time to check data validation and add elements to DataTable is very slow (10k data ~ 2 minute). I tried many ways but it didn't get any good results...

What should I do to improve my performance in this situation?

public ActionResult ConvertFile(HttpPostedFileBase postedFile, FormCollection formCollection)
{
    Stopwatch st = new Stopwatch();
    st.Start();

    string message = "";

    ConcurrentBag<ImportFailed> listFailed = new ConcurrentBag<ImportFailed>();

    var semesterName = formCollection["SemesterName"];
    var semester = SemestersDAO.GetSemesterByName(semesterName);

    var campusName = formCollection["CampusName"];
    var campus = CampusDAO.GetCampusByName(campusName);

    try
    {
        if (postedFile != null)
        {
            string path = Server.MapPath("~/Uploads/");

            if (!Directory.Exists(path))
            {
                Directory.CreateDirectory(path);
            }

            string filePath = path + Path.GetFileName(postedFile.FileName);
            postedFile.SaveAs(filePath);

            // Check Header of the file csv if it valid
            var validateHeadersRs = ImportHelper.IsValidHeader(filePath, new CSVStudent().Headers);

            if (validateHeadersRs != null)
            {
                message = $"Error: Missing headers: {validateHeadersRs}";
                ViewBag.Error = message;
                return View();
            }

            string conString = string.Empty;
            conString = string.Format(conString, filePath);
            conString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

            // Read CSV
            var reports = ImportHelper.ReadData<CSVStudent>(filePath, modified: false).ToList();
            st.Stop();

            var timeToGetList = st.ElapsedMilliseconds;
            st.Restart();

            DataTable studentDt = new DataTable();
            studentDt.Columns.AddRange(new DataColumn[5]
                    {
                        new DataColumn("Email", typeof(string)),
                        new DataColumn("Roll", typeof(string)),
                        new DataColumn("Full_Name", typeof(string)),
                        new DataColumn("Campus_ID", typeof(string)),
                        new DataColumn("Semester_ID", typeof(string))
                    });

            DataTable subjectStudentDt = new DataTable();
            subjectStudentDt.Columns.AddRange(new DataColumn[3]
                    {
                        new DataColumn("Subject_ID", typeof(string)),
                        new DataColumn("Roll", typeof(string)),
                        new DataColumn("Semester_ID", typeof(string))
                    });

            int countLine = 1;

            // ProcessData(reports, studentDt, subjectStudentDt, listFailed, semester, campus, countLine);

            # region Parallel
            Parallel.ForEach(reports, report =>
                    {
                        var (isValid, errorMessage) = validateEmail(report.Email);

                        if (!isValid)
                        {
                            listFailed.Add(new ImportFailed
                            {
                                Row = countLine.ToString(),
                                Error = errorMessage
                            });
                        }
                        else if (report.IsAnyEmpty())
                        {
                            listFailed.Add(new ImportFailed
                            {
                                Row = countLine.ToString(),
                                Error = "Data can't be empty!"
                            });
                        }
                        else if (SubjectsDAO.IsExistsSubjectStudent(report.Subject_ID, report.Roll,
                                     semester.Semester_ID))
                        {
                            listFailed.Add(new ImportFailed
                            {
                                Row = countLine.ToString(),
                                Error =
                                    $"Student {report.Roll} and subject {report.Subject_ID} is already exist"
                            });
                        }
                        else
                        {
                            lock (studentDt)
                            {
                                if (studentDt.AsEnumerable().All(row => report.Roll != row.Field<string>("Roll")))
                                {
                                    DataRow studentRow = studentDt.NewRow();
                                    studentRow["Email"] = report.Email;
                                    studentRow["Roll"] = report.Roll;
                                    studentRow["Full_Name"] = report.Full_Name;
                                    studentRow["Campus_ID"] = campus.Campus_ID;
                                    studentRow["Semester_ID"] = semester.Semester_ID;
                                    studentDt.Rows.Add(studentRow);
                                }
                            }
                            lock (subjectStudentDt)
                            {
                                DataRow row2 = subjectStudentDt.NewRow();
                                row2["Subject_ID"] = report.Subject_ID;
                                row2["Roll"] = report.Roll;
                                row2["Semester_ID"] = semester.Semester_ID;
                                subjectStudentDt.Rows.Add(row2);
                            }
                        }
                        Interlocked.Increment(ref countLine);
                    });

            #endregion

            int subStuBefore = Subject_StudentDAO.CountSubject_Student();
            int countBefore = StudentsDAO.CountNoStudent();
            st.Stop();

            var timeBeforeBulk = st.ElapsedMilliseconds;

            st.Restart();

            if (listFailed.Count <= 0)
            {
                using (var bulkCopy = new SqlBulkCopy(conString, SqlBulkCopyOptions.Default))
                {
                    // Set options for SqlBulkCopy
                    bulkCopy.BatchSize = 10000;
                    bulkCopy.BulkCopyTimeout = 0;
                            
                    bulkCopy.DestinationTableName = "Student";

                    foreach (DataColumn column in studentDt.Columns)
                    {
                        bulkCopy.ColumnMappings.Add(column.ColumnName, column.ColumnName);
                    }

                    bulkCopy.WriteToServer(studentDt);
                            
                    bulkCopy.DestinationTableName = "Subject_Student";

                    foreach (DataColumn column in subjectStudentDt.Columns)
                    {
                        bulkCopy.ColumnMappings.Add(column.ColumnName, column.ColumnName);
                    }

                    bulkCopy.WriteToServer(subjectStudentDt);
                }

                int subStuAfter = Subject_StudentDAO.CountSubject_Student();
                int countAfter = StudentsDAO.CountNoStudent();

                int countAdd = countAfter - countBefore;
                int countFail = studentDt.Rows.Count - countAdd;
                int addSubStu = subStuAfter - subStuBefore;

                st.Stop();

                message = $"Import success {countAdd} student(s), fail {countFail} student(s) and success {addSubStu} subject student(s) in {timeToGetList} - {timeBeforeBulk} - {st.ElapsedMilliseconds} ms";
                        ViewBag.Message = message;
            }
            else
            {
                ViewBag.Error = "There's some error in your file. Please check errors below, correct your file and import again!";
            }
        }
    }
    catch (Exception e)
    {
        message = e.Message;
        ViewBag.Error = message;
    }

    return View(listFailed);
}

“Very slow” is not a measurement. Tell us: how many rows? how many seconds? What is your target rows-per-second throughput figure? — J_H
– J_H, Commented Feb 12, 2024 at 23:16
@J_S Oh, sorry, With 10k line data from a csv file, it takes about 2 minutes. — HaNgocHieu
– HaNgocHieu, Commented Feb 12, 2024 at 23:38
While not strictly required, it is site standard to tag with the programming language used. — greybeard
– greybeard, Commented Feb 13, 2024 at 4:48

J_H · Accepted Answer · 2024-02-13 03:31:58Z

ACID, profiling

From your database tag and the Review Context, I'm assuming you are using a relational database management system, likely sqlserver. You explain that throughput with "SqlBulkCopy is very good", at 50k row/sec. And then time to validate + add will crawl along at just 83 row/sec.

You posted a routine with 193 SLOC, not breaking out per-line profiling measurements.

Think back on the last 193 menu items you ate for lunch. Which was the spiciest? Which was the 40th item? Which was the longest? You can't recall? Yeah neither can I, even though I just read all 193 lines. I can't see them all simultaneously on my monitor, and I can't hold them all in my mind. Why is that? Because I am human. As are all the colleagues you wish to share your technical ideas with. Write for your audience. Break large unsolvable problems into small solvable ones.

How do we know the current giant problem is unsolvable? Because you are asking your colleagues for help.

Maybe your code needs per-row ACID guarantees in real time. Personally, I am skeptical that your Requirements document specified that. I have not yet viewed such project documentation.

We see some timing instrumentation, but you have chosen not to share those results with reviewers.

region Parallel

Knuth asks us not to prematurely optimize, and instead to rely on measurements. The OP does not describe where observed hot spots are, so I will brashly assume the region within # region Parallel .. #endregion is (A.) where author believes most time is consumed, and (B.) where observations reveal the time is actually consumed. This is a risky assumption. Preferably we would have recorded timings to rely on.

Breaking out this region of code as a small helper function would simplify matters.

validation

It looks like we call validateEmail() and some others. Not sure how expensive each call is, as OP does not report that.

I assume that error rate is "low", and that empty rate is "low", so we don't spend much time evaluating those nor dealing with any resulting errors. Absent per-line profiling figures, it's hard to validate such an assumption.

enrollment

                studentRow["Roll"] = report.Roll;

OK, I'm just going to come out and say it. I have no idea what the semantics of that assignment might be.

I'm familiar with the notion of enrollment of students. And also how a given student might play the role of a TA or RA. But here it looks more like a Role, and there's no Review Context or comment documentation to guide me. In short, the spelling of that identifier looks like a bug, a code defect.

locking

                lock (studentDt) ...
                lock (subjectStudentDt) ...
            Interlocked.Increment(ref countLine);

I don't know what the cost of those mutex statements is, because folks who could measure it didn't tell me. But they look expensive.

Batch size is "one record". An RDBMS can certainly do BEGIN transaction, UPDATE single_record, COMMIT. But if you want it do perform a great many such updates per second, you will want to batch up multiple UPDATEs per COMMIT transaction.

Parallelizing this code is just asking for a proliferation of interacting locks.

A client that loops over a large worklist of pending database requests, issuing one small request after another, is typically working against the RDBMS's backend planner. Prefer to expose all million rows to the backend in the form of an unvalidated scratch temporary table using a single transaction. The hope is to obtain an improved EXPLAIN SELECT query plan.

happy path

It seems likely that more than 900k of the 1m rows you're sending will pass your sanity checks.

Identify such rows up front, and INSERT (or UPDATE) them in a single transaction. Then worry about the minority of losing rows. Since there's only a few, dealing with them won't take much time.

tl;dr: Prefer a few big DB transactions, over a million little ones.

Stack Exchange Network

Insert huge data from csv file in .Net framework 4.7.2

1 Answer 1

ACID, profiling

region Parallel

validation

enrollment

locking

happy path

You must log in to answer this question.

Hot Network Questions

Insert huge data from csv file in .Net framework 4.7.2

1 Answer 1

ACID, profiling

region Parallel

validation

enrollment

locking

happy path

You must log in to answer this question.

Related

Hot Network Questions