测试 批量 插入 数据库

using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using System.Threading.Tasks;

namespace BigData
{
    class Program
    {
        static void Main(string[] args)
        {
            IDbConnection connection = new SqlConnection("data source=127.0.0.1;initial catalog=DBTest;persist security info=True;user id=sa;password=123456;");
            IDbConnection connection2 = new SqlConnection("data source=127.0.0.1;initial catalog=DBTest;persist security info=True;user id=sa;password=123456;");


            byte[] randomBytes = new byte[4];
            RNGCryptoServiceProvider rngServiceProvider = new RNGCryptoServiceProvider();//产生随机数

            //       System.Diagnostics.Stopwatch stopwatch = new Stopwatch();
            //       stopwatch.Start(); //  开始监视代码运行时间
            //       #region 方式1
            //       for (int i = 1568691; i < 1578691; i++)//100000000
            //       {
            //           rngServiceProvider.GetBytes(randomBytes);
            //           Int32 resultInt = BitConverter.ToInt32(randomBytes, 0);
            //           string sql = $@" INSERT INTO [dbo].[User]
            //      ([Name]
            //       ,[Num]
            //      ,[Time])
            //VALUES
            //      ('Name{i}'
            //       ,{resultInt}
            //      ,'{DateTime.Now}') ";
            //           var result = connection.Execute(sql,
            //                  new { });
            //       } 
            //       #endregion
            //       stopwatch.Stop(); //  停止监视
            //       TimeSpan timespan = stopwatch.Elapsed;  //获取当前实例测量得出的总时间
            //       Console.WriteLine("方式1代码执行时间：{0}(毫秒)", timespan.TotalMilliseconds);//总毫秒数


            System.Diagnostics.Stopwatch stopwatch2 = new Stopwatch();
            stopwatch2.Start(); //  开始监视代码运行时间
            #region 方式2

            StringBuilder sb = new StringBuilder();

            for (int i = 1588691; i < 1598691; i++)//100000000
            {
                rngServiceProvider.GetBytes(randomBytes);
                Int32 resultInt = BitConverter.ToInt32(randomBytes, 0);

                sb.AppendLine($@" INSERT INTO [dbo].[User]
           ([Name]
            ,[Num]
           ,[Time])
     VALUES
           ('Name{i}'
            ,{resultInt}
           ,'{DateTime.Now}') ");

            }
            var result2 = connection.Execute(sb.ToString(),
                               new { });


            #endregion
            stopwatch2.Stop(); //  停止监视
            TimeSpan timespan2 = stopwatch2.Elapsed;  //获取当前实例测量得出的总时间
            Console.WriteLine("方式2代码执行时间：{0}(毫秒)", timespan2.TotalMilliseconds);//总毫秒数
                                                                                // 1万行数据
                                                                                //方式1代码执行时间：80770.9122(毫秒)
                                                                                //方式2代码执行时间：22732.9235(毫秒)
                                                                                //2 万行 数据库执行超时
                                                                                //10 万行 数据库执行超时


            System.Diagnostics.Stopwatch stopwatch3 = new Stopwatch();
            stopwatch3.Start(); //  开始监视代码运行时间

            #region 方式3
            List<Task> tasks = new List<Task>();
            tasks.Add(Task.Run(() =>
            {
                InsertFun(connection, rngServiceProvider,
 randomBytes, 1598691, 1603691);
            }));
            tasks.Add(Task.Run(() =>
            {
                InsertFun(connection2, rngServiceProvider,
randomBytes, 1603691, 1608691);
            }));
            Task.WaitAll();
            #endregion

            stopwatch3.Stop(); //  停止监视
            TimeSpan timespan3 = stopwatch3.Elapsed;  //获取当前实例测量得出的总时间
            Console.WriteLine("方式3代码执行时间：{0}(毫秒)", timespan3.TotalMilliseconds);//总毫秒数
            // 1万条数据测试
            //方式2代码执行时间：28438.1286(毫秒)
            //方式3代码执行时间：41.3877(毫秒)

            Console.Read();
        }





        static void InsertFun(IDbConnection connection, RNGCryptoServiceProvider rngServiceProvider,
            byte[] randomBytes, int start, int end)
        {
            #region 方式3

            StringBuilder sb3 = new StringBuilder();

            for (int i = start; i < end; i++)//100000000
            {
                rngServiceProvider.GetBytes(randomBytes);
                Int32 resultInt = BitConverter.ToInt32(randomBytes, 0);

                sb3.AppendLine($@" INSERT INTO [dbo].[User]
           ([Name]
            ,[Num]
           ,[Time])
     VALUES
           ('Name{i}'
            ,{resultInt}
           ,'{DateTime.Now}') ");

            }
            var result3 = connection.Execute(sb3.ToString(),
                               new { });


            #endregion

        }
    }




    public class Random
    {
        public int Get(System.Security.Cryptography.RNGCryptoServiceProvider rng)
        {
            //这样产生0 ~ 100的强随机数（含100）
            int max = 100;
            int rnd = int.MinValue;
            decimal _base = (decimal)long.MaxValue;
            byte[] rndSeries = new byte[8];

            //但是要注意别扔到循环里去，实例化RNG对象可是很消耗资源的
            //System.Security.Cryptography.RNGCryptoServiceProvider rng
            //    = new System.Security.Cryptography.RNGCryptoServiceProvider();
            rng.GetBytes(rndSeries);
            //不含100需去掉+1 
            //这个rnd就是你要的随机数，
            return rnd = (int)(Math.Abs(BitConverter.ToInt64(rndSeries, 0)) / _base * (max + 1));



        }
    }
}