SQL Server 高性能写入的一些经验总结-木庄网络博客

本文整理自网络，侵删。

1.1.1 摘要

在开发过程中，我们不时会遇到系统性能瓶颈问题，而引起这一问题原因可以很多，有可能是代码不够高效、有可能是硬件或网络问题，也有可能是数据库设计的问题。

本篇博文将针对一些常用的数据库性能调休方法进行介绍，而且，为了编写高效的SQL代码，我们需要掌握一些基本代码优化的技巧，所以，我们将从一些基本优化技巧进行介绍。

本文目录

代码中的问题
数据库性能开销
使用存储过程
使用数据库事务
使用SqlBulkCopy
使用表参数

1.1.2 正文

假设，我们要设计一个博客系统，其中包含一个用户表（User），它用来存储用户的账户名、密码、显示名称和注册日期等信息。

由于时间的关系，我们已经把User表设计好了，它包括账户名、密码（注意：这里没有考虑隐私信息的加密存储）、显示名称和注册日期等，具体设计如下：

代码如下:

-- =============================================
-- 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表设计

上面，我们定义了Users表，它包含账户名、密码、显示名称和注册日期等10个字段，其中，ID是一个自增的主键，user_resistered用来记录用户的注册时间，它设置了默认值GETDATE()。

接下来，我们将通过客户端代码实现数据存储到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();

代码中的问题
上面，我们使用再普通不过的ADO.NET方式实现数据写入功能，但大家是否发现代码存在问题或可以改进的地方呢？

首先，我们在客户端代码中，创建一个数据库连接，它需要占用一定的系统资源，当操作完毕之后我们需要释放占用的系统资源，当然，我们可以手动释放资源，具体实现如下：

代码如下:

//// 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资源时抛出异常，那么在它后面的资源SqlConnection将得不到释放。我们仔细想想当发生异常时，可以通过try/catch捕获异常，所以无论是否发生异常都可以使用finally检查资源是否已经释放了，具体实现如下：

代码如下:

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

通过上面的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_key", 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秒（我的机器很破了），这样系统性能还是可以满足许多公司的需求了。

假如，用户请求量增大了，我们还能保证系统能满足需求吗？事实上，我们不应该满足于现有的系统性能，因为我们知道代码的执行效率还有很大的提升空间。

接下来，将进一步介绍代码改善的方法。

阅读剩余部分