95992828九五至尊2

高质量写入的部分总括882828九五至尊手机版

三月 1st, 2019  |  882828九五至尊手机版

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

SQL Server 高质量写入的一对总计

1.1.1 摘要

在支付进度中,大家平常会遇上系统特性瓶颈难点,而滋生这一难点原因能够多多,有可能是代码不够飞快、有大概是硬件或网络难题,也有或然是数据库设计的题材。

本篇博文将针对一些常用的数据库质量调休方法进行介绍,而且,为了编写制定高效的SQL代码,大家要求了解一些基本代码优化的技巧,所以,大家将从局地着力优化技术进行介绍。

1.1.1 摘要

  在支付进度中,我们平时会赶上系统品质瓶颈难题,而引起这一难点由来能够多多,有可能是代码不够急速、有恐怕是硬件或互联网难点,也有可能是数据库设计的难题。

  本篇博文将对准有的常用的数据库质量调休方法开始展览介绍,而且,为了编写制定高效的SQL代码,我们供给控制一些着力代码优化的技能,所以,大家将从一些为主优化技术举办介绍。

正文目录

  本文目录

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
)

882828九五至尊手机版 1

图1 Users表设计

上边,我们定义了Users表,它富含账户名、密码、呈现名称和挂号日期等1一个字段,当中,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();

  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
)

 

882828九五至尊手机版 2图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秒(小编的机械很破了),那样系统品质还能够满足广大商店的供给了。

若果,用户请求量增大了,大家还能够保障系统能满意要求呢?事实上,大家不应当满足于现有的系统天性,因为我们知道代码的实践作用还有一点都不小的擢升空间。

接下去,将越来越介绍代码改革的措施。

882828九五至尊手机版 3

图2 多少写入Users表

为了使数据库获得更快的写入速度,大家必须询问数据库在进展写入操作时的最首要耗费时间。

  代码中的难点

  上边,大家利用再常见可是的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();
}

 

  上边,大家往数据库中写入了一千0条数据,执行时间为
7.136秒(笔者的机器很破了),那样系统品质还是得以满足众多店铺的要求了。

  假使,用户请求量增大了,我们还是可以保证系统能满意供给吗?事实上,大家不应有满意于现有的连串质量,因为大家通晓代码的实践效率还有不小的升级换代空间。

882828九五至尊手机版,  接下去,将特别介绍代码改进的法门。

882828九五至尊手机版 4图2
数据写入Users表

  为了使数据库获得更快的写入速度,我们务必明白数据库在拓展写入操作时的要紧耗费时间。

数据库品质开支

  数据库品质开销

连接时间

当大家实践conn.Open()时,首先,必须建立物理通道(例如套接字或命名管道),必须与服务器进行初次握手,必须分析连接字符串新闻,必须由服务器对连日举行身份验证,必须运转检查以便在现阶段工作中登记,等等

这一两种操作恐怕必要一两秒钟时间,即使我们每回执行conn.Open()都有实行这一星罗棋布操作是很耗时的,为了使打开的连年成本低于,ADO.NET使用称为连接池的优化措施。

连接池:收缩新连接需求开拓的次数,只要用户在接二连三上调用
Open()方法,池进度就会检查池中是或不是有可用的连接,假若有个别池连接可用,那么将该连接重返给调用者,而不是创设新连接;应用程序在该连接上调用
Close()Dispose()
时,池进度会将接连重返到运动连接池集中,而不是的确关闭连接,连接再次来到到池中之后,即可在下1个Open 调用中重复使用。

  连接时间

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

  这一密密麻麻操作或许供给一两分钟时间,尽管我们每趟执行conn.Open()都有举办这一名目繁多操作是很耗时的,为了使打开的连日开支最低,ADO.NET使用称为连接池的优化措施。

  连接池:收缩新连接必要开辟的次数,只要用户在连接上调用Open()方法,池进度就会检查池中是还是不是有可用的接连,借使某些池连接可用,那么将该连接再次回到给调用者,而不是创建新连接;应用程序在该连接上调用Close()Dispose()时,池过程会将连续重临到活动连接池集中,而不是实在关闭连接,连接再次来到到池中之后,即可在下二个Open调用中重复使用。

解析器的支付

