亲亲宝宝

109、提高ADO性能的优秀经验
一、概述
　　“性能”这一术语有着几种不同的、差异微妙的含义。当人们谈到某个东西性能多少好时，他们想要表达的意思可能就是在一定的时间之内它完成了多少工作。例如，一个性能好的发动机运行起来更稳定，产生的动力更强大。对于开发小组，你同样也可能应用这个判断标准：一个性能好的开发小组工作时比较安静，而且能够生产出大量高质量的代码。对我来说，性能至少意味着两件事情——我的代码运行起来有多好，我的开发小组和我本人工作效率怎么样。无论哪一方面，本文介绍的技巧都将起到一定的帮助作用：帮助你更快地编写代码，帮助你编写更快的代码——安静地完成这一切，减少这样那样的错误。本文介绍的技巧主要面向ADO，特别是如何通过ADO访问SQL Server。但与此同时，我还将涉及一些适用范围更广的COM技巧，它们适用于你所编写的所有Visual Basic代码。

　　为了了解从哪些SQL Server数据访问代码编写技术、哪些体系、哪些开发习惯可以得到最好的性能，我已经花了不少时间。一些情况下，对于应用的整体性能来说，单一的技术意义很小，除非我们通过循环将性能的改善程度成倍放大。例如，在一个客户机/服务器应用中，当我们不是通过指定ODBC数据源（DSN）的方式连接数据库时，大约能够节省一到二秒的时间。对于应用整体的适用性或性能来说，这部分节省的时间所产生的影响很小。但是，如果我们在一个中间层组件上应用这种技术，这个组件每分钟（或每小时，每天）都要建立和关闭数据库连接数百（甚至数千）次，那么，这种技术将显著地影响系统的性能表现。因此，对于我在这里讨论的每一种技术，请务必考虑这个倍数因子——即，在一定的时间周期内，你的系统将执行同一段代码多少次。

　　当你开始寻求改进性能的方案时，请考虑一下你的应用（组件，或者是ASP代码）大部份的等待和处理时间花在什么地方。如果你发现应用程序把大量的时间花在等待Open或Execute方法执行完成，那么，你应该认真地检查一下服务器端的查询策略。包括ADO在内，所有的数据访问接口等待查询结果的时间都相同。例如，如果你有一个查询，SQL Server需要20秒才能完成它，不论用来执行该查询的是什么接口，没有一种接口能够比其他接口以更快的速度返回结果。虽然有些接口打开连接的速度比较快，有些接口处理结果集的速度比较快，但没有一种接口能够影响数据库引擎编译和执行查询的速度。因此，如果你的查询具有太高的“挑战性”——例如你没有对索引进行优化，你没有使用存储过程，服务器负载过重，或者你要求返回的记录数量太多——那么，世界上没有一种ADO技术能够帮助你提高性能。除非你解决了这些基本的查询问题，否则没有一种性能调整技术能够显著地改善整体性能。SQL Server的Query Analyzer是一个分析查询性能的优秀工具。它能够用图形的方式显示查询的执行过程，并对改进性能的方法提出建议。

　　如果你能够确信查询具有较高的效率，那么，你可以使用本文介绍的技术进一步调整ADO代码的性能。这里介绍的技巧将从各个方面帮助你简化和改进ADO编程，包括：建立和维护连接，构造和提交执行速度更快的查询，提高处理查询结果的效率，等等。

二、建立连接

　　在一个客户机/服务器应用中，我们可以用好几种方法把建立和初始化数据库连接所需要的时间隐藏起来，使得应用程序既能够打开连接，又不需要用户等待应用程序启动。首先，我们可以尝试异步连接。使用异步连接时，ADO启动连接操作之后，不等待连接完成就把控制权返回给应用程序——这样，应用程序就能够接着执行大部份初始化操作，以更快的速度完成form_load事件处理。如果关闭并重新建立连接的时间小于连接池释放连接的时间，那么这个连接实际上是即时的。但在许多情况下（特别是用户数量不多时），让连接保持打开状态更具有现实意义。在中间层组件或ASP页面内部，如果数据库查询多次重复出现，我建议你让Connection对象保持打开状态。

