当前位置：首页 > 资讯 > 技术文档

SQL Server 高性能写入的一些经验总结

时间：2020-07-02 10:16 编辑：来源：阅读：
扫一扫，手机访问

摘要：SQL Server 高性能写入的一些经验总结

1.1.1 摘要在开发过程中，我们不时会遇到系统性能瓶颈问题，而引起这一问题原因可以很多，有可能是代码不够高效、有可能是硬件或网络问题，也有可能是数据库设计的问题。本篇博文将针对一些常用的数据库性能调休方法进行介绍，而且，为了编写高效的SQL代码，我们需要掌握一些基本代码优化的技巧，所以，我们将从一些基本优化技巧进行介绍。 [b]本文目录 [/b]代码中的问题数据库性能开销使用存储过程使用数据库事务使用SqlBulkCopy 使用表参数 1.1.2 正文假设，我们要设计一个博客系统，其中包含一个用户表（User），它用来存储用户的账户名、密码、显示名称和注册日期等信息。由于时间的关系，我们已经把User表设计好了，它包括账户名、密码（注意：这里没有考虑隐私信息的加密存储）、显示名称和注册日期等，具体设计如下：

[url=http://images.cnblogs.com/cnblogs_com/rush/201208/201208312149171595.png][img]http://files.jb51.net/file_images/article/201208/2012083122354415.png[/img] [/url] 图1 Users表设计上面，我们定义了Users表，它包含账户名、密码、显示名称和注册日期等10个字段，其中，ID是一个自增的主键，user_resistered用来记录用户的注册时间，它设置了默认值GETDATE()。接下来，我们将通过客户端代码实现数据存储到Users表中，具体的代码如下：

[url=http://images.cnblogs.com/cnblogs_com/rush/201208/201208312149346780.png][img]http://files.jb51.net/file_images/article/201208/2012083122354416.png[/img] [/url] 图2 数据写入Users表为了使数据库获得更快的写入速度，我们必须了解数据库在进行写入操作时的主要耗时。 [b]数据库性能开销[/b] 连接时间当我们执行conn.Open()时，首先，必须建立物理通道（例如套接字或命名管道），必须与服务器进行初次握手，必须分析连接字符串信息，必须由服务器对连接进行身份验证，必须运行检查以便在当前事务中登记，等等这一系列操作可能需要一两秒钟时间，如果我们每次执行conn.Open()都有进行这一系列操作是很耗费时间的，为了使打开的连接成本最低，ADO.NET使用称为连接池的优化方法。连接池：减少新连接需要打开的次数，只要用户在连接上调用 Open()方法，池进程就会检查池中是否有可用的连接，如果某个池连接可用，那么将该连接返回给调用者，而不是创建新连接；应用程序在该连接上调用 Close()或Dispose() 时，池进程会将连接返回到活动连接池集中，而不是真正关闭连接，连接返回到池中之后，即可在下一个 Open 调用中重复使用。 [b]解析器的开销[/b] 当我们向SQL Server传递SQL语句INSERT INTO …时，它需要对SQL语句进行解析，由于SQL Server解析器执行速度很快，所以解析时间往往是可以忽略不计，但我们仍然可以通过使用存储过程，而不是直SQL语句来减少解析器的开销。 [b]数据库连接 [/b]为了提供ACID（事务的四个特性），SQL Server必须确保所有的数据库更改是有序的。它是通过使用锁来确保该数据库插入、删除或更新操作之间不会相互冲突（关于数据库的锁请参考这里）。由于，大多数数据库都是面向多用户的环境，当我们对User表进行插入操作时，也许有成千上百的用户也在对User表进行操作，所以说，SQL Server必须确保这些操作是有序进行的。那么，当SQL Server正在做所有这些事情时，它会产生锁，以确保用户获得有意义的结果。SQL Server保证每条语句执行时，数据库是完全可预测的（例如：预测SQL执行方式）和管理锁都需要耗费一定的时间。 [b]约束处理 [/b]在插入数据时，每个约束（如：外键、默认值、SQL CHECK等）需要额外的时间来检测数据是否符合约束；由于SQL Server为了保证每个插入、更新或删除的记录都符合约束条件，所以，我们需要考虑是否应该在数据量大的表中增加约束条件。 [b]Varchar [/b]VARCHAR是数据库常用的类型，但它也可能导致意想不到的性能开销；每次我们存储可变长度的列，那么SQL Server必须做更多的内存管理；字符串可以很容易地消耗数百字节的内存的，如果我们在一个VARCHAR列中设置索引，那么SQL Server执行B-树搜索时，就需要进行O(字符串长度)次比较，然而，整数字段比较次数只受限于内存延迟和CPU频率。 [b]磁盘IO [/b]SQL Server最终会将数据写入到磁盘中，首先，SQL Server把数据写入到事务日志中，当执行备份时，事务日志会合并到永久的数据库文件中；这一系列操作由后台完成，它不会影响到数据查询的速度，但每个事物都必须拥有属于自己的磁盘空间，所以我们可以通过给事务日志和主数据文件分配独立的磁盘空间减少IO开销，当然，最好解决办法是尽可能减少事务的数量。正如大家所看到的，我们通过优化联接时间、解析器的开销、数据库联接、约束处理,、Varchar和磁盘IO等方法来优化数据库，接下来，我们将对前面的例子进行进一步的优化。 [b]使用存储过程[/b] 前面例子中，我们把SQL代码直接Hardcode在客户端代码中，那么，数据库就需要使用解析器解析客户端中SQL语句，所以我们可以改用使用存储过程，从而，减少解析器的时间开销；更重要的一点是，由于SQL是动态执行的，所以我们修改存储过程中的SQL语句也无需重新编译和发布程序。 User表中的字段user_registered设置了默认值（GETDATE()），那么我们通过消除表默认值约束来提高系统的性能，简而言之，我们需要提供字段user_registered的值。接下来，让我们省去User表中的默认值约束和增加存储过程，具体代码如下：

[url=http://images.cnblogs.com/cnblogs_com/rush/201208/201208312149485784.png][img]http://files.jb51.net/file_images/article/201208/2012083122354417.png[/img] [/url] 图3数据写入时间 [b]使用数据库事务 [/b] 想想数据是否可以延长写入到数据库中，是否可以批量地写入呢？如果允许延迟一段时间才写入到数据库中，那么我们可以使用Transaction来延迟数据写入。数据库事务是数据库管理系统执行过程中的一个逻辑单位，由一个有限的数据库操作序列构成。 SQL Server确保事务执行成功后，数据写入到数据库中，反之，事务将回滚。如果我们对数据库进行十次独立的操作，那么SQL Server就需要分配十次锁开销，但如果把这些操作都封装在一个事务中，那么SQL Server只需要分配一次锁开销。

[url=http://images.cnblogs.com/cnblogs_com/rush/201208/201208312150086703.png][img]http://files.jb51.net/file_images/article/201208/2012083122354419.png[/img] [/url] 图5 数据写入时间上面，我们通过事务和SqlBulkCopy实现数据批量写入数据库中，但事实上，每次我们调用cmd.ExecuteNonQuery()方法都会产生一个往返消息，从客户端应用程序到数据库中，所以我们想是否存在一种方法只发送一次消息就完成写入的操作呢？ [b]使用表参数 [/b]如果，大家使用SQL Server 2008，它提供一个新的功能表变量（Table Parameters）可以将整个表数据汇集成一个参数传递给存储过程或SQL语句。它的注意性能开销是将数据汇集成参数(O(数据量))。现在，我们修改之前的代码，在SQL Server中定义我们的表变量，具体定义如下：

[url=http://beginner-sql-tutorial.com/sql-query-tuning.htm]http://beginner-sql-tutorial.com/sql-query-tuning.htm[/url] [2] [url=http://www.dzone.com/links/r/sql_optimization_tipsquestions.html]http://www.dzone.com/links/r/sql_optimization_tipsquestions.html[/url] [3] [url=http://blackrabbitcoder.net/archive/2010/11/11/c.net-little-wonders---a-presentation.aspx]http://blackrabbitcoder.net/archive/2010/11/11/c.net-little-wonders---a-presentation.aspx[/url] [4] [url=http://www.altdevblogaday.com/2012/05/16/sql-server-high-performance-inserts/]http://www.altdevblogaday.com/2012/05/16/sql-server-high-performance-inserts/[/url]

全部评论(0)

上一篇：SQL Select语句完整的执行顺序
下一篇：SQL Server 2008 数据库镜像部署实例之三配置见证服务器

资讯排行榜
更多>>

联系客服

客服电话：
400-000-3129

微信版

扫一扫进微信版

返回顶部