添加表500000行

本文关键字：500000行 添加 | 更新日期: 2023-09-27 18:10:27

需要在表中添加500000行。但是我的代码版本太费时间了。表问题5个字段:id, NameQuestion, DetailsQuestion, UserId (Guid)， Category(另一个表，多对多)添加到表Question 500000 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();
        }

我怎样才能加快这个过程?

添加表500000行

您应该关闭并为每个新用户每100次迭代重新创建一个新上下文，以清除上下文并避免对其进行附加操作

考虑关闭Configuration。AutoDetectChangesEnabled属性，以防止自动调用DetectChanges()方法

源

要插入如此大量的记录，最好使用bcp(批量复制):

            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是为大的插入做的，它在更新之前删除索引和外键，然后重新添加它们来更新

相关文章：