也有可能是数据库设计的题目,本文转自

解析器的支出

当我们向SQL Server传递SQL语句INSERT INTO
…时,它需要对SQL语句进行剖析,由于SQL
Server解析器
推行进度很快,所以解析时间多次是足以忽略不计,但大家依旧可以透过采纳存储过程,而不是直SQL语句来减弱解析器的开销。

  约束处理

  在插入数据时,每个约束(如:外键、默认值、SQL
CHECK等)需要非常的刻钟来检测数据是否顺应约束;由于SQL
Server为了保证每个插入、更新或删除的笔录都适合约束原则,所以,我们需要考虑是否应该在数据量大的表中扩充约束原则。

磁盘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秒。

图片 1图3数目写入时间

本文转自:http://www.cnblogs.com/rush/archive/2012/08/31/2666090.html

  1.1.3 总结

  本文通过博客系统用户表设计的例子,介绍大家在筹划过程中易于犯的荒谬和代码的欠缺,例如:SQL注入、数据库资源自由等题材;进而使用一些常
用的代码优化技巧对代码举行优化,并且通过分析数据库写入的性质开销(连接时间、解析器、数据库连接、约束处理、VARCHAR和磁盘IO),我们利用存
储过程、数据库事务、SqlBulkCopy和表参数等艺术降低数据库的开发。

参考

[1] http://beginner-sql-tutorial.com/sql-query-tuning.htm

[2] http://www.dzone.com/links/r/sql_optimization_tipsquestions.html

[3]
http://blackrabbitcoder.net/archive/2010/11/11/c.net-little-wonders—a-presentation.aspx

[4]
http://www.altdevblogaday.com/2012/05/16/sql-server-high-performance-inserts/

关于作者:

[作者]:JK_Rush从事.NET开发和热衷于开源高性能系统设计,通过博文交流和分享经验,欢迎转载,请保留原文地址,谢谢。 [出处]: http://www.cnblogs.com/rush/ [本文基于]: 署名-非商业性使用 3.0 许可协议发布,欢迎转载,演绎,但是必须保留本文的署名 JK_Rush (包含链接),且不得用于商业目的。如您有任何疑问或者授权方面的协商,请与我联系 。

 

  使用数据库事务

  想想数据是否可以延长写入到数据库中,是否足以批量地写入呢?要是允许延迟一段时间才写入到数据库中,那么我们得以应用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();
}

图片 2图4
数据写入时间

利用数据库事务

思维数据是否可以延长写入到数据库中,是否足以批量地写入呢?假若同意延迟一段时间才写入到数据库中,那么咱们得以应用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();
}

图片 3

图4 数据写入时间

  本文目录

代码中的问题

地点,大家运用再平凡但是的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秒(我的机械很破了),这样系统性能还是可以够满足广大铺面的需求了。

假设,用户请求量增大了,大家还是能保证系统能满意要求呢?事实上,我们不应当知足于现有的系统特性,因为咱们知道代码的进行效用还有很大的提高空间。

接下去,将更为介绍代码改进的不二法门。

图片 4

图2 数码写入Users表

为了使数据库得到更快的写入速度,大家务必了然数据库在展开写入操作时的要紧耗时。

  解析器的开发

  当我们向SQL Server传递SQL语句INSERT INTO
…时,它需要对SQL语句举行剖析,由于SQL
Server解析器
施行进度迅猛,所以解析时间屡屡是可以忽略不计,但我们照样可以因此采纳存储过程,而不是直SQL语句来减弱解析器的支付。

使用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

 

 

 

图5 数据写入时间

地点,大家经过作业和SqlBulkCopy实现数据批量写入数据库中,但实在,每一趟大家调用cmd.ExecuteNonQuery()方法都会生出一个往来新闻,从客户端应用程序到数据库中,所以我们想是不是留存一种办法只发送两次新闻就形成写入的操作呢?

  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
)

图片 6图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();

自律处理

