SQL Server 高性能寫(xiě)入的一些總結(jié)

在開(kāi)發(fā)過(guò)程中，我們不時(shí)會(huì)遇到系統(tǒng)性能瓶頸問(wèn)題，而引起這一問(wèn)題原因可以很多，有可能是代碼不夠高效、有可能是硬件或網(wǎng)絡(luò)問(wèn)題，也有可能是數(shù)據(jù)庫(kù)設(shè)計(jì)的問(wèn)題....

1.1.1 摘要

在開(kāi)發(fā)過(guò)程中，我們不時(shí)會(huì)遇到系統(tǒng)性能瓶頸問(wèn)題，而引起這一問(wèn)題原因可以很多，有可能是代碼不夠高效、有可能是硬件或網(wǎng)絡(luò)問(wèn)題，也有可能是數(shù)據(jù)庫(kù)設(shè)計(jì)的問(wèn)題。

本篇博文將針對(duì)一些常用的數(shù)據(jù)庫(kù)性能調(diào)休方法進(jìn)行介紹，而且，為了編寫(xiě)高效的SQL代碼，我們需要掌握一些基本代碼優(yōu)化的技巧，所以，我們將從一些基本優(yōu)化技巧進(jìn)行介紹。

1.1.2 正文

假設(shè)，我們要設(shè)計(jì)一個(gè)博客系統(tǒng)，其中包含一個(gè)用戶(hù)表（User），它用來(lái)存儲(chǔ)用戶(hù)的賬戶(hù)名、密碼、顯示名稱(chēng)和注冊(cè)日期等信息。

由于時(shí)間的關(guān)系，我們已經(jīng)把User表設(shè)計(jì)好了，它包括賬戶(hù)名、密碼（注意：這里沒(méi)有考慮隱私信息的加密存儲(chǔ)）、顯示名稱(chēng)和注冊(cè)日期等，具體設(shè)計(jì)如下：

-- =============================================
-- Author:        JKhuang
-- Create date: 7/8/2012
-- Description:    A table stores the user information.
-- =============================================
CREATE TABLE [dbo].[jk_users](
     -- This is the reference to Users table, it is primary key.
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [user_login] [varchar](60) NOT NULL,
    [user_pass] [varchar](64) NOT NULL,
    [user_nicename] [varchar](50) NOT NULL,
    [user_email] [varchar](100) NOT NULL,
    [user_url] [varchar](100) NOT NULL,

    -- This field get the default from function GETDATE().
    [user_registered] [datetime] NOT NULL CONSTRAINT [DF_jk_users_user_registered]  DEFAULT (getdate()),
    [user_activation_key] [varchar](60) NOT NULL,
    [user_status] [int] NOT NULL CONSTRAINT [DF_jk_users_user_status]  DEFAULT ((0)),
    [display_name] [varchar](250) NOT NULL
)

圖1 Users表設(shè)計(jì)

上面，我們定義了Users表，它包含賬戶(hù)名、密碼、顯示名稱(chēng)和注冊(cè)日期等10個(gè)字段，其中，ID是一個(gè)自增的主鍵，user_resistered用來(lái)記錄用戶(hù)的注冊(cè)時(shí)間，它設(shè)置了默認(rèn)值GETDATE()。

接下來(lái)，我們將通過(guò)客戶(hù)端代碼實(shí)現(xiàn)數(shù)據(jù)存儲(chǔ)到Users表中，具體的代碼如下：

//// Creates a database connection.
var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLCONN1"].ToString());
conn.Open();

//// This is a massive SQL injection vulnerability, 
//// don't ever write your own SQL statements with string formatting!
string sql = String.Format(
      @"INSERT INTO jk_users (user_login, user_pass, user_nicename, user_email, user_status,display_name, user_url, user_activation_key)
        VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}')",
      userLogin, userPass, userNicename, userEmail, userStatus, displayName, userUrl, userActivationKey);
var cmd = new SqlCommand(sql, conn);
cmd.ExecuteNonQuery();

//// Because this call to Close() is not wrapped in a try/catch/finally clause, 
//// it could be missed if an exception occurs above.  Don't do this!
conn.Close();