当大家向SQL Server传递SQL语句INSE汉兰达T INTO
…时,它供给对SQL语句举行分析,由于SQL
Server解析器
实践进程急速,所以解析时间往往是足以忽略不计,但大家还能经过应用存款和储蓄进程,而不是直SQL语句来收缩解析器的开支。

  解析器的付出

  当大家向SQL Server传递SQL语句INSEOdysseyT INTO
…时,它供给对SQL语句实行剖析,由于SQL
Server解析器
实践进度快捷,所以解析时间屡屡是能够忽略不计,但我们照样能够由此采取存款和储蓄进程,而不是直SQL语句来缩短解析器的支出。

数据库连接

为了提供ACID(事务的六本性子),SQL
Server必须保障全数的数据库更改是不变的。它是经过应用锁来保障该数据库插入、删除或更新操作之间不会相互争论(关于数据库的锁请参考这里)。

是因为,一大56%据库都是面向多用户的环境,当我们对User表实行扦插操作时,只怕有成千上百的用户也在对User表举行操作,所以说,SQL
Server必须保障这个操作是雷打不动展开的。

那么,当SQL
Server正在做有所那个业务时,它会产生锁,以保证用户得到有意义的结果。SQL
Server保障每条语句执行时,数据库是截然可预测的(例如:预测SQL执行措施)和管理锁都亟需消耗一定的年月。

  数据库连接

  为了提供ACID(事务的八个天性),SQL
Server必须保障全部的数据库更改是平稳的。它是因此选拔锁来担保该数据库插入、删除或更新操作之间不会相互冲突(关于数据库的锁请参考这里)。

  由于,第一次全国代表大会52%据库都是面向多用户的环境,当大家对User表进行插队操作时,可能有成千上百的用户也在对User表实行操作,所以说,SQL
Server必须保证这个操作是上行下效进展的。

  那么,当SQL
Server正在做有所这么些业务时,它会爆发锁,以保障用户得到有意义的结果。SQL
Server保障每条语句执行时,数据库是一点一滴可预测的(例如:预测SQL执行情势)和治本锁都亟待开销一定的年华。

自律处理

在插入数据时,各个约束(如:外键、暗中认可值、SQL
CHECK等)须要额外的日子来检查和测试数据是还是不是吻合约束;由于SQL
Server为了保障每一种插入、更新或删除的记录都适合约束原则,所以,我们需求考虑是否应该在数据量大的表中扩展约束原则。

  约束处理

  在插入数据时,各种约束(如:外键、默许值、SQL
CHECK等)要求额外的大运来检查和测试数据是还是不是相符约束;由于SQL
Server为了保证每一个插入、更新或删除的笔录都符合约束原则,所以,大家必要考虑是否合宜在数据量大的表中扩大约束原则。

Varchar

VA索罗德CHA昂科威是数据库常用的项目,但它也恐怕导致意外的性质费用;每一回大家存款和储蓄可变长度的列,那么SQL
Server必须做越来越多的内部存款和储蓄器管理;字符串可以很不难地消耗数百字节的内部存储器的,假使我们在一个VACRUISERCHATiguan列中装置索引,那么SQL
Server执行B-树搜索时,就要求展开O(字符串长度)次相比较,可是,整数字段相比较次数只受限于内部存款和储蓄器延迟和CPU频率。

  Varchar

  VA索罗德CHA帕杰罗是数据库常用的类型,但它也可能导致意外的习性成本;每一次大家存储可变长度的列,那么SQL
Server必须做越来越多的内部存款和储蓄器管理;字符串能够很不难地消耗数百字节的内部存款和储蓄器的,假诺大家在二个VA奥迪Q5CHAQashqai列中设置索引,那么SQL
Server执行B-树搜索时,就必要进行O(字符串长度)次比较,可是,整数字段对比次数只受限于内部存款和储蓄器延迟和CPU频率。

磁盘IO

SQL Server最后会将数据写入到磁盘中,首先,SQL
Server把数据写入到事情日志中,当执行备份时,事务日志会见并到永远的数据库文件中;这一多元操作由后台实现,它不会影响到多少查询的速度,但每种事物都不可能不怀有属于自身的磁盘空间,所以大家能够经过给业务日志和主数据文件分配独立的磁盘空间减弱IO花费,当然,最好消除办法是尽或者裁减事务的数目。