在插入数据时,每个约束(如:外键、默认值、SQL
CHECK等)需要额外的时刻来检测数据是否合乎约束;由于SQL
Server为了保证每个插入、更新或删除的记录都符合约束原则,所以,我们需要考虑是不是合宜在数据量大的表中扩张约束原则。

  磁盘IO

  SQL Server最后会将数据写入到磁盘中,首先,SQL
Server把数据写入到事情日志中,当执行备份时,事务日志汇合并到千古的数据库文件中;这一名目繁多操作由后台完成,它不会潜移默化到数量查询的速度,但各样事物都不可以不具有属于自己的磁盘空间,所以我们可以通过给工作日志和主数据文件分配独立的磁盘空间减弱IO开销,当然,最好解决办法是尽可能减弱事务的数
量。

  正如我们所见到的,大家通过优化联接时间、 解析器的开销、
数据库联网、约束处理,、Varchar和磁盘IO等办法来优化数据库,接下去,我们将对前方的例证举行更加的优化。

数据库性能开销

1.1.1 摘要

  在付出进程中,我们经常会遇上系统特性瓶颈问题,而引起这一题材由来可以多多,有可能是代码不够急迅、有可能是硬件或网络问题,也有可能是数据库设计的题材。

  本篇博文将针对有些常用的数据库性能调休方法举办介绍,而且,为了编制高效的SQL代码,我们需要控制一些中心代码优化的技能,所以,大家将从局部主导优化技术举办介绍。

使用表参数

即便,我们使用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相当。

  数据库性能开销

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
)

图片 7

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

  数据库连接

  为了提供ACID(事务的多少个特征),SQL
Server必须确保所有的数据库更改是有序的。它是经过行使锁来确保该数据库插入、删除或更新操作之间不会相互争论(关于数据库的锁请参考这里)。

  由于,大多数数据库都是面向多用户的环境,当我们对User表举办插队操作时,也许有成千上百的用户也在对User表进行操作,所以说,SQL
Server必须保证那多少个操作是雷打不动展开的。

  那么,当SQL
Server正在做有所这个工作时,它会生出锁,以担保用户拿到有含义的结果。SQL
Server保证每条语句执行时,数据库是全然可预测的(例如:预测SQL执行办法)和治本锁都需要耗费一定的光阴。

拔取存储过程

前方例子中,我们把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秒。

图片 8

图3数目写入时间

  参考

  [1] http://beginner-sql-tutorial.com/sql-query-tuning.htm

  [2]
http://www.dzone.com/links/r/sql_optimization_tipsquestions.html

  [3]
http://blackrabbitcoder.net/archive/2010/11/11/c.net-little-wonders—a-presentation.aspx

  [4]
http://www.altdevblogaday.com/2012/05/16/sql-server-high-performance-inserts/

1.1.3总结

本文通过博客系统用户表设计的事例,介绍我们在统筹过程中易于犯的不当和代码的败笔,例如:SQL注入、数据库资源自由等问题;进而使用部分常用的代码优化技巧对代码进行优化,并且经过分析数据库写入的性能开销(连接时间、解析器、数据库连接、约束处理、VARCHAR和磁盘IO),我们使用存储过程、数据库事务、SqlBulkCopy和表参数等艺术降低数据库的开发。

  使用表参数

  假如,我们使用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分外。

连接时间

当我们实践conn.Open()时,首先,必须建立物理通道(例如套接字或命名管道),必须与服务器进行初次握手,必须分析连接字符串消息,必须由服务器对连年举办身份验证,必须运行检查以便在目前工作中登记,等等

这一名目繁多操作可能需要一两分钟时间,假使我们每一趟执行conn.Open()都有举行这一文山会海操作是很耗费时间的,为了使打开的连天成本低于,ADO.NET使用称为连接池的优化措施。