代碼中的問(wèn)題

上面，我們使用再普通不過(guò)的ADO.NET方式實(shí)現(xiàn)數(shù)據(jù)寫(xiě)入功能，但大家是否發(fā)現(xiàn)代碼存在問(wèn)題或可以改進(jìn)的地方呢？

首先，我們?cè)诳蛻?hù)端代碼中，創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)連接，它需要占用一定的系統(tǒng)資源，當(dāng)操作完畢之后我們需要釋放占用的系統(tǒng)資源，當(dāng)然，我們可以手動(dòng)釋放資源，具體實(shí)現(xiàn)如下：

//// Creates a database connection.
var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLCONN1"].ToString());
conn.Open();

//// This is a massive SQL injection vulnerability, 
//// don't ever write your own SQL statements with string formatting!
string sql = String.Format(
      @"INSERT INTO jk_users (user_login, user_pass, user_nicename, user_email, user_status,display_name, user_url, user_activation_key)
        VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}')",
      userLogin, userPass, userNicename, userEmail, userStatus, displayName, userUrl, userActivationKey);
var cmd = new SqlCommand(sql, conn);
cmd.ExecuteNonQuery();

//// If throws an exception on cmd dispose.
cmd.Dispose();
//// conn can't be disposed.
conn.Close();
conn.Dispose();

假如，在釋放SqlCommand資源時(shí)拋出異常，那么在它后面的資源SqlConnection將得不到釋放。我們仔細(xì)想想當(dāng)發(fā)生異常時(shí)，可以通過(guò)try/catch捕獲異常，所以無(wú)論是否發(fā)生異常都可以使用finally檢查資源是否已經(jīng)釋放了，具體實(shí)現(xiàn)如下：

SqlCommand cmd = null;
SqlConnection conn = null;
try
{
    //// Creates a database connection.
    conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLCONN1"].ToString());
    conn.Open();

    //// This is a massive SQL injection vulnerability, 
    //// don't ever write your own SQL statements with string formatting!
    string sql = String.Format(
          @"INSERT INTO jk_users (user_login, user_pass, user_nicename, user_email, user_status,display_name, user_url, user_activation_key)
        VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}')",
          userLogin, userPass, userNicename, userEmail, userStatus, displayName, userUrl, userActivationKey);
    cmd = new SqlCommand(sql, conn);
    cmd.ExecuteNonQuery();
}
finally
{
    //// Regardless of whether there is an exception,
    //// we will dispose the resource. 
    if (cmd != null) cmd.Dispose();
    if (conn != null) conn.Dispose();
}

通過(guò)上面的finally方式處理了異常情況是很普遍的，但為了更安全釋放資源，使得我們?cè)黾恿薴inally和if語(yǔ)句，那么是否有更簡(jiǎn)潔的方法實(shí)現(xiàn)資源的安全釋放呢？

其實(shí)，我們可以使用using語(yǔ)句實(shí)現(xiàn)資源的釋放，具體實(shí)現(xiàn)如下：

using語(yǔ)句：定義一個(gè)范圍，將在此范圍之外釋放一個(gè)或多個(gè)對(duì)象。

string sql = String.Format(
      @"INSERT INTO jk_users (user_login, user_pass, user_nicename, user_email, user_status,display_name, user_url, user_activation_key)
        VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}')",
              userLogin, userPass, userNicename, userEmail, userStatus, displayName, userUrl, userActivationKey);

//// Creates a database connection.
using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLCONN1"].ToString()))
using (var cmd = new SqlCommand(sql, conn))
{
    //// Your code here.
}

上面的代碼使用了using語(yǔ)句實(shí)現(xiàn)資源的釋放，那么是否所有對(duì)象都可以使用using語(yǔ)句實(shí)現(xiàn)釋放呢？

只有類(lèi)型實(shí)現(xiàn)了IDisposable接口并且重寫(xiě)Dispose()方法可以使用using語(yǔ)句實(shí)現(xiàn)資源釋放，由于SqlConnection和SqlCommand實(shí)現(xiàn)了IDisposable接口，那么我們可以使用using語(yǔ)句實(shí)現(xiàn)資源釋放和異常處理。

