ADO+Access更新、插入速度优化

zhongchangliang

小鸡无用 发表于 2018-5-9 22:32
where ID is null 非常耗费资源。建议 ID 值默认为0 where ID =0 。
update 不要直接使用单元格，全表存入 ...

谢谢，where ID is null 在过程AddNew里面，现在主要是update 语句慢，对于“全表存入数组，用循环方式逐条更新”，我会测试下，非常感谢

zhongchangliang

貌似要沉啊，自己顶一下
不知哪位老师还有高见？

ivccav

zhongchangliang 发表于 2018-5-10 11:49
貌似要沉啊，自己顶一下
不知哪位老师还有高见？

上面那位小哥的说法是错的，where ID is null 不会影响性能，不用Null也不会提高性能，只不过允许Null的字段

需要多一个字节标识是否为Null，浪费空间而已（如果整个字段有很多Null，才会影响性能）。任何数据库都会
优化有Null字段的查询。影响查询速度的是索引，没有索引就需要全表扫描，速度可相差几十倍上百倍。我没法

打开你的数据库文件，不知你在ID字段是否有索引。我这10多年的老电脑，5万行的数据库，更新也是极快的，

因为WHERE子句中要比较的字段是自动编号的ID，数据库可直接定位到需要的行，那个速度真叫快。

建议：

1.建立公共连接对象，减少每次更新或加入数据都独立创建对象；

2.看是否有索引，如果没有索引，必须建立索引。如果是关键字段，建议必填，且不要有NULL值

as42065300

为什么不直接使用记录集直接更新和插入？

ivccav

as42065300 发表于 2018-5-10 13:09
为什么不直接使用记录集直接更新和插入？

用记录集update一样要花时间，用循环循环几十万数据很慢的。直接execute一次性更新没有问题。

我不明白的是，楼主说在”汇总表“中一年积累上万行数据，如果是这样的话，那么

这一万行数据每天都要全部刷新一遍。这就不正常了，一年才1万行，一天就三四十行数据，

每天都要刷新这1万行数据，而不管数据是否有更改，思路都错了。

jiangxiaoyun

ivccav 发表于 2018-5-10 13:37
用记录集update一样要花时间，用循环循环几十万数据很慢的。直接execute一次性更新没有问题。

我不明 ...

即使1万行，效率也不会差啊，只是一万行而已。o(∩_∩)o

ivccav

jiangxiaoyun 发表于 2018-5-10 13:46
即使1万行，效率也不会差啊，只是一万行而已。o(∩_∩)o

ADO数据集使用update（或用批更新）时一样要转化成数据库能识别的SQL语句再执行，
跟直接在ADO的connection对象执行SQL语句没有区别啊。

1万行的确不多，insert三万行数据也就一两秒时间。但如果ID列没有索引，用where子句作为

条件更新就慢了。我的EXCEL2007没法打开楼主的access，运行不了代码，不好评论。

zhongchangliang

ivccav 发表于 2018-5-10 13:37
用记录集update一样要花时间，用循环循环几十万数据很慢的。直接execute一次性更新没有问题。

我不明 ...

因为是生产排产的数据，某些订单可能会持续一两个月才能做完，不确定哪天会改哪条数据

每天都刷新所以数据的确不合适，我也考虑过
想法1：用工作表Change事件，实现单元格更改后直接update
想法2：执行update之前先选择需要上传的数据区域，计算选择区域的起始行、结束行
           只更新选择区域所在行的数据
但是不知道如何能实现，望赐教。

ivccav

zhongchangliang 发表于 2018-5-10 14:23
因为是生产排产的数据，某些订单可能会持续一两个月才能做完，不确定哪天会改哪条数据

每天都刷新所以 ...

你这样每次都刷新上万行数据，几十万个单元格，效率太低。

我的做法是这样的：订单数据全部存储在数据库中，需要更改内容的时候，在Excel中用窗体+Listview只把需要的数据项导出到Excel，修改之后，再更新回数据库，每天也就200多行的修改量。数据库有5万行(只保存2年数据)。

我把半年的数据读到数组中，然后用订单号和产品号作为条件，用Like循环数组模糊匹配。因为我认为超过50条的查询结果没有意义，很难在这么多结果中找到想要的行，所以对于同一个条件，我循环到20条就跳出循环，因此循环数组也没有任何卡顿。且是动态匹配，每次输入一个字符，都会把结果缩小，一般输入5个字符就只剩下3个选项了，效率很高。

因为只加载半年数据，在查询不到结果时，我还加了一个按钮，可根据输入的内容单独查询整个数据库。订单号和产品号在数据库中都有索引，单独查询也很快，局域网内一两秒就差不多了。

小鸡无用

今天再看了下。总体感觉不是vba语法的问题，而是解决问题思路的问题。
建议所有计算，修改，新增，删除都在excel中进行，assess只作为单纯的储存数据库。
1.assess 存在计算项，这个可以转移到excel进行，数据库只存记录。
2.跟新一万条数据完全没有必要。
excel只作为操作界面，每次从数据库导出最新数据。
如果有修改原有数据，通过VBA事件监视excel数据变动，更新相应条目相应字段即可（跟新量大幅度缩小）。