　　另外一个改进连接性能的办法是，避免使用带有DSN的ODBC。在Microsoft，ODBC已经转入了Quick Fix Engineering（QFE，快速修理工程）状态，它意味着：除非发现重大BUG，该公司将不再在ODBC或它的驱动程序上花时间。另外，考虑性能和部署问题时，ODBC DSN也是一个必须关注的问题。DSN必须安装到客户系统上，要求进行注册表查找，与OLE DB连接相比，它建立连接所需要的时间更长——特别是当你用直接编码的方式指定ConnectionString时，这一点尤其突出。从实际效果来看，避免使用DSN降低的系统开销很有限：如果完全取消连接建立过程，对于每个连接，你也许能够剩下二到五秒时间（假设数据库连接池中已经没有连接）。然而，如果你的应用程序需要频繁地建立连接，节省的时间累计起来就很可观了。

　　建立数据库连接的时候，你要选择一个数据提供者。Microsoft建议我们使用OLE DB提供者替代默认的ODBC提供者。对比最新的OLE DB本地提供者和功能类似但较早的ODBC提供者，我感到前者令人不愉快的意外之事较少。但无论是哪种情况，你都应该在决定使用某个新的提供者之前对应用进行完整地测试——代码的性能、支持的功能、行为方式都有可能发生变化。

　　在中间层和ASP中，在保持连接打开的情况下，我们不能（从实践来看）创建出可伸缩的组件——至少在多次调用之间是这样的。一般地，当IIS引用和释放组件、ASP页面的实例时，组件和ASP页面被频繁地装入、丢弃。由于基于ADO的代码每次执行时都必须建立、使用、释放数据库连接，最小化连接复杂程度的策略对性能的提高程度达到了可明显测量的程度。在这些情形下，对于我们连接数据库的速度来说，连接/会话池有着重要的意义。如果你为Command对象的ConnectionString属性指定合适的值（即，每次使用同样的服务器、初始目录、登录ID和其他参数），那么，连接已经打开且处于可用状态的机会很大。如果连接池中能够找到匹配的连接，连接（或重新连接）的时间将接近0（通常小于250 ms）。

　　然而，如果ADO（或VB）代码不释放Connection对象，或者，我们在不同的实例之间改换了ConnectionString，OLE DB必须每次建立一个新的连接。如果出现了这种情况，我们将很快耗尽连接池内可用连接的数量。要确保连接被释放，我们必须在关闭连接之后把Connection对象设置为Nothing。另外，不要在Recordset Open方法中使用ConnectionString，而是以独立的方式打开Connection对象；这样，当我们要关闭Connection对象以及要把它设置成Nothing的时候，引用它就很方便了。

三、构造和提交查询

　　在构造查询的时候，要搞清楚为什么必须这么做、为什么不能那么做是一个很复杂的问题。然而，一些基本的指导方针能够让构造高效查询的过程更加流畅、轻松。一般地，你不应该让查询浪费服务器时间。下面几个技巧能够帮助你构造出更好、更高效的查询。

　　不要强制SQL Server每次执行查询的时候重新编译和构造查询执行计划。避免这种重复操作的一种简单方法是使用带有参数的存储过程。注意尽量不要使用ADO Command对象的Prepare属性——有时它不能正确工作。如果使用存储过程，你还可以通过消除不必要的“受影响行数”返回值进一步提高ADO性能——只需在存储过程中加入SET NOCOUNT ON就可以了。

　　尽量减少与服务器的通信次数。如果你有几个相关的操作要执行，请把它们合并为一个存储过程，或者是一个可以在服务器上作为脚本执行的复合查询。避免使用方法（比如Refresh）和不适当的Parameters集合引用，它们会强制ADO增加额外的服务器通信过程。