在客戶(hù)端代碼中，我們使用拼接SQL語(yǔ)句方式實(shí)現(xiàn)數(shù)據(jù)寫(xiě)入，由于SQL語(yǔ)句是動(dòng)態(tài)執(zhí)行的，所以惡意用戶(hù)可以通過(guò)拼接SQL的方式實(shí)施SQL注入攻擊。

對(duì)于SQL注入攻擊，我們可以通過(guò)以下方式防御：

正則表達(dá)校驗(yàn)用戶(hù)輸入
參數(shù)化存儲(chǔ)過(guò)程
參數(shù)化SQL語(yǔ)句
添加數(shù)據(jù)庫(kù)新架構(gòu)
LINQ to SQL

接下來(lái)，我們將通過(guò)參數(shù)化SQL語(yǔ)句防御SQL注入攻擊，大家也可以使用其他的方法防御SQL注入攻擊，具體實(shí)現(xiàn)代碼如下：

//// Creates a database connection.
using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLCONN1"].ToString()))
{
    conn.Open();
    string sql = string.Format(
             @"INSERT INTO jk_users (user_login, user_pass, user_nicename, user_email, 
                user_status,display_name, user_url, user_activation_key)");

    using (var cmd = new SqlCommand(sql, conn))
    {
        //// Parameterized SQL to defense injection attacks
        cmd.Parameters.Add("@user_login", userLogin);
        cmd.Parameters.Add("@user_pass", userPass);
        cmd.Parameters.Add("@user_nicename", userNicename);
        cmd.Parameters.Add("@user_email", userEmail);
        cmd.Parameters.Add("@user_status", userStatus);
        cmd.Parameters.Add("@display_name", displayName);
        cmd.Parameters.Add("@user_url", userUrl);
        cmd.Parameters.Add("@user_activation_key", userActivationKey);
        cmd.ExecuteNonQuery();
    }
}

上面通過(guò)參數(shù)化SQL語(yǔ)句和using語(yǔ)句對(duì)代碼進(jìn)行改進(jìn)，現(xiàn)在代碼的可讀性更強(qiáng)了，而且也避免了SQL注入攻擊和資源釋放等問(wèn)題。

接下來(lái)，讓我們簡(jiǎn)單的測(cè)試一下代碼執(zhí)行時(shí)間，首先我們?cè)诖a中添加方法Stopwatch.StartNew()和Stopwatch.Stop()來(lái)計(jì)算寫(xiě)入代碼的執(zhí)行時(shí)間，具體代碼如下：

    //// calc insert 10000 records consume time.
    var sw = Stopwatch.StartNew();

    //// Creates a database connection.
    using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLCONN2"].ToString()))
    {
        conn.Open();
        int cnt = 0;
        while (cnt++ < 10000)
        {
            string sql = string.Format(@"INSERT INTO jk_users 
                 (user_login, user_pass, user_nicename, user_email, user_status,display_name, user_url, user_activation_key)
                 VALUES (@user_login, @user_pass, @user_nicename, @user_email, @user_status, @display_name, @user_url, @user_activation_key)");

            using (var cmd = new SqlCommand(sql, conn))
            {
                //// Parameterized SQL to defense injection attacks
                cmd.Parameters.Add("@user_login", userLogin);
                cmd.Parameters.Add("@user_pass", userPass);
                cmd.Parameters.Add("@user_nicename", userNicename);
                cmd.Parameters.Add("@user_email", userEmail);
                cmd.Parameters.Add("@user_status", userStatus);
                cmd.Parameters.Add("@display_name", displayName);
                cmd.Parameters.Add("@user_url", userUrl);
                cmd.Parameters.Add("@user_activation_key", userActivationKey);
                cmd.ExecuteNonQuery();
            }
        }
    }

    sw.Stop();
}

上面，我們往數(shù)據(jù)庫(kù)中寫(xiě)入了10000條數(shù)據(jù)，執(zhí)行時(shí)間為 7.136秒（我的機(jī)器很破了），這樣系統(tǒng)性能還是可以滿(mǎn)足許多公司的需求了。