连接池:减弱新连接需要打开的次数,只要用户在连接上调用
Open()方法,池进程就会检查池中是否有可用的连日,假若某个池连接可用,那么将该连接再次来到给调用者,而不是成立新连接;应用程序在该连接上调用
Close()Dispose()
时,池进程会将连续重回到移动连接池集中,而不是当真关闭连接,连接重临到池中之后,即可在下一个
Open 调用中重复使用。

  连接时间

  当我们履行conn.Open()时,首先,必须树立物理通道(例如套接字或命名管道),必须与服务器举办初次握手,必须分析连接字符串信息,必须由服务器对连接举行身份验证,必须运行检查以便在眼前事务中注册,等等

  这一文山会海操作可能需要一两分钟时间,即便大家每一趟执行conn.Open()都有拓展这一层层操作是很耗费时间的,为了使打开的接连成本低于,ADO.NET使用称为连接池的优化措施。

  连接池:缩小新连接需要开拓的次数,只要用户在连年上调用Open()方法,池进程就会检查池中是否有可用的连接,倘诺某个池连接可用,那么将该连接重返给调用者,而不是创办新连接;应用程序在该连接上调用Close()Dispose()时,池进程会将连续重临到活动连接池集中,而不是真的关闭连接,连接重临到池中之后,即可在下一个Open调用中重复使用。

数据库连接

为了提供ACID(事务的两个性状),SQL
Server必须确保所有的数据库更改是平稳的。它是透过动用锁来担保该数据库插入、删除或更新操作之间不会相互顶牛(关于数据库的锁请参考这里)。

是因为,大多数数据库都是面向多用户的环境,当我们对User表举行插队操作时,也许有成千上百的用户也在对User表举办操作,所以说,SQL
Server必须保证这个操作是一动不动展开的。

这就是说,当SQL
Server正在做有所这一个工作时,它会时有暴发锁,以担保用户得到有含义的结果。SQL
Server保证每条语句执行时,数据库是全然可预测的(例如:预测SQL执行措施)和治本锁都亟待耗费一定的时刻。

  Varchar

  VARCHAR是数据库常用的连串,但它也恐怕造成意外的性能开销;每便我们存储可变长度的列,那么SQL
Server必须做更多的内存管理;字符串可以很容易地消耗数百字节的内存的,如若大家在一个VARCHAR列中设置索引,那么SQL
Server执行B-树搜索时,就需要开展O(字符串长度)次相比,不过,整数字段比较次数只受限于内存延迟和CPU频率。

本文目录

  代码中的问题

  上边,我们应用再常见可是的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秒(我的机械很破了),这样系统性能还能够满意广大店家的要求了。

  若是,用户请求量增大了,大家还可以保证系统能满意要求呢?事实上,大家不应当满意于现有的系统特性,因为我们领略代码的履行效用还有很大的升级空间。

  接下去,将更加介绍代码改进的点子。

图片 9图2
数目写入Users表

  为了使数据库得到更快的写入速度,我们务必了然数据库在举行写入操作时的要害耗时。

Varchar

VARCHAR是数据库常用的体系,但它也说不定造成意外的习性开销;每趟我们存储可变长度的列,那么SQL
Server必须做更多的内存管理;字符串可以很容易地消耗数百字节的内存的,假诺我们在一个VARCHAR列中安装索引,那么SQL
Server执行B-树搜索时,就需要开展O(字符串长度)次相比较,但是,整数字段相比较次数只受限于内存延迟和CPU频率。

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

图片 10图5
数据写入时间

  下边,我们透过业务和SqlBulkCopy实现数据批量写入数据库中,但实际上,每趟我们调用cmd.ExecuteNonQuery()方法都会发出一个来往音讯,从客户端应用程序到数据库中,所以咱们想是不是存在一种形式只发送三遍信息就做到写入的操作呢?

1.1.1 摘要

在付出进程中,我们平日会赶上系统特性瓶颈问题,而滋生这一题材由来可以多多,有可能是代码不够连忙、有可能是硬件或网络问题,也有可能是数据库设计的题材。

本篇博文将针对一些常用的数据库性能调休方法进行介绍,而且,为了编制高效的SQL代码,大家需要了解一些为主代码优化的技巧,所以,我们将从部分主导优化技术举办介绍。

相关文章