SQL Server 高性能写入的一些总结
}
通过上面的 finally 方式处理了异常情况是很普遍的，但为了更安全释放资源，使得我们增加了 finally 和 if 语句，那么是否有更简洁的方法实现资源的安全释放呢？
其实，我们可以使用 using 语句实现资源的释放，具体实现如下：
using 语句：定义一个范围，将在此范围之外释放一个或多个对象。
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 语句实现资源的释放，那么是否所有对象都可以使用 using 语句实现释放呢？
只有类型实现了 IDisposable 接口并且重写 Dispose()方法可以使用 using 语句实现资源释放，由于 SqlConnection 和 SqlCommand 实现了 IDisposable 接口，那么我们可以使用 using 语句实现资源释放和异常处理。
在客户端代码中，我们使用拼接 SQL 语句方式实现数据写入，由于 SQL 语句是动态执行的，所以恶意用户可以通过拼接 SQL 的方式实施SQL注入攻击。
对于 SQL 注入攻击，我们可以通过以下方式防御：
正则表达校验用户输入
参数化存储过程
参数化 SQL 语句
添加数据库新架构
LINQ to SQL
接下来，我们将通过参数化 SQL 语句防御 SQL 注入攻击，大家也可以使用其他的方法防御 SQL 注入攻击，具体实现代码如下：
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_k
ey", userActivationKey);
cmd.ExecuteNonQuery();
}
}
上面通过参数化 SQL 语句和 using 语句对代码进行改进，现在代码的可读性更强了，而且也避免了 SQL 注入攻击和资源释放等问题。
接下来，让我们简单的测试一下代码执行时间，首先我们在代码中添加方法 Stopwatch.StartNew()和 Stopwatch.Stop()来计算写入代码的执行时间，具体代码如下：
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();
}
上面，我们往数据库中写入了 10000 条数据，执行时间为 7.136 秒（我的机器很破了），这样系统性能还是可以满足许多公司的需求了。
假如，用户请求量增大了，我们还能保证系统能满足需求吗？事实上，我们不应该满足于现有的系统性能，因为我们知道代码的执行效率还有很大的提升空间。
接下来，将进一步介绍代码改善的方法。
图 2 数据写入 Users 表
为了使数据库获得更快的写入速度，我们必须了解数据库在进行写入操作时的主要耗时。
数据库性能开销
连接时间
当我们执行 conn.Open()时，首先，必须建立物理通道（例如套接字或命名管道），必须与服务器进行初次握手，必须分析连接字符串信息，必须由服务器对连接进行身份验证，必须运行检查以便在当前事务中登记，等等
这一系列操作可能需要一两秒钟时间，如果我们每次执行 conn.Open()都有进行这一系列操作是很耗费时间的，为了使打开的连接成本最低，ADO.NET 使用称为连接池的优化方法。
连接池：减少新连接需要打开的次数，只要用户在连接上调用 Open()方法，池进程就会检查池中是否有可用的连接，如果某个池连接可用，那么将该连接返回给调用者，而不是创建新连接；应用程序在该连接上调用 Close()或 Dispose() 时，池进程会将连接返回到活动连接池集中，而不是真正关闭连接，连接返回到池中之后，即可在下一个 Open 调用中重复使用。
解析器的开销
当我们向 SQL Server 传递 SQL 语句 INSERT INTO …时，它需要对 SQL 语句进行解析，由于SQL Server解析器执行速度很快，所以解析时间往往是可以忽略不计，但我们仍然可以通过使用存储过程，而不是直 SQL 语句来减少解析器的开销。
数据库连接
为了提供ACID（事务的四个特性），SQL Server 必须确保所有的数据库更改是有序的。它是通过使用锁来确保该数据库插入、删除或更新操作之间不会相互冲突（关于数据库的锁请参考这里）。
由于，大多数数据库都是面向多用户的环境，当我们对 User 表进行插入操作时，也许有成千上百的用户也在对 User 表进行操作，所以说，SQL Server 必须确保这些操作是有序进行的。
那么，当 SQL Server 正在做所有这些事情时，它会产生锁，以确保用户获得有意义的结果。SQL Server 保证每条语句执行时，数据库是完全可预测的（例如：预测 SQL 执行方式）和管理锁都需要耗费一定的时间。
约束处理
在插入数据时，每个约束（如：外键、默认值、SQL CHECK 等）需要额外的时间来检测数据是否符合约束；由于 SQL Server 为了保证每个插入、更新或删除的记录都符合约束条件，所以，我们需要考虑是否应该在数据量大的表中增加约束条件。
Varchar
VARCHAR 是数据库常用的类型，但它也可能导致意想不到的性能开销；每次我们存储可变长度的列，那么 SQL Server 必须做更多的内存管理；字符串可以很容易地消耗数百字节的内存的，如果我们在一个 VARCHAR 列中设置索引，那么 SQL Server 执行 B-树搜索时，就需要进行 O(字符串长度)次比较，然而，整数字段比较次数只受限于内存延迟和 CPU 频率。
磁盘 IO
SQL Server 最终会将数据写入到磁盘中，首先，SQL Server 把数据写入到事务日志中，当执行备份时，事务日志会合并到永久的数据库文件中；这一系列操作由后台完成，它不会影响到数据查询的速度，但每个事物都必须拥有属于自己的磁盘空间，所以我们可以通过给事务日志和主数据文件分配独立的磁盘空间减少 IO 开销，当然，最好解决办法是尽可能减少事务的数量。
正如大家所看到的，我们通过优化联接时间、 解析器的开销、 数据库联接、约束处理,、Varchar 和磁盘 IO 等方法来优化数据库，接下来，我们将对前面的例子进行进一步的优化。
使用存储过程
前面例子中，我们把 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 实现大量数据的写入操作，首先我们创建数据行，然后使用 SqlBulkCopy 的 WriteToServer()方法将数据行批量写入到表中，具体实现代码如下：
/// <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.cnblogs.com/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()方法用来创建数据行，首先我们创建了一个自定义表，给该表添加相应的数据列，这里我们把数据列都命名为对应于表中列名，当然，名字可以不一样，这时我们就有一个疑问了，那么数据库如何把自定义数据列和表中数据列对应起来呢？其实，我们需要调用 ColumnMappings.Add 方法建立起自定义数据列和表中数据列的对应关系，接下来，我们调用 SqlBulkCopy 的 WriteToServer()方法将数据行写入表中。
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 数据写入时间
上面，我们通过事务和 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
接下我们在客户端代码中，调用存储过程并且将表作为参数方式传递给存储过程。
评论