一句话让程序运行速度提高了几百倍(recordset的CursorLocation属性)

来源:www.accessoft.com 点击数:18570 　 | 收藏 | 复制

时　间:2012-09-01 08:24:41
作　者:风行(转载) ID：16058 城市：江阴
摘　要:因工作需要，用EXCEL+ADO+SQL SERVER编写了一个加班工资录入的小程序。写好后进行了一些测试，因测试数据不多，没发现有什么问题。这个月正式用这个程序来计算加班工资并上传，一共1000多条记录，运行上传，花了很长时间程序还是没有响应，开始以为是程序有问题，调试后发现程序本身并没有失去响应，只是速度非常慢，测试了一下，更新10条记录平均一下要3秒，这样1000多条记录的话差不多要5分钟，于是开始查找原因。
正　文:

先简单说一下程序工作的方法，因要连接到SQL SERVER数据库，所以上传附件也不是太能说明问题，而且最终发现解决问题的关键也不需要上传附件。我的程序使用ADO建立一个connection对象cnGzb和一个recordset对象rsGzb，通过SQL语句将数据读入rsGzb，加班信息存放在一张工作表中，遍历每条加班数据，将加班工资更新rsGzb中相应的记录（使用recordset的filter属性根据工号进行筛选），最后用recordset的updatebatch方法更新SQL Server数据库表中的相应字段。

首先我打算先找出哪些代码影响了速度，我先把觉得可能影响速度的代码注释掉，再运行，最终发现影响速度的是recordset的filter属性，可能是filter的速度比较慢而的引起的，于是就想是否能找到一个更快的查找方法，发现ADO中还有一个find方法。修改代码为find后速度还是没有太大的改变，郁闷...

怎么办呢？突然想起以看过一遍关于优化SQL查询的文章，里面对SQL查询优化有一个比较详细的说明，特别比较了在已建立索引的字段上进行查询与未建立索引字段上查询速度的差别，想起一句话，“所有快速查找都是基于排序的”，于是马上修改代码，添加对rsGzb中的工号字段进行排序代码（rsGzb.Sort = "gh ASC"），运行，出错（运行时错误3251：当前提供程序不支持排序或过滤所必须的界面），查看sort属性的帮助，发现里面有一条说明“此属性要求将 CursorLocation 属性设置为 adUseClient。”，于是添加代码(cnGzb.CursorLocation = adUseClient)，再运行。太爽了，1秒内就完成了操作，太有成就感了！\(^O^)/
没想到排序可以把查找的数据提高到这么恐怖的程度。太厉害了。

马上想到把这个经过到坛子里来发贴，和大家分享一下。
为了能很好的表达这个意思，也让大家感觉一下前后的差别，于是想到利用recordset的save方法把recordset保存到文件中，这样就可以脱开SQLSERVER，大家就可以在没有数据库连接的情况下进行测试了。修改代码，保存recrodset。完成。

进行测试，把排序的代码注释掉，运行。咦，不到1秒就完成了，怎么回事，难道注销的代码行搞错了，反复检查发现排序的代码的确是注释掉了，反复测试发现似乎排序对程序的运行影响不大，那到底是怎么回事呢？

想了很久终于明白了，提高速度的那行代码是（cnGzb.CursorLocation = adUseClient），
CursorLocation属性是一个connect和recordset对象都有的一个属性，该属性表示如何存储查询结果，默认值为AdUseServer。若不对recordset的CursorLocation进行设置，则recordset将继承connection对象的CursorLocation值。

在默认情况下recordset对象对于查询的结果由OLE DB提供程序或数据库来管理，简单理解为recordset的查询结果存放于服务器端，而且处理这些查询结果的过程要通过OLE DB或数据库来完成而不是通过ADO来完成，这时候ADO更象一个任务派发者，指挥OLE DB或数据库来完成工作，这样一来，一个命令下达后，影响这个命令执行的因素就很多了，如OLE DB或数据库的响应速度，网络连接等等，我当时由于数据库在其他的服务器上，因为可能网络连接引起的速度变慢可以也是其中的一个因素。当把CursorLocation设置为adUseClient时，查询结果直接存放于recordset的临时表中（本地），由ADO直接对recordset进行操作，这时前面的影响因素就没有了，处理的速度直接取决于本机速度。这就是我所碰到的问题的关键。

把这个经过记录下来是为了和大家分享一下我从发现问题到分析问题到解决问题的过程，可能对大家有所帮助。

下面就是程序中的那几句关键代码(第1名和第3句在这儿的作用相同，可以只写一句）
cnGzb.CursorLocation = adUseClient
Set rsGzb = New ADODB.Recordset
rsGzb.CursorLocation = adUseClient 'CursorLocation对于关闭的recordset对象可读可写，对于打开的recordset对象只读
rsGzb.Open sSqlCommand, cnGzb, adOpenKeyset, adLockBatchOptimistic

Access软件网官方交流QQ群 (群号:483923997) 　　　　　　Access源码网店

【打印此文】【关闭窗口】