假如，用戶(hù)請(qǐng)求量增大了，我們還能保證系統(tǒng)能滿(mǎn)足需求嗎？事實(shí)上，我們不應(yīng)該滿(mǎn)足于現(xiàn)有的系統(tǒng)性能，因?yàn)槲覀冎来a的執(zhí)行效率還有很大的提升空間。

接下來(lái)，將進(jìn)一步介紹代碼改善的方法。

圖2 數(shù)據(jù)寫(xiě)入U(xiǎn)sers表

為了使數(shù)據(jù)庫(kù)獲得更快的寫(xiě)入速度，我們必須了解數(shù)據(jù)庫(kù)在進(jìn)行寫(xiě)入操作時(shí)的主要耗時(shí)。

數(shù)據(jù)庫(kù)性能開(kāi)銷(xiāo)

連接時(shí)間

當(dāng)我們執(zhí)行conn.Open()時(shí)，首先，必須建立物理通道（例如套接字或命名管道），必須與服務(wù)器進(jìn)行初次握手，必須分析連接字符串信息，必須由服務(wù)器對(duì)連接進(jìn)行身份驗(yàn)證，必須運(yùn)行檢查以便在當(dāng)前事務(wù)中登記，等等

這一系列操作可能需要一兩秒鐘時(shí)間，如果我們每次執(zhí)行conn.Open()都有進(jìn)行這一系列操作是很耗費(fèi)時(shí)間的，為了使打開(kāi)的連接成本最低，ADO.NET使用稱(chēng)為連接池的優(yōu)化方法。

連接池：減少新連接需要打開(kāi)的次數(shù)，只要用戶(hù)在連接上調(diào)用 Open()方法，池進(jìn)程就會(huì)檢查池中是否有可用的連接，如果某個(gè)池連接可用，那么將該連接返回給調(diào)用者，而不是創(chuàng)建新連接；應(yīng)用程序在該連接上調(diào)用 Close()或Dispose() 時(shí)，池進(jìn)程會(huì)將連接返回到活動(dòng)連接池集中，而不是真正關(guān)閉連接，連接返回到池中之后，即可在下一個(gè) Open 調(diào)用中重復(fù)使用。

解析器的開(kāi)銷(xiāo)

當(dāng)我們向SQL Server傳遞SQL語(yǔ)句INSERT INTO …時(shí)，它需要對(duì)SQL語(yǔ)句進(jìn)行解析，由于SQL Server解析器執(zhí)行速度很快，所以解析時(shí)間往往是可以忽略不計(jì)，但我們?nèi)匀豢梢酝ㄟ^(guò)使用存儲(chǔ)過(guò)程，而不是直SQL語(yǔ)句來(lái)減少解析器的開(kāi)銷(xiāo)。

數(shù)據(jù)庫(kù)連接

為了提供ACID（事務(wù)的四個(gè)特性），SQL Server必須確保所有的數(shù)據(jù)庫(kù)更改是有序的。它是通過(guò)使用鎖來(lái)確保該數(shù)據(jù)庫(kù)插入、刪除或更新操作之間不會(huì)相互沖突（關(guān)于數(shù)據(jù)庫(kù)的鎖請(qǐng)參考這里）。

由于，大多數(shù)數(shù)據(jù)庫(kù)都是面向多用戶(hù)的環(huán)境，當(dāng)我們對(duì)User表進(jìn)行插入操作時(shí)，也許有成千上百的用戶(hù)也在對(duì)User表進(jìn)行操作，所以說(shuō)，SQL Server必須確保這些操作是有序進(jìn)行的。

那么，當(dāng)SQL Server正在做所有這些事情時(shí)，它會(huì)產(chǎn)生鎖，以確保用戶(hù)獲得有意義的結(jié)果。SQL Server保證每條語(yǔ)句執(zhí)行時(shí)，數(shù)據(jù)庫(kù)是完全可預(yù)測(cè)的（例如：預(yù)測(cè)SQL執(zhí)行方式）和管理鎖都需要耗費(fèi)一定的時(shí)間。

約束處理