正如我们所见到的,大家经过优化联接时间、 解析器的付出、
数据库联网、约束处理,、Varchar和磁盘IO等艺术来优化数据库,接下去,我们将对前方的例子进行特其余优化。

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

882828九五至尊手机版 5

图3多少写入时间

  使用存款和储蓄进程

  前边例子中,大家把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秒。

882828九五至尊手机版 6图3数据写入时间

行使数据库事务

合计数据是或不是足以拉开写入到数据库中,是还是不是能够批量地写入呢?假如同意延迟一段时间才写入到数据库中,那么大家能够运用Transaction来延缓数量写入。

数据库事务是数据库管理体系实施进度中的2个逻辑单位,由三个星星的数据库操作类别构成。
SQL Server确定保障工作执行成功后,数据写入到数据库中,反之,事务将回滚。

只要大家对数据库进行1八次独立的操作,那么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();
}

882828九五至尊手机版 7

图4 数据写入时间

  使用数据库事务

  想想数据是还是不是足以拉开写入到数据库中,是不是能够批量地写入呢?固然允许延迟一段时间才写入到数据库中,那么我们能够动用Transaction来推迟多少写入。

  数据库事务是数据库管理种类进行进程中的二个逻辑单位,由2个个其余数据库操作连串构成。
SQL Server确定保证工作执行成功后,数据写入到数据库中,反之,事务将回滚。

  借使我们对数据库进行14次独自的操作,那么SQL
Server就要求分配十二回锁开支,但若是把这一个操作都封装在贰个事务中,那么SQL
Server只须要分配1回锁费用。

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

 

882828九五至尊手机版 8图4
数据写入时间

使用SqlBulkCopy

通过使用工作封装了写入操作,当大家再一次运转代码,发现数目写入的进度大大进步了,只需4.5109秒,由于2个事务只需分配二次锁能源,减弱了分红锁和数据库联网的耗费时间。

自然,大家能够也运用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();
}

882828九五至尊手机版 9

 

 

 

图5 数据写入时间

下面,我们经过业务和SqlBulkCopy完毕数据批量写入数据库中,但实在,每一次大家调用cmd.ExecuteNonQuery()方法都会产生八个来往音讯,从客户端应用程序到数据库中,所以大家想是还是不是存在一种办法只发送贰回新闻就形成写入的操作呢?

  使用SqlBulkCopy

  通过选取工作封装了写入操作,当大家再度运转代码,发现数目写入的进程大大提升了,只需4.5109秒,由于一个工作只需分配2遍锁财富,收缩了分配锁和数据库联网的耗费时间。

  当然,大家得以也采纳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();
}

 

882828九五至尊手机版 10图5
数据写入时间

  上边,大家因而作业和SqlBulkCopy达成数据批量写入数据库中,但实际上,每回大家调用cmd.ExecuteNonQuery()方法都会发出一个往返音讯,从客户端应用程序到数据库中,所以大家想是还是不是留存一种情势只发送二回信息就落成写入的操作呢?

运用表参数

一经,大家使用SQL Server 二〇〇八,它提供多少个新的机能表变量(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 二零一零,它提供二个新的成效表变量(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,接着大家定义3个存储进度接受表参数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.3总结

本文通过博客系统用户表设计的例子,介绍大家在统一筹划进度中易于犯的失实和代码的毛病,例如:SQL注入、数据库资源自由等题材;进而使用部分常用的代码优化技巧对代码进行优化,并且经过分析数据库写入的属性费用(连接时间、解析器、数据库连接、约束处理、VA安德拉CHATiguan和磁盘IO),大家利用存款和储蓄进度、数据库事务、SqlBulkCopy和表参数等办法下跌数据库的开发。

  1.1.3 总结

  本文通过博客系统用户表设计的例证,介绍大家在计划进度中易于犯的荒唐和代码的弱项,例如:SQL注入、数据库财富自由等题材;进而使用部分常用的代码优化技巧对代码进行优化,并且经过分析数据库写入的品质源消费用(连接时间、解析器、数据库连接、约束处理、VA奥迪Q7CHA奥迪Q3和磁盘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 (包含链接),且不得用于商业目的。如您有任何疑问或者授权方面的协商,请与我联系 。

 

  参考

  [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/

相关文章

Your Comments

近期评论

    功能


    网站地图xml地图