天天看點
傳回

MSSQL資料批量插入優化詳細最常見的insert做法利于MSSQL資料庫的使用者自定義表類型做優化使用者自定義表類型常見的BULK INSERT 資料集插入優化

插入10w條資料的操作

MSSQL資料批量插入優化詳細最常見的insert做法利于MSSQL資料庫的使用者自定義表類型做優化使用者自定義表類型常見的BULK INSERT 資料集插入優化

最常見的insert做法 

　　　　　//執行資料條數
        int cnt = 10 * 10000;
        //要插入的資料
        CustomerFeedbackEntity m = new CustomerFeedbackEntity() { BusType = 1, CustomerPhone = "1888888888", BackType = 1, Content = "123123dagvhkfhsdjk肯定會撒嬌繁華的撒嬌防護等級劃分噶哈蘇德高房價盛大開放" };
        //第一種
        public void FristWay()
        {
            using (var conn = new SqlConnection(ConnStr))
            {
                conn.Open();
                Stopwatch sw = new Stopwatch();
                sw.Start();
                StringBuilder sb = new StringBuilder();
                Console.WriteLine("從：" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss fff") + "開始循環執行:" + cnt + "條sql語句 ...");
                for (int i = 0; i <= cnt; i++)
                {
                    sb.Clear();
                    sb.Append(@"INSERT INTO [dbo].[CustomerFeedback]
                                           ([BusType]
                                           ,[CustomerPhone]
                                           ,[BackType]
                                           ,[Content]
                                          )
                                     VALUES(");
                    sb.Append(m.BusType);
                    sb.Append(",'");
                    sb.Append(m.CustomerPhone);
                    sb.Append("',");
                    sb.Append(m.BackType);
                    sb.Append(",'");
                    sb.Append(m.Content);
                    sb.Append("')");
                    using (SqlCommand cmd = new SqlCommand(sb.ToString(), conn))
                    {
                        cmd.CommandTimeout = 0;
                        cmd.ExecuteNonQuery();
                    }
                }
                Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss fff") + "時，執行:" + cnt + "條sql語句完成 ! 耗時：" + sw.ElapsedMilliseconds + "毫秒。");
            }
        }

複制

執行結果如下：

MSSQL資料批量插入優化詳細最常見的insert做法利于MSSQL資料庫的使用者自定義表類型做優化使用者自定義表類型常見的BULK INSERT 資料集插入優化

10w條資料，693906毫秒，11分鐘

點評下：

1、不停的建立與釋放sqlcommon對象，會有性能浪費。

2、不停的與資料庫建立連接配接，會有很大的性能損耗。

此2點還有執行結果告訴我們，此種方式不可取，即便這是我們最常見的資料插入方式。

那麼我們針對以上兩點做優化，1、建立一次sqlcommon對象，隻與資料庫建立一次連接配接。優化改造代碼如下：

public void SecondWay()
        {
            using (var conn = new SqlConnection(ConnStr))
            {
                conn.Open();
                Stopwatch sw = new Stopwatch();
                sw.Start();
                StringBuilder sb = new StringBuilder();
                Console.WriteLine("從：" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss fff") + "開始循環拼接:" + cnt + "條sql語句 ...");
                for (int i = 0; i <= cnt; i++)
                {
                    sb.Append(@"INSERT INTO [dbo].[CustomerFeedback]
                                           ([BusType]
                                           ,[CustomerPhone]
                                           ,[BackType]
                                           ,[Content]
                                          )
                                     VALUES(");
                    sb.Append(m.BusType);
                    sb.Append(",'");
                    sb.Append(m.CustomerPhone);
                    sb.Append("',");
                    sb.Append(m.BackType);
                    sb.Append(",'");
                    sb.Append(m.Content);
                    sb.Append("')");
                }
                var result = sw.ElapsedMilliseconds;
                Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss fff") + "時，循環拼接:" + cnt + "條sql語句完成 ! 耗時：" + result + "毫秒。");
                using (SqlCommand cmd = new SqlCommand(sb.ToString(), conn))
                {
                    cmd.CommandTimeout = 0;
                    Stopwatch sw1 = new Stopwatch();
                    sw1.Start();
                    Console.WriteLine("從：" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss fff") + "開始執行:" + cnt + "條sql語句 ...");
                    cmd.ExecuteNonQuery();
                    Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss fff") + "時，執行:" + cnt + "條sql語句完成 ! 耗時：" + sw1.ElapsedMilliseconds + "毫秒。");
                }
            }
        }

複制

執行結果如下：

MSSQL資料批量插入優化詳細最常見的insert做法利于MSSQL資料庫的使用者自定義表類型做優化使用者自定義表類型常見的BULK INSERT 資料集插入優化

呀，好奇怪啊，為什麼跟上一個方案沒有多大差別呢？

點評：雖然看似得到啦優化，其實與上一個解決方案的執行過程幾乎是一樣的，是以就不用多說什麼啦。

利于MSSQL資料庫的使用者自定義表類型做優化

依舊先上代碼

CREATE TYPE CustomerFeedbackTemp AS  TABLE(
BusType int NOT NULL,
CustomerPhone varchar(40) NOT NULL,
BackType int NOT NULL,
Content nvarchar(1000) NOT NULL
)

複制

上面的腳本是在建立了 CustomerFeedback 表之後 額外建立的。

public void ThirdWay()
        {
            Stopwatch sw = new Stopwatch();
            Stopwatch sw1 = new Stopwatch();
            DataTable dt = GetTable();
            using (var conn = new SqlConnection(ConnStr))
            {
                string sql = @"INSERT INTO[dbo].[CustomerFeedback]
                                           ([BusType]
                                           ,[CustomerPhone]
                                           ,[BackType]
                                           ,[Content]
                                          ) select BusType,CustomerPhone,BackType,[Content] from @TempTb";
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    cmd.CommandTimeout = 0;
                    SqlParameter catParam = cmd.Parameters.AddWithValue("@TempTb", dt);
                    catParam.SqlDbType = SqlDbType.Structured;
                    catParam.TypeName = "dbo.CustomerFeedbackTemp";
                    conn.Open();
                    Console.WriteLine("從：" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss fff") + "開始循環插入記憶體表中:" + cnt + "條資料 ...");
                    sw.Start();
                    for (int i = 0; i < cnt; i++)
                    {
                        DataRow dr = dt.NewRow();
                        dr[0] = m.BusType;
                        dr[1] = m.CustomerPhone;
                        dr[2] = m.BackType;
                        dr[3] = m.Content;
                        dt.Rows.Add(dr);
                    }
                    Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss fff") + "時，循環插入記憶體表:" + cnt + "條資料完成 ! 耗時：" + sw.ElapsedMilliseconds + "毫秒。");
                    sw1.Start();
                    if (dt != null && dt.Rows.Count != 0)
                    {
                        cmd.ExecuteNonQuery();
                        sw.Stop();
                    }
                    Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss fff") + "時，執行:" + cnt + "條資料的datatable的資料進資料庫 ! 耗時：" + sw1.ElapsedMilliseconds + "毫秒。");
                }
            }
        }

複制

運作結果：

MSSQL資料批量插入優化詳細最常見的insert做法利于MSSQL資料庫的使用者自定義表類型做優化使用者自定義表類型常見的BULK INSERT 資料集插入優化

是的你沒有看錯，10w條資料，不到2秒。是不是迫不及待的要知道為什麼？迫不及待的想知道我們用到的使用者自定義表類型是什麼？

使用者自定義表類型

首先類型大家應該很容易了解，像int，varchar，bit等都是類型，那麼這個表類型是個毛線呢？

其實他就是使用者可以自己定義一個表結構然後把他當作一個類型。

建立自定義類型的詳細文檔：https://msdn.microsoft.com/zh-cn/library/ms175007.aspx

其次自定義類型也有一些限制，安全性：https://msdn.microsoft.com/zh-cn/library/bb522526.aspx

然後就是如何用這個類型，他的使用就是作為表值參數來使用的。

優點

就像其他參數一樣，表值參數的作用域也是存儲過程、函數或動态 Transact-SQL 文本。 同樣，表類型變量也與使用 DECLARE 語句建立的其他任何局部變量一樣具有作用域。 可以在動态 Transact-SQL 語句内聲明表值變量，并且可以将這些變量作為表值參數傳遞到存儲過程和函數。

表值參數具有更高的靈活性，在某些情況下，可比臨時表或其他傳遞參數清單的方法提供更好的性能。 表值參數具有以下優勢：

  • 首次從用戶端填充資料時，不擷取鎖。
  • 提供簡單的程式設計模型。
  • 允許在單個例程中包括複雜的業務邏輯。
  • 減少到伺服器的往返。
  • 可以具有不同基數的表結構。
  • 是強類型。
  • 使用戶端可以指定排序順序和唯一鍵。
  • 在用于存儲過程時像臨時表一樣被緩存。 從 SQL Server 2012 開始，對于參數化查詢，表值參數也被緩存。

限制

表值參數有下面的限制：

  • SQL Server 不維護表值參數列的統計資訊。
  • 表值參數必須作為輸入 READONLY 參數傳遞到 Transact-SQL 例程。 不能在例程體中對表值參數執行諸如 UPDATE、DELETE 或 INSERT 這樣的 DML 操作。
  • 不能将表值參數用作 SELECT INTO 或 INSERT EXEC 語句的目标。 表值參數可以在 SELECT INTO 的 FROM 子句中，也可以在 INSERT EXEC 字元串或存儲過程中。

常見的BULK INSERT 資料集插入優化 

public void FourWay()
        {

            Stopwatch sw = new Stopwatch();
            Stopwatch sw1 = new Stopwatch();
            DataTable dt = GetTable();
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                SqlBulkCopy bulkCopy = new SqlBulkCopy(conn);
                bulkCopy.BulkCopyTimeout = 0;
                bulkCopy.DestinationTableName = "CustomerFeedback";
                bulkCopy.BatchSize = dt.Rows.Count;
                conn.Open();
                Console.WriteLine("從：" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss fff") + "開始循環插入記憶體表中:" + cnt + "條資料 ...");
                sw.Start();
                for (int i = 0; i < cnt; i++)
                {
                    DataRow dr = dt.NewRow();
                    dr[0] = m.BusType;
                    dr[1] = m.CustomerPhone;
                    dr[2] = m.BackType;
                    dr[3] = m.Content;
                    dt.Rows.Add(dr);
                }
                Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss fff") + "時，循環插入記憶體表:" + cnt + "條資料完成 ! 耗時：" + sw.ElapsedMilliseconds + "毫秒。");
                sw1.Start();
                if (dt != null && dt.Rows.Count != 0)
                {
                    bulkCopy.WriteToServer(dt);
                    sw.Stop();
                }
                Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss fff") + "時，執行:" + cnt + "條資料的datatable的資料進資料庫 ! 耗時：" + sw1.ElapsedMilliseconds + "毫秒。");
            }

複制

執行結果：

MSSQL資料批量插入優化詳細最常見的insert做法利于MSSQL資料庫的使用者自定義表類型做優化使用者自定義表類型常見的BULK INSERT 資料集插入優化

1秒之内完成，1秒之内完成，看完這個簡直要在1秒之内完成10w條資料的插入的節奏，逆天，逆天啊。

bulk insert詳解：https://msdn.microsoft.com/zh-cn/library/ms188365.aspx

專業的點評：

表值參數的使用方法與其他基于資料集的變量的使用方法相似；但是，頻繁使用表值參數将比大型資料集要快。 大容量操作的啟動開銷比表值參數大，與之相比，表值參數在插入數目少于 1000 的行時具有很好的執行性能。

另外，這裡提供下GetTable()方法的類似實作

public static DataTable GetTable()
        {
            // Create a new DataTable.
            System.Data.DataTable table = new DataTable("ParentTable");
            // Declare variables for DataColumn and DataRow objects.
            DataColumn column;
            DataRow row;

            // Create new DataColumn, set DataType, 
            // ColumnName and add to DataTable.    
            column = new DataColumn();
            column.DataType = System.Type.GetType("System.Int32");
            column.ColumnName = "id";
            column.ReadOnly = true;
            column.Unique = true;
            // Add the Column to the DataColumnCollection.
            table.Columns.Add(column);

            // Create second column.
            column = new DataColumn();
            column.DataType = System.Type.GetType("System.String");
            column.ColumnName = "ParentItem";
            column.AutoIncrement = false;
            column.Caption = "ParentItem";
            column.ReadOnly = false;
            column.Unique = false;
            // Add the column to the table.
            table.Columns.Add(column);


        }

複制

這裡隻需要對表名稱，列名稱及類型做修改(隻需要寫類型及名稱即可)

參考網址

上一篇: 09——FFmpeg指令行實作畫中畫
下一篇: 是"幀"愛還是逢"場"做戲