在插入數(shù)據(jù)時(shí)，每個(gè)約束（如：外鍵、默認(rèn)值、SQL CHECK等）需要額外的時(shí)間來(lái)檢測(cè)數(shù)據(jù)是否符合約束；由于SQL Server為了保證每個(gè)插入、更新或刪除的記錄都符合約束條件，所以，我們需要考慮是否應(yīng)該在數(shù)據(jù)量大的表中增加約束條件。

Varchar

VARCHAR是數(shù)據(jù)庫(kù)常用的類(lèi)型，但它也可能導(dǎo)致意想不到的性能開(kāi)銷(xiāo)；每次我們存儲(chǔ)可變長(zhǎng)度的列，那么SQL Server必須做更多的內(nèi)存管理；字符串可以很容易地消耗數(shù)百字節(jié)的內(nèi)存的，如果我們?cè)谝粋€(gè)VARCHAR列中設(shè)置索引，那么SQL Server執(zhí)行B-樹(shù)搜索時(shí)，就需要進(jìn)行O(字符串長(zhǎng)度)次比較，然而，整數(shù)字段比較次數(shù)只受限于內(nèi)存延遲和CPU頻率。

磁盤(pán)IO

SQL Server最終會(huì)將數(shù)據(jù)寫(xiě)入到磁盤(pán)中，首先，SQL Server把數(shù)據(jù)寫(xiě)入到事務(wù)日志中，當(dāng)執(zhí)行備份時(shí)，事務(wù)日志會(huì)合并到永久的數(shù)據(jù)庫(kù)文件中；這一系列操作由后臺(tái)完成，它不會(huì)影響到數(shù)據(jù)查詢(xún)的速度，但每個(gè)事物都必須擁有屬于自己的磁盤(pán)空間，所以我們可以通過(guò)給事務(wù)日志和主數(shù)據(jù)文件分配獨(dú)立的磁盤(pán)空間減少I(mǎi)O開(kāi)銷(xiāo)，當(dāng)然，最好解決辦法是盡可能減少事務(wù)的數(shù)量。

正如大家所看到的，我們通過(guò)優(yōu)化聯(lián)接時(shí)間、解析器的開(kāi)銷(xiāo)、數(shù)據(jù)庫(kù)聯(lián)接、約束處理,、Varchar和磁盤(pán)IO等方法來(lái)優(yōu)化數(shù)據(jù)庫(kù)，接下來(lái)，我們將對(duì)前面的例子進(jìn)行進(jìn)一步的優(yōu)化。

使用存儲(chǔ)過(guò)程

前面例子中，我們把SQL代碼直接Hardcode在客戶(hù)端代碼中，那么，數(shù)據(jù)庫(kù)就需要使用解析器解析客戶(hù)端中SQL語(yǔ)句，所以我們可以改用使用存儲(chǔ)過(guò)程，從而，減少解析器的時(shí)間開(kāi)銷(xiāo)；更重要的一點(diǎn)是，由于SQL是動(dòng)態(tài)執(zhí)行的，所以我們修改存儲(chǔ)過(guò)程中的SQL語(yǔ)句也無(wú)需重新編譯和發(fā)布程序。

User表中的字段user_registered設(shè)置了默認(rèn)值（GETDATE()），那么我們通過(guò)消除表默認(rèn)值約束來(lái)提高系統(tǒng)的性能，簡(jiǎn)而言之，我們需要提供字段user_registered的值。

接下來(lái)，讓我們省去User表中的默認(rèn)值約束和增加存儲(chǔ)過(guò)程，具體代碼如下：

-- =============================================
-- Author:        JKhuang
-- Create date: 08/16/2012
-- Description:    Creates stored procedure to insert
-- data into table jk_users.
-- =============================================
ALTER PROCEDURE [dbo].[SP_Insert_jk_users] 
    @user_login varchar(60), 
    @user_pass varchar(64), 
    @user_nicename varchar(50), 
    @user_email varchar(100), 
    @user_url varchar(100), 
    @user_activation_key varchar(60),
    @user_status int, 
    @display_name varchar(250)
     
AS
BEGIN
    SET NOCOUNT ON;

