Add in table 500 000 rows

Question

It is necessary to add 500 000 lines in the table. But my version of the code takes too much time. Table Question 5 fields: id, NameQuestion, DetailsQuestion, UserId (Guid), Category (another table, a many-to-many) Example of code added to the table Question 500 000 rows.

 public void AddFive()
        {
            var q = new Question();
            int counQ = 0;
            for (int i=1; i<=100000; i++)
            {

                 foreach (var x in GetUsers()) // 5 пользователей
                 {
                     ++counQ;
           q = new Question
            {
                NameQuestion = "TestQuestion" + counQ,
                DetailsQuestion = "TestQuestion" + counQ,
                UserId =x,
                Category = GetcategoryList()
            };
           _context.Question.Add(q);

                }
                 _context.SaveChanges();
            }


        }

        public List<Guid> GetUsers()
        {
            List<Guid> userList = (from x in _context.aspnet_Users
                select x.UserId).ToList();
            return userList;
        }

        public List<Category> GetcategoryList()
        {

               var category = new List<Category>();
            Random rand = new Random();
            var count = rand.Next(1, 7);

            for (int i = 1; i <= count; i++)
            {
                var idCategory = rand.Next(1, 7);

                    category.Add(_context.Category.Find(idCategory));

            }

            return category.ToList();

        }

How can I speed up the process?

I'm voting to close this question as off-topic because any non-russian speaker can't help you. — Yuval Itzchakov, Aug 31 '15 at 11:55
Could you change the text to English. I wish I knew Russian. — , Aug 31 '15 at 11:57
With the above question, you should also put a tutorial of Russian! — Harshit, Aug 31 '15 at 11:58
[Do posts have to be in English on Stack Exchange?](http://meta.stackexchange.com/questions/13676/do-posts-have-to-be-in-english-on-stack-exchange) — Tim Schmelter, Aug 31 '15 at 11:59
Just move SaveChanges out from foreach statements. You no need to save each one row (Or at least do it for each 1000-x iteration). — MikkaRin, Aug 31 '15 at 12:12
I think the BulkInsert project might be something worth looking at : https://efbulkinsert.codeplex.com/ — Mithrandir, Aug 31 '15 at 12:25

score 0 · Answer 1 · edited May 23 '17 at 12:06

0

You should shut down and recreate a new context every 100 iterations for each new user to clear the context and avoid to surcharge it

Consider to turn off the Configuration.AutoDetectChangesEnabled property from your context in order to prevent automatic calls of the DetectChanges() method

Source

edited May 23 '17 at 12:06

Community

1
1

answered Aug 31 '15 at 12:33

Paradise228

699
3
16

score 0 · Answer 2 · answered Aug 31 '15 at 12:47

Such a huge number of records to be inserted cnan be done best with bcp (bulk copy):

            questionDT = new DataTable();
        questionDT.Columns.Add(new DataColumn("NameQuestion", typeof(string)));
        questionDT.Columns.Add(new DataColumn("DetailsQuestion", typeof(string)));
        questionDT.Columns.Add(new DataColumn("UserId", typeof(int)));
        var q = new Question();
        int counQ = 0;
        for (int i=1; i<=100000; i++)
        {

            foreach (var x in GetUsers()) 
            {
                ++counQ;
                var dr = questionDT.NewRow();

                dr["NameQuestion"] = "TestQuestion" + counQ;
                dr["DetailsQuestion"] = "TestQuestion" + counQ;
                dr["UserId"] = x;
                questionDT.Rows.Add(dr);
            }
        }
        using (var c = new SqlConnection(connectionstring))
        {
            c.Open();
            using (var bcp = new SqlBulkCopy(c))
            {
                bcp.BatchSize = 10000;
                bcp.ColumnMappings.Add("NameQuestion", "NameQuestion");
                bcp.ColumnMappings.Add("DetailsQuestion", "DetailsQuestion");
                bcp.ColumnMappings.Add("UserId", "UserId");
                bcp.DestinationTableName = "Questions";
                bcp.BulkCopyTimeout = 0;
                bcp.WriteToServer(questionDT);
            }
            c.Close();
        }

BCP is made for huge inserts, it removes indexes and foreign keys befor the update and re-adds them after to update

Add in table 500 000 rows

2 Answers2