SqlBulkCopy插入1亿条数据需要20分钟？

为什么80%的码农都做不了架构师？>>>   

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace SQLTest
{
    public class ClassData
    {
        public int GradeID = 0;
        public int ClassID = 0;
        public int StudentID = 0;
        public DateTime Datetime = DateTime.UtcNow;
        public double Data = 0;
        public int GradeIDField
        {
            get { return GradeID; }
            set { GradeID = value; }
        }
        public int ClassIDField
        {
            get { return ClassID; }
            set { ClassID = value; }
        }
        public int StudentIDField
        {
            get { return StudentID; }
            set { StudentID = value; }
        }
        public DateTime DatetimeField
        {
            get { return Datetime; }
            set { Datetime = value; }
        }
        public double DataField
        {
            get { return Data; }
            set { Data = value; }
        }

        public ClassData(int gradeID, int classID, int studentID, DateTime datetime, double data)
        {
            this.GradeID = gradeID;
            this.ClassID = classID;
            this.StudentID = studentID;
            this.Datetime = datetime;
            this.Data = data;
        }
    }
    class Program
    {
        public static void SqlBulkCopyByDatatable(string connectionString, string TableName, DataTable dataTable)
        {

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.UseInternalTransaction))
                {
                    try
                    {
                        sqlbulkcopy.DestinationTableName = TableName;
                        for (int i = 0; i < dataTable.Columns.Count; i++)
                        {
                            sqlbulkcopy.ColumnMappings.Add(dataTable.Columns[i].ColumnName, dataTable.Columns[i].ColumnName);
                        }
                        sqlbulkcopy.WriteToServer(dataTable);
                    }
                    catch (System.Exception ex)
                    {
                        throw ex;
                    }
                }
            }
        }

        private static string connectionString = @"Data Source=(local)\myschool;Database=mj_mydatabase;Integrated Security=true";

        static void Main(string[] args)
        {
            DateTime startDateTime = new DateTime(2016, 1, 1, 0, 0, 37);
            DateTime endDateTime = new DateTime(2016, 2, 10, 0, 10, 0);

            SqlConnection myConnection = new SqlConnection(connectionString);
            myConnection.Open();

            while (startDateTime <= endDateTime)
            {
                List<ClassData> dataList = new List<ClassData>();
                int start = 250;
                int step = 1;
                while (step <= 250)
                {
                    int temp = start + step;
                    for (int j = 36; j <= 39; j++)
                    {
                        DateTime realTime = startDateTime;
                        for (int k = 0; k < 5; k++)
                        {
                            dataList.Add(new ClassData(1, temp, j, realTime, 0.333333));
                            realTime = realTime.AddMinutes(1);
                        }
                    }
                    temp = start - step;
                    for (int j = 36; j <= 39; j++)
                    {
                        DateTime realTime = startDateTime;
                        for (int k = 0; k < 5; k++)
                        {
                            dataList.Add(new ClassData(1, temp, j, realTime, 0.333333));
                            realTime = realTime.AddMinutes(1);
                        }
                    }
                    step++;
                }
                startDateTime = startDateTime.AddMinutes(5);
                try
                {
                    DataTable dataTable = new DataTable("MJ_ClassData");
                    dataTable.Columns.Add("GradeID ", Type.GetType("System.Int32"));
                    dataTable.Columns.Add("ClassID", Type.GetType("System.Int32"));
                    dataTable.Columns.Add("StudentID", Type.GetType("System.Int32"));
                    dataTable.Columns.Add("Datetime", Type.GetType("System.DateTime"));
                    dataTable.Columns.Add("Data", Type.GetType("System.Single"));
                    foreach (ClassData data in dataList)
                    {
                        dataTable.Rows.Add(new Object[] { data.GradeID, data.ClassID, data.StudentID, data.Datetime, data.Data });
                    }
                    SqlBulkCopyByDatatable(connectionString, "MJ_ClassData", dataTable);
                }
                finally
                {
                    myConnection.Close();
                }
            }

        }
    }
}

比较搞笑的是，一开始是这样做的，让list的数据全部做完，调用一次sqlbulkcopy，结果...

转载于:https://my.oschina.net/mj23/blog/707172