-- The stored procedure allows SQL server to avoid virtually all parser work
INSERT INTO jk_users 
       (user_login, user_pass, user_nicename, user_email, user_status,display_name, user_url, user_activation_key, user_registered)
       VALUES (@user_login, @user_pass, @user_nicename, @user_email, @user_status, @display_name, @user_url, @user_activation_key, GETDATE());
END

上面我們定義了存儲(chǔ)過(guò)程SP_Insert_jk_users向表中插入數(shù)據(jù)，當(dāng)我們重新執(zhí)行代碼時(shí)，發(fā)現(xiàn)數(shù)據(jù)插入的時(shí)間縮短為6.7401秒。

圖3數(shù)據(jù)寫(xiě)入時(shí)間

使用數(shù)據(jù)庫(kù)事務(wù)

想想數(shù)據(jù)是否可以延長(zhǎng)寫(xiě)入到數(shù)據(jù)庫(kù)中，是否可以批量地寫(xiě)入呢？如果允許延遲一段時(shí)間才寫(xiě)入到數(shù)據(jù)庫(kù)中，那么我們可以使用Transaction來(lái)延遲數(shù)據(jù)寫(xiě)入。

數(shù)據(jù)庫(kù)事務(wù)是數(shù)據(jù)庫(kù)管理系統(tǒng)執(zhí)行過(guò)程中的一個(gè)邏輯單位，由一個(gè)有限的數(shù)據(jù)庫(kù)操作序列構(gòu)成。 SQL Server確保事務(wù)執(zhí)行成功后，數(shù)據(jù)寫(xiě)入到數(shù)據(jù)庫(kù)中，反之，事務(wù)將回滾。

如果我們對(duì)數(shù)據(jù)庫(kù)進(jìn)行十次獨(dú)立的操作，那么SQL Server就需要分配十次鎖開(kāi)銷(xiāo)，但如果把這些操作都封裝在一個(gè)事務(wù)中，那么SQL Server只需要分配一次鎖開(kāi)銷(xiāo)。

    //// calc insert 10000 records consume time.
    var sw = Stopwatch.StartNew();

    //// Creates a database connection.
    using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLCONN2"].ToString()))
    {
        conn.Open();
        int cnt = 0;
        SqlTransaction trans = conn.BeginTransaction();
        while (cnt++ < 10000)
        {
            using (var cmd = new SqlCommand("SP_Insert_jk_users", conn))
            {
                //// Parameterized SQL to defense injection attacks
                cmd.CommandType = CommandType.StoredProcedure;

                //// Uses transcation to batch insert data.
                //// To avoid lock and connection overhead.
                cmd.Transaction = trans;
                cmd.Parameters.Add("@user_login", userLogin);
                cmd.Parameters.Add("@user_pass", userPass);
                cmd.Parameters.Add("@user_nicename", userNicename);
                cmd.Parameters.Add("@user_email", userEmail);
                cmd.Parameters.Add("@user_status", userStatus);
                cmd.Parameters.Add("@display_name", displayName);
                cmd.Parameters.Add("@user_url", userUrl);
                cmd.Parameters.Add("@user_activation_key", userActivationKey);
                cmd.ExecuteNonQuery();
            }
        }

        //// If no exception, commit transcation.
        trans.Commit();
    }

    sw.Stop();
}

圖4 數(shù)據(jù)寫(xiě)入時(shí)間

使用SqlBulkCopy

通過(guò)使用事務(wù)封裝了寫(xiě)入操作，當(dāng)我們重新運(yùn)行代碼，發(fā)現(xiàn)數(shù)據(jù)寫(xiě)入的速度大大提高了，只需4.5109秒，由于一個(gè)事務(wù)只需分配一次鎖資源，減少了分配鎖和數(shù)據(jù)庫(kù)聯(lián)接的耗時(shí)。

當(dāng)然，我們可以也使用SqlBulkCopy實(shí)現(xiàn)大量數(shù)據(jù)的寫(xiě)入操作，首先我們創(chuàng)建數(shù)據(jù)行，然后使用SqlBulkCopy的WriteToServer()方法將數(shù)據(jù)行批量寫(xiě)入到表中，具體實(shí)現(xiàn)代碼如下：

