背景:
我們在使用數(shù)據(jù)庫的過程中，很多時候要追求性能，特別在處理大批量數(shù)據(jù)的時候更希望快速處理。那么對SQL SERVER而言，數(shù)據(jù)庫實現(xiàn)大批量插入的優(yōu)化方案，這里特別介紹通過大容量插入的一種方式。
基本原理：
簡單恢復(fù)模式按小方式記錄大多數(shù)大容量操作，對于完整恢復(fù)模式下的數(shù)據(jù)庫，大容量導(dǎo)入期間執(zhí)行的所有行插入操作被完整地記錄到事務(wù)日志中。如果數(shù)據(jù)導(dǎo)入量較大，會導(dǎo)致迅速填滿事務(wù)日志。對于大容量導(dǎo)入操作，按小方式記錄比完整記錄更有效，并減少了大容量導(dǎo)入操作填滿日志空間的可能性,所以性能會得到極大的提升。
但是，大容量導(dǎo)入中按小方式記錄日志的前提條件需要滿足：
1. 當前沒有復(fù)制表
2. 指定了表鎖定:
注意：鎖定是 SQL Server 數(shù)據(jù)庫引擎用來對多個用戶同時訪問同一數(shù)據(jù)塊的操作進行同步。當事務(wù)修改某個數(shù)據(jù)塊時，它將持有保護所做修改的鎖，直到事務(wù)結(jié)束。指定大容量導(dǎo)入操作的表鎖定后，該表將在大容量導(dǎo)入操作期間采取大容量更新 (BU) 鎖定。大容量更新 (BU) 鎖允許多個線程將數(shù)據(jù)并發(fā)地大容量導(dǎo)入到同一表中，同時阻止其他不進行大容量導(dǎo)入數(shù)據(jù)的進程訪問該表。表鎖定可以通過減少表的鎖爭用來提高大容量導(dǎo)入操作的性能。
基本的理論信息還很多，這里不再累述。
在阿里云SQL SERVER的主備架構(gòu)中，使用大容量插入時，使用時需要特別留意一個特性需要明確指定，如果不指定，會觸發(fā)微軟尚未在SQL Server 2008 R2中未修復(fù)的BUG（https://technet.microsoft.com/en-us/library/ms186247%28v=sql.105%29.aspx），會導(dǎo)致鏡像SUSPEND，那么如何來避免各種情況呢？ 下面列舉了一些常見的場景：
通過ado.net sqlbulkcopy 方式：
只需要將SqlBulkCopy 指定SqlBulkCopyOptions.CheckConstraints就好，數(shù)據(jù)庫指定AdventureWorks2008R2的Person表。舉個例子：
static void Main()
{
string srcConnString = "Data Source=(local);Integrated Security=true;Initial Catalog=testdb";
string desConnString = "Data Source=****.sqlserver.rds.aliyuncs.com,3433;User ID=**;Password=**;Initial Catalog=testdb";
SqlConnection srcConnection = new SqlConnection();
SqlConnection desConnection = new SqlConnection();
SqlCommand sqlcmd = new SqlCommand();
SqlDataAdapter da = new SqlDataAdapter();
DataTable dt = new DataTable();
srcConnection.ConnectionString = srcConnString;
desConnection.ConnectionString = desConnString;
sqlcmd.Connection = srcConnection;
sqlcmd.CommandText = @"SELECT top 1000000 [PersonType],[NameStyle],[Title],[FirstName],[MiddleName],[LastName],[Suffix],[EmailPromotion]
,[AdditionalContactInfo],[Demographics],NULL as rowguid,[ModifiedDate] FROM [testdb].[dbo].[Person]";
sqlcmd.CommandType = CommandType.Text;
sqlcmd.Connection.Open();
da.SelectCommand = sqlcmd;
da.Fill(dt);
using (SqlBulkCopy blkcpy = new SqlBulkCopy(desConnString, SqlBulkCopyOptions.CheckConstraints))
//using (SqlBulkCopy blkcpy = new SqlBulkCopy(desConnString, SqlBulkCopyOptions.Default))
{
blkcpy.BatchSize = 2000;
blkcpy.BulkCopyTimeout = 5000;
blkcpy.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnSqlRowsCopied);
blkcpy.NotifyAfter = 2000;
foreach (DataColumn dc in dt.Columns)
{
blkcpy.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);
}
try
{
blkcpy.DestinationTableName = "Person";
blkcpy.WriteToServer(dt);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
sqlcmd.Clone();
srcConnection.Close();
desConnection.Close();
}
}
}
private static void OnSqlRowsCopied(
object sender, SqlRowsCopiedEventArgs e)
{
Console.WriteLine("Copied {0} so far...", e.RowsCopied);
}
通過jdbc sqlbulkcopy 方式：
只需要在初始化對象時指定setCheckConstraints屬性為TRUE,例如：
QLServerBulkCopyOptions copyOptions = new SQLServerBulkCopyOptions();
copyOptions.setCheckConstraints(true);
實例可參見：https://msdn.microsoft.com/zh-cn/library/mt221490(v=sql.110).aspx
通過DTS/SSIS方式:
1. import/export data方式需要先保存SSIS包，然后修改Connection Manager的屬性
2. 直接使用SQL Server Business Intelligence Development Stuidio新建 SSIS包
通過BCP方式
1. 先將數(shù)據(jù)BCP出來 BCP ...OUT
BCP testdb.dbo.person Out "bcp_data" /t /N /U **** /P *** /S "****.sqlserver.rds.aliyuncs.com,3433"
2. 然后將數(shù)據(jù)BCP進去 BCP...IN ，但需要指定提示：/h "CHECK_CONSTRAINTS"
BCP testdb.dbo.person In "bcp_data" /C /N /q /k /h "CHECK_CONSTRAINTS" /U *** /P *** /b 500 /S "***.sqlserver.rds.aliyuncs.com,3433"
通過bulk insert方式（在RDS不可是實現(xiàn)，因為不允許上傳文件）
BULK INSERT testdb.dbo.person_in
FROM N'D:\trace\bcp.txt'
WITH
(
CHECK_CONSTRAINTS
);
本站文章版權(quán)歸原作者及原出處所有 。內(nèi)容為作者個人觀點， 并不代表本站贊同其觀點和對其真實性負責，本站只提供參考并不構(gòu)成任何投資及應(yīng)用建議。本站是一個個人學(xué)習交流的平臺，網(wǎng)站上部分文章為轉(zhuǎn)載，并不用于任何商業(yè)目的，我們已經(jīng)盡可能的對作者和來源進行了通告，但是能力有限或疏忽，造成漏登，請及時聯(lián)系我們，我們將根據(jù)著作權(quán)人的要求，立即更正或者刪除有關(guān)內(nèi)容。本站擁有對此聲明的最終解釋權(quán)。
