sql server高效能寫入

標籤：des   blog   http   使用   os   io   資料   ar   

　　使用預存程序

　　前面例子中，我們把SQL代碼直接Hardcode在用戶端代碼中，那麼，資料庫就需要使用解析器解析用戶端中SQL語句，所以我們可以改用使 用預存程序，從而，減少解析器的時間開銷；更重要的一點是，由於SQL是動態執行的，所以我們修改預存程序中的SQL語句也無需重新編譯和發布程式。

User表中的欄位user_registered設定了預設值（GETDATE()），那麼我們通過消除表預設值約束來提高系統的效能，簡而言之，我們需要提供欄位user_registered的值。

接下來，讓我們省去User表中的預設值約束和增加預存程序，具體代碼如下：

-- =============================================-- 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)ASBEGIN    SET NOCOUNT ON;-- The stored procedure allows SQL server to avoid virtually all parser workINSERT 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

上面我們定義了預存程序SP_Insert_jk_users向表中插入資料，當我們重新執行代碼時，探索資料插入的時間縮短為6.7401秒。

圖3資料寫入時間

　　使用資料庫事務

　　想想資料是否可以延長寫入到資料庫中，是否可以批量地寫入呢？如果允許延遲一段時間才寫入到資料庫中，那麼我們可以使用Transaction來延遲資料寫入。

資料庫事務是資料庫管理系統執行過程中的一個邏輯單位，由一個有限的資料庫操作序列構成。 SQL Server確保事務執行成功後，資料寫入到資料庫中，反之，事務將復原。

如果我們對資料庫進行十次獨立的操作，那麼SQL Server就需要分配十次鎖開銷，但如果把這些操作都封裝在一個事務中，那麼SQL Server只需要分配一次鎖開銷。

    //// 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 資料寫入時間

　　使用SqlBulkCopy

　　通過使用事務封裝了寫入操作，當我們重新運行代碼，探索資料寫入的速度大大提高了，只需4.5109秒，由於一個事務只需分配一次鎖資源，減少了分配鎖和資料庫聯結的耗時。

當然，我們可以也使用SqlBulkCopy實現大量資料的寫入操作，具體實現代碼如下：

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 資料寫入時間

　　上面，我們通過事務和SqlBulkCopy實現資料批量寫入資料庫中，但事實上，每次我們調用cmd.ExecuteNonQuery()方法都會產生一個往返訊息，從用戶端應用程式到資料庫中，所以我們想是否存在一種方法只發送一次訊息就完成寫入的操作呢？

　　使用表參數

　　如果，大家使用SQL Server 2008，它提供一個新的功能表變數（Table Parameters）可以將整個表資料彙集成一個參數傳遞給預存程序或SQL語句。它的注意效能開銷是將資料彙集成參數(O(資料量))。

現在，我們修改之前的代碼，在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))

上面，我們定義了一個表參數jk_users_bulk_insert，接著我們定義一個預存程序接受表參數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 ASINSERT 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

接下我們在用戶端代碼中，調用預存程序並且將表作為參數方式傳遞給預存程序。

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();

現在，我們重新執行寫入操作發現寫入效率與SqlBulkCopy相當。