/// <summary>
/// Gets the data rows.
/// </summary>
/// <returns></returns>
DataRow[] GetDataRows(int rowCnt)
{
    //// Creates a custom table.
    var dt = new DataTable("jk_users");
    dt.Columns.Add(new DataColumn("user_login", typeof(System.String)));
    dt.Columns.Add(new DataColumn("user_pass", typeof(System.String)));
    dt.Columns.Add(new DataColumn("user_nicename", typeof(System.String)));
    dt.Columns.Add(new DataColumn("user_email", typeof(System.String)));
    dt.Columns.Add(new DataColumn("user_url", typeof(System.String)));
    dt.Columns.Add(new DataColumn("user_registered", typeof(System.DateTime)));
    dt.Columns.Add(new DataColumn("user_activation_key", typeof(System.String)));
    dt.Columns.Add(new DataColumn("user_status", typeof(System.Int32)));
    dt.Columns.Add(new DataColumn("display_name", typeof(System.String)));

    //// Initializes data row.
    var dr = dt.NewRow();
    dr["user_login"] = "JK_RUSH";
    dr["user_pass"] = "D*<1C2jK#-";
    dr["user_nicename"] = "JK";
    dr["user_email"] = "jkhuang@gamil.com";
    dr["user_status"] = 1;
    dr["display_name"] = "JK_RUSH";
    dr["user_url"] = "http://www.rzrgm.cn/rush";
    dr["user_activation_key"] = "347894102386";
    dr["user_registered"] = DateTime.Now;

    //// Creates data row array.
    var dataRows = new DataRow[rowCnt];
    for (int i = 0; i < rowCnt; i++)
    {
        dataRows[i] = dr;
    }

    return dataRows;
}

前面，我們定義了GetDataRows()方法用來(lái)創(chuàng)建數(shù)據(jù)行，首先我們創(chuàng)建了一個(gè)自定義表，給該表添加相應(yīng)的數(shù)據(jù)列，這里我們把數(shù)據(jù)列都命名為對(duì)應(yīng)于表中列名，當(dāng)然，名字可以不一樣，這時(shí)我們就有一個(gè)疑問(wèn)了，那么數(shù)據(jù)庫(kù)如何把自定義數(shù)據(jù)列和表中數(shù)據(jù)列對(duì)應(yīng)起來(lái)呢？其實(shí)，我們需要調(diào)用ColumnMappings.Add方法建立起自定義數(shù)據(jù)列和表中數(shù)據(jù)列的對(duì)應(yīng)關(guān)系，接下來(lái)，我們調(diào)用SqlBulkCopy的WriteToServer()方法將數(shù)據(jù)行寫(xiě)入表中。

//// Creates 10001 data rows. 
var dataRows = GetDataRows(10001);
var sw = Stopwatch.StartNew();

//// Creates a database connection.
using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLCONN2"].ToString()))
{
    conn.Open();
    using (var bulkCopy = new SqlBulkCopy(conn))
    {
        //// Maping the data columns.
        bulkCopy.ColumnMappings.Add("user_login", "user_login");
        bulkCopy.ColumnMappings.Add("user_pass", "user_pass");
        bulkCopy.ColumnMappings.Add("user_nicename", "user_nicename");
        bulkCopy.ColumnMappings.Add("user_email", "user_email");
        bulkCopy.ColumnMappings.Add("user_url", "user_url");
        bulkCopy.ColumnMappings.Add("user_registered", "user_registered");
        bulkCopy.ColumnMappings.Add("user_activation_key", "user_activation_key");
        bulkCopy.ColumnMappings.Add("user_status", "user_status");
        bulkCopy.ColumnMappings.Add("display_name", "display_name");
        bulkCopy.DestinationTableName = "dbo.jk_users";
        //// Insert data into datatable.
        bulkCopy.WriteToServer(dataRows);
    }
    sw.Stop();
}

圖5 數(shù)據(jù)寫(xiě)入時(shí)間

