sql server高性能写入

  使用存储过程

  前面例子中,我们把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)
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

上面我们定义了存储过程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 
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

接下我们在客户端代码中,调用存储过程并且将表作为参数方式传递给存储过程。

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相当。

sql server高性能写入,古老的榕树,5-wow.com

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。