sql server高性能写入
时间:2022-03-13 22:52
使用存储过程
前面例子中,我们把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秒。
图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高性能写入,布布扣,bubuko.com