上面，我們通過(guò)事務(wù)和SqlBulkCopy實(shí)現(xiàn)數(shù)據(jù)批量寫(xiě)入數(shù)據(jù)庫(kù)中，但事實(shí)上，每次我們調(diào)用cmd.ExecuteNonQuery()方法都會(huì)產(chǎn)生一個(gè)往返消息，從客戶(hù)端應(yīng)用程序到數(shù)據(jù)庫(kù)中，所以我們想是否存在一種方法只發(fā)送一次消息就完成寫(xiě)入的操作呢？

使用表參數(shù)

如果，大家使用SQL Server 2008，它提供一個(gè)新的功能表變量（Table Parameters）可以將整個(gè)表數(shù)據(jù)匯集成一個(gè)參數(shù)傳遞給存儲(chǔ)過(guò)程或SQL語(yǔ)句。它的注意性能開(kāi)銷(xiāo)是將數(shù)據(jù)匯集成參數(shù)(O(數(shù)據(jù)量))。

現(xiàn)在，我們修改之前的代碼，在SQL Server中定義我們的表變量，具體定義如下：

-- =============================================
-- Author:        JKhuang
-- Create date: 08/16/2012
-- Description:    Declares a user table paramter.
-- =============================================
CREATE TYPE jk_users_bulk_insert AS TABLE (
    user_login varchar(60),
    user_pass varchar(64),
    user_nicename varchar(50),
    user_email varchar(100),
    user_url varchar(100),
    user_activation_key varchar(60),
    user_status int,
    display_name varchar(250)
)

上面，我們定義了一個(gè)表參數(shù)jk_users_bulk_insert，接著我們定義一個(gè)存儲(chǔ)過(guò)程接受表參數(shù)jk_users_bulk_insert，具體定義如下：

-- =============================================
-- Author:        JKhuang
-- Create date: 08/16/2012
-- Description:    Creates a stored procedure, receive
-- a jk_users_bulk_insert argument.
-- =============================================
CREATE PROCEDURE sp_insert_jk_users 
@usersTable jk_users_bulk_insert READONLY 
AS

INSERT INTO jk_users (user_login, user_pass, user_nicename, user_email, user_url, 
user_activation_key, user_status, display_name, user_registered) 

SELECT user_login, user_pass, user_nicename, user_email, user_url, 
user_activation_key, user_status, display_name, GETDATE() 
FROM @usersTable

接下我們?cè)诳蛻?hù)端代碼中，調(diào)用存儲(chǔ)過(guò)程并且將表作為參數(shù)方式傳遞給存儲(chǔ)過(guò)程。

var sw = Stopwatch.StartNew();
using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLCONN2"].ToString()))
{
    conn.Open();
    //// Invokes the stored procedure.
    using (var cmd = new SqlCommand("sp_insert_jk_users", conn))
    {
        cmd.CommandType = CommandType.StoredProcedure;

        //// Adding a "structured" parameter allows you to insert tons of data with low overhead
        var param = new SqlParameter("@userTable", SqlDbType.Structured) { Value = dt };
        cmd.Parameters.Add(param);
        cmd.ExecuteNonQuery();
    }
}

sw.Stop();

現(xiàn)在，我們重新執(zhí)行寫(xiě)入操作發(fā)現(xiàn)寫(xiě)入效率與SqlBulkCopy相當(dāng)。

1.1.3總結(jié)

本文通過(guò)博客系統(tǒng)用戶(hù)表設(shè)計(jì)的例子，介紹我們?cè)谠O(shè)計(jì)過(guò)程中容易犯的錯(cuò)誤和代碼的缺陷，例如：SQL注入、數(shù)據(jù)庫(kù)資源釋放等問(wèn)題；進(jìn)而使用一些常用的代碼優(yōu)化技巧對(duì)代碼進(jìn)行優(yōu)化，并且通過(guò)分析數(shù)據(jù)庫(kù)寫(xiě)入的性能開(kāi)銷(xiāo)（連接時(shí)間、解析器、數(shù)據(jù)庫(kù)連接、約束處理、VARCHAR和磁盤(pán)IO），我們使用存儲(chǔ)過(guò)程、數(shù)據(jù)庫(kù)事務(wù)、SqlBulkCopy和表參數(shù)等方式降低數(shù)據(jù)庫(kù)的開(kāi)銷(xiāo)。