　　在客户机/服务器应用中，只构造Command对象一次，而不是每次使用Command对象的时候重新构造。你可以重新设置Command的参数值，然后在需要时执行它。

　　当查询返回的不是一个记录集时，确保使用了adExecuteNoRecords选项，告诉ADO越过所有那些用来接收和构造记录集（Recordset格式）的代码。你可以把adExecuteNoRecords选项传递给Execute方法，或把它作为Command的选项。

　　执行返回简单记录集的存储过程时，不要使用Command对象。所有的存储过程（以及Command对象）可以作为Connection对象的COM方法出现。让存储过程作为Connection对象的方法出现有着显著的性能优势，同时它也简化了代码。尽管这种技术对于那些有Return Status值或Output参数的存储过程没有什么帮助，但对于动作查询（INSERT、DELETE等）以及那些返回一个或多个记录的查询来说，这种技术很有用。把存储过程作为Connection的方法之后，你可以用方法参数的形式传入存储过程的输入参数；如果调用存储过程返回了一个记录集，你可以通过方法调用中最后一个参数引用该Recordset。例如，下面的ADO语句执行一个名为“Fred”的存储过程，Fred存储过程有两个输入参数，返回一个Recordset：

　 　MyConnection.Fred “InputArg1”, 2, myRecordset

　　编写代码的时候，不要寄希望于VB的自动完成功能会把存储过程或Command对象名字视为合法的Connection对象的方法。在正式运行之前，COM不会解析这类名字。

　　除非绝对必要，否则不要返回记录集。当正在执行的查询返回记录时，ADO就会构造一个Recordset对象。构造Recordset对象的开销很大，因此你应该尽量避免使用Recordset对象。注意有时候执行查询虽然返回结果，但不是返回记录。例如，你可以通过Return Status参数返回整数值。另外，你可以返回Output参数来替代需要构造Recordset对象的记录集，SQL Server允许返回的Output参数多达1000个。

　　只要有可能，请用动作查询（INSERT，UPDATE，DELETE和执行这些操作的存储过程）替代可更新的Recordset游标。此时，你应该使用Execute方法和它的adExecuteNoRecords选项，确保ADO能够知道查询不需要构造Recordset对象。

　　除非必要，否则不要请求服务器进行排序。大多数情况下，对于一个适度大小的Recordset对象，当它被发送到客户端之后，排序速度将更快。另外，如果让ADO客户程序排序Recordset中的记录，则客户应用程序能够按照用户选择的次序排序，从而提高了灵活性。

　　在编写查询之前了解索引的结构。创建合适的索引，调整查询的语法以利用这些索引，你将能够提高记录提取的速度。Query Analyzer能够帮助你决定是否有必要添加更多的索引。

　　不要一次性返回太多的记录。很多时候，容量太大的记录集会严重地影响应用程序的性能。只返回那些当前你需要的记录，如果客户程序需要更多的记录，则以后随时提取。通过带有参数的WHERE子句，或者灵活地运用TOP N查询，限制查询的范围。

　　不要返回太多的列。避免使用SELECT *。SELECT *语句告诉SQL Server返回所有的列，不管实际存在的列有多少。只选择那些你需要的列，这样，当有人为表增加了更多的列时，你不会得到大得出奇的结果集。

　　避免使用游标。如果你必须使用游标，那么不要使用那些所需资源数量超过必要的游标类型。如果没有必要，不要要求游标提供滚动、更新和数据缓冲能力。

　　详细地告诉ADO你想要它做些什么。打开Recordset或者构造Command对象时，不要忘了设置CommandType选项。它避免了ADO“猜测”你的意图，你将能够减少与服务器的通信，而且使得代码更加稳定。

　　另外，学习使用诊断工具，测定运行在服务器上的代码和应用程序的代码占用了多少时间——以及这些时间花在哪里。在这方面，SQL Server Profiler是一个宝贵的工具。它能够阐明你的代码在要求服务器做些什么，能够在草率构造的查询中或对于错误选择的命令属性突出显示。另外，Query Analyzer还能够用图示的方式显示出SQL Server将如何执行查询，提出改进查询的建议，帮助你调整查询。Query Analyzer甚至还能够执行它提出的建议（例如，添加或者删除索引），你只需点击一下按钮就可以完成。

四、处理查询结果

　　查询结果记录发送到客户端之后，客户端应用程序可能需要相当可观的时间去处理结果集。每一种体系（客户机/服务器，多层体系中的中间层，以及ASP）都为优化这个阶段的代码提供了相应的技术。下面是几个能够显著改善性能的技巧。

　　我在代码中看到最多的错误之一是：在引用Recordset Field.Value的时候，使用延迟绑定（Late Binding）。由于代码需要频繁地引用Value属性，而且通常要引用的Field对象有很多，本文前面提到的倍数因子将起到重要影响——因此，所有这里介绍的技巧能够显著地改善性能。一些开发者使用延迟绑定技术的原因在于，他们想要明确地标识出SELECT语句选择了哪些行。为了这个目标，许多人使用了用引号包围字符串的做法。例如，为了引用记录集RS字段集合中的“Cows”字段，你可能使用：

　 　RS(“Cows”)或者：RS.Fields(“Cows”).Value

　　后面这种方法显式地引用了记录集内Fields集合中指定成员的Value属性。这种方法要稍微快一点，而且当你把这些代码向Visual Basic.NET迁移时，它的向上兼容性也要好一些。上述方法的一种变化是使用感叹号（!）操作符：

　 　RS!Cows

　　与先行绑定（Early-Binding）相比，采用上述方法时COM进行解析的时间要长得多，这是因为它们强制COM在运行时（而不是在编译时）解析对Value属性的引用，每一次对该对象的引用都要求有一系列类似的、后台进行的查找过程。

　　然而，使用延迟绑定时，不存在代码引用的是哪一个列这类问题。如果你完全按照下列方式编写代码：

　 　RS(0) ‘ 指向第一个列（Fields集合的成员）

　　这时，COM能够在编译时解析Value属性地址，代码的运行速度将加快。但是，只有那些了解查询所返回的列以及返回次序的人能够理解这行代码到底指向了哪一个列。如果开发者不具备控制查询数据源的权限（这是很常见的情况），这种方法可能带来问题。为了确定RS(0)引用了哪一个SELECT列，你必须找出生成该Recordset的是哪一个SELECT语句、搞清楚SELECT语句所返回的各个列。

　　然而，有几种技术允许你既能够实现快速地运行时引用，同时保证代码的可读性。其中一种方法的要求如下：开发者必须创建一个查询所返回列的枚举列表。如果查询被改变，比如返回更多的列，或者列的次序发生变化，开发者必须修改和重新部署枚举列表。遗憾的是，要保持枚举列表与查询的匹配，对于管理者来说是一个有些困难的任务。例如，为了快速、明白地标识出ADO代码引用的是哪一个列，你可以结合下面的SELECT语句和枚举列表：

　 　SELECT CatName, CatType, CatSize from Cats Where…Enum enuCatsQuery CatName CatType CatSizeEnd Enum

　　注意，SELECT语句返回的列与枚举列表中声明的列完全匹配。此后，当你需要引用Recordset的Fields集合时，可以使用下面的代码：

　 　StrMyName = Rs(enuCatsQuery.CatName)

　　按照这种方法，代码不仅具有较好的可读性，而且它仍旧是编译时绑定，代码的运行速度明显加快。

　　然而，要避免延迟绑定，你还可以使用另外一种方法。vbdata-l@peach.ease.lsoft.com列表服务上一场长时间的讨论得出了一种我称之为预先绑定（Prebinding）的方法，它结合了两种技术。当你只需引用Field对象一次时，这种技术没有什么帮助；但在客户机/服务器应用中，预先绑定方法非常理想。使用这种方法时，你要创建多个独立的、命名的Field对象，并把这些对象设置为Recordset对象Fields集合中的成员。编写代码的时候，你首先要为每一个想要使用的字段创建一个命名的Field对象，例如：

　 　Dim fldName as ADODB.FieldDim fldType as ADODB.FieldDim fldSize as ADODB.Field

　　创建这些Field对象需要一定的开销。然而，你应该估量一下，这是一次性的开销，但它却能够戏剧性地改善性能。

　　打开Recordset之后，你只需一次性地把这些命名的Field对象设置为SELECT查询选择出来的列：

　 If fldName is Nothing thenSet fldName = RS!CatNameSet fldType = RS!CatTypeSet fldSize = RS!CatSizeEnd if

　　你可以在这里用引号包围字符串的方法引用列，甚至也可以使用感叹号操作符。由于这里的代码只运行一次，不论使用什么方法，性能的差异都不大。接下来，当你需要引用Field对象（查询之后）时，只需使用预先绑定的变量即可：

　 　strName = fldNamestrType = fldTypestrSize = fldSize

　　这种预先绑定方法的性能甚至比序数引用方式（例如RS(0)）都要好。

五、客户机/服务器、中间层和ASP策略

　　在编写代码的时候，你还必须考虑到其他一些影响性能的因素。其中一些因素与ADO没有什么关系——它们与COM有关。Microsoft最近的一份白皮书指出，在Windows 2000 ASP页面中执行ADO操作（连接，查询，处理）要比调用COM组件执行同样的代码更快。这个结论并不令人奇怪：当我们从VB调用一个外部COM组件（处于当前进程之外的一些代码），访问COM组件以及把控制传递给它时在后台进行的操作复杂得出奇，而且速度很慢。虽然我们没有必要刻意避免调用COM组件去运行ADO代码。但是，我们不应该简单地把多个独立的ADO操作封装成大量的小型COM组件，然后在需要的时候每次都去调用它们。相反，我们应该尽量把全部逻辑封装到一个COM组件里面，使得程序只需一次调用，COM就能够完成大多数（如果不是全部）操作。我相信，你已经发现运行二进制形式的（例如COM组件）ADO代码要比运行ASP之类的解释执行代码要快。因此，你应该寻找一些方法，减少进入COM组件和从COM组件返回所需要的昂贵开销。

　　如果你离不开Command对象，或者不能预先绑定Field对象并在必要时重用，那么你应该考虑避免多余的对象创建操作的技术。在这种情况下，把存储过程作为Connection对象的方法调用有着更重要的意义。另外，用先行绑定的方式引用Field属性也有助于改善性能。记住操作完成后必须进行的清理工作：关闭Connection和Recordset对象，然后把它们设置成Nothing。

　　为了让代码和代码编写者都表现出最好的性能，请记住以下基本规则：利用连接池和异步连接；减少ADO代码和数据库服务器通信的次数；选用一种COM-先行绑定技术；除非必要，尽量避免使用代价昂贵的ADO对象，例如Recordset和Command对象；如有可能，用Return Status和Output参数替代记录集。尽可能地提高查询的效率，如有可能，不要忘了利用存储过程。详细地告诉ADO你想要它做些什么，避免让ADO猜测你的意图——显式地指定ADO CommandType，使用adExecuteNoRecords之类的选项。

　　对于本文所介绍的所有技巧和其他文章提出的编程忠告，我建议你以审视的目光看待它们。我们所做的工作、所编写的代码、所构造的系统，都属于非常复杂的东西，许多不断变化的因素影响着它们。理解了这一点之后，如果你对本文所讨论的某一项技术感兴趣，可以先进行一下试验。如果它确实有效，那么就正式实现它，然后再进行测试。如果这时它仍旧有效，那么恭喜你。如果它不再有效，你得看看是否违反了应用该技术的必要条件。