Saving data in SQL with many conditions then retrieves data from SQL to compare with before saving each record

Question

Conditions are if this record already exist, if the email in this record is valid or is in another table. i am looping data from excel sheet by uploading it and looping into records my code is working well and saving in database but the issue is that its taking to much time to finish the job like 4 days for comparing and saving 200k records... i need a better way

var Email = dt.Rows[j][3].ToString();
                List<AllBounced> bounce = cnx.AllBounceds.Where(x => x.Email == Email).ToList();
                List<MainField> mainfield = cnx.MainFields.Where(x => x.Email == data.Email).ToList();
                List<MainField> mainfield2 = cnx.MainFields.Where(x => x.Phone == data.Phone || x.Mobile == data.Mobile).ToList();
                if (data.Email != null)
                {
                    if (IsEmailValid(Email) == true)
                    {
                        if (mainfield.Count != 0)
                        {
                            MainFieldsDuplicate data2 = new MainFieldsDuplicate
                            {
                                Id = Guid.NewGuid(),
                                FirstName = dt.Rows[j][0].ToString(),
                                MiddleName = dt.Rows[j][1].ToString(),
                                LastName = dt.Rows[j][2].ToString(),
                                Email = dt.Rows[j][3].ToString(),
                                Phone = dt.Rows[j][4].ToString(),
                                Mobile = dt.Rows[j][5].ToString(),
                                a= dt.Rows[j][6].ToString(),
                                b= dt.Rows[j][7].ToString(),
                                c= dt.Rows[j][8].ToString(),
                                d= dt.Rows[j][9].ToString(),
                                e= dt.Rows[j][11].ToString(),
                                f= dt.Rows[j][12].ToString(),
                                j= dt.Rows[j][14].ToString(),
                                isDeleted = 0,
                                h= dt.Rows[j][13].ToString(),
                                Date = GetDateTime(dt.Rows[j][10].ToString()),
                            };
                            cnx.MainFieldsDuplicates.Add(data2);
                            cnx.SaveChanges();
                        }
                        else
                        {
                            if (bounce.Count != 0)
                            {
                                data.isBounce = true;
                            }
                            else
                            {
                                data.isBounce = false;
                            }
                            data.isValid = true;
                            cnx.MainFields.Add(data);
                            cnx.SaveChanges();
                        }
                    }

                    else
                    {
                        if (mainfield.Count != 0)
                        {
                            MainFieldsDuplicate data2 = new MainFieldsDuplicate
                            {
                                Id = Guid.NewGuid(),
                                FirstName = dt.Rows[j][0].ToString(),
                                MiddleName = dt.Rows[j][1].ToString(),
                                LastName = dt.Rows[j][2].ToString(),
                                Email = dt.Rows[j][3].ToString(),
                                Phone = dt.Rows[j][4].ToString(),
                                Mobile = dt.Rows[j][5].ToString(),
                                a= dt.Rows[j][6].ToString(),
                                b= dt.Rows[j][7].ToString(),
                                c= dt.Rows[j][8].ToString(),
                                d= dt.Rows[j][9].ToString(),
                                e = dt.Rows[j][11].ToString(),
                                f= dt.Rows[j][12].ToString(),
                                j= dt.Rows[j][14].ToString(),
                                isDeleted = 0,
                                h = dt.Rows[j][13].ToString(),
                                Date = GetDateTime(dt.Rows[j][10].ToString())
                            };
                            cnx.MainFieldsDuplicates.Add(data2);
                            cnx.SaveChanges();
                        }
                        else
                        {
                            if (bounce.Count != 0)
                            {
                                data.isBounce = true;
                            }
                            else
                            {
                                data.isBounce = false;
                            }
                            data.isValid = false;
                            cnx.MainFields.Add(data);
                            cnx.SaveChanges();
                        }
                    }
                }
                else
                {
                    if (mainfield2.Count != 0)
                    {
                        MainFieldsDuplicate data3 = new MainFieldsDuplicate
                        {
                            Id = Guid.NewGuid(),
                            FirstName = dt.Rows[j][0].ToString(),
                            MiddleName = dt.Rows[j][1].ToString(),
                            LastName = dt.Rows[j][2].ToString(),
                            Email = dt.Rows[j][3].ToString(),
                            Phone = dt.Rows[j][4].ToString(),
                            Mobile = dt.Rows[j][5].ToString(),
                            a= dt.Rows[j][6].ToString(),
                            b= dt.Rows[j][7].ToString(),
                            c= dt.Rows[j][8].ToString(),
                            d= dt.Rows[j][9].ToString(),
                            e= dt.Rows[j][11].ToString(),
                            f= dt.Rows[j][12].ToString(),
                            j= dt.Rows[j][14].ToString(),
                            isDeleted = 0,
                            h= dt.Rows[j][13].ToString(),
                            Date = GetDateTime(dt.Rows[j][10].ToString())
                        };
                        cnx.MainFieldsDuplicates.Add(data3);
                        cnx.SaveChanges();
                    }
                    else
                    {
                        cnx.MainFields.Add(data);
                        cnx.SaveChanges();
                    }

May be your looking for `SqlBulkCopy` https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlbulkcopy?view=netframework-4.8 https://stackoverflow.com/questions/13722014/insert-2-million-rows-into-sql-server-quickly — Eldho, Sep 25 '19 at 06:16
I Guess not in my case because i found that the issue is probably with SQL now slow inserting for new records — CH-siko, Sep 26 '19 at 06:24

score 0 · Accepted Answer · answered Sep 25 '19 at 04:50

0

You might want to profile your app to determine which parts you need to optimize, e.g. by just using the Stopwatch Class.

In your case, main suspects are the calls to the context, i.e. SaveChanges and Where/ToList. One thing you could try is avoiding to call SaveChanges on the context in each iteration, see this question. Another thing would be (if feasable) to load the AllBounced and MainField lists upfront into memory.

answered Sep 25 '19 at 04:50

user7217806

1,714
2
7
10

Thank you i removed the SaveChanges and put it at then end of all the code and changed the List<> to a int that only take the count of the list so the performance is better but I realized that after 30 min running the process, I stopped the function but it took SQL all the night to finish inserting new records till now, So it seems that the issue is with SQL Queue very slow to insert new records!!! – CH-siko Sep 26 '19 at 06:17
You can find tips for improving the insert performance in this [question](https://stackoverflow.com/questions/5940225/fastest-way-of-inserting-in-entity-framework). – user7217806 Sep 26 '19 at 10:12

Saving data in SQL with many conditions then retrieves data from SQL to compare with before saving each record

1 Answers1