Excel SQL查询近期变慢的原因测试

xrspook

两年前开始用ADO+SQL+VBA做多个Excel文件的数据汇总。一直相安无事，除了有些时候不知道因为什么，平时能运行的VBA会卡死，换一个电脑或者重启就好了，那个情况想再现可能再现不了，但不知道什么时候就冒出来，但过了那么一两个月以后几乎我就没遇到过了，怀疑是windows某次升级带入的某个bug？

昨天下午（2025-04-09）发现用了2年的查询突然很慢。因为查询的数据不多，所以正常情况下应该1秒内出结果，但实际上却花费了24秒。多次重复测试，同样的查询文件、同样的源文件、同样在Microsoft 365之下测试，都需要24秒，这很不正常。上个月底和这个月头使用的时候是正常的。为什么突然就抽风了呢？？？

win10系统上周没有升级过，Microsoft 365好像在这周初的时候打开Excel时被强制升级了一次。出现了不正常的24秒，我把win10和Microsoft 365都手动升级到最新版本，其中Microsoft 365没有用beta频道，现在的版本是Microsoft 365MSO (版本 2503 Build 16.0.18623.20178) 64 位。升级到了最新版本，问题依旧。

查询文件没改过脚本，源文件一再检查没有奇怪的错误结构，怎么突然就慢了这么多呢？虽然也能汇总出我想要的结果，但使用体验非常差。我翻出了去年的查询文件和源文件，发现以前的确1秒内出结果的东西现在同样需要24秒，再次验证了当前我正在使用的查询文件和源文件的确不是因为手贱更改了某些东西的原因而出现这个毛病。

那个之前一秒内出结果的汇总页面是多个查询汇总的结果。在不知道是谁出了问题的时候，我只好搭个脚手架，一个个测试，让我意外的是，让查询从1秒变成24秒的居然是一个把查询文件工作表里的一个超级表转化为SQL数据的操作。数据很少，只有1个字段，2条数据，相比于其它跨表数据，这实在太简单了。排除了这一条后，其它数据很多的跨表汇总全部都在1秒内出结果。

因为我日常使用的ADO+SQL+VBA都是跨表的，有些查询引用的Excel文件完全是外部的，但因为设计的时候图方便懒惰操作，一些日期表之类的索引我放在了查询文件xlsm里。当我把自己的xlsm查询都试了一圈之后发现，好像真的是只有在查询时用到了查询文件xlsm里超级表转化为SQL数据，才会让查询变慢。有个查询文件xlsm内部引用的超级表对应的SQL数据有4个字段，那个之前只需要0.5秒就能完成查询的东西现在变成需要97秒，约等于24*4，所以这个变慢的程度还和字段多少有关？

我隐隐觉得这个ADO+SQL+VBA变慢的问题和查询时使用了查询文件xlsm数据转换有关。

接下来，针对内部文件（查询文件xlsm）和外部文件（Excel文件，随便什么格式，这次我用来测试的文件后缀为xlsm，但里面没有VBA），我进行了个排列组合的ADO+SQL+VBA测试。

每个宏里面都引用了2个文件，第一个是只是用来建立cnn连接，第二个才是SQL查询及输出数据的主体。排列组合分别称呼为内内、内外、外外、外内。因为内内和外外两条引用实际上对应同一个东西，所以我进一步把测试细分为内内1、内内2、内外，外外1，外外2，外内共6种情况。

内内1

1. strCnn = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.FullName
   
2. cnn.Open strCnn
   
3. strPath_date = "[Excel 12.0;DATABASE=" & ThisWorkbook.FullName & "]." '日期表

复制代码

内内2

1. strCnn = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.FullName
   
2. cnn.Open strCnn

复制代码

内外

1. strCnn = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.FullName
   
2. cnn.Open strCnn
   
3. strPath_date = "[Excel 12.0;DATABASE=" & ThisWorkbook.Path & "\测试B.xlsm]." '日期表

复制代码

外外1

1. strCnn = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.Path & "\测试B.xlsm"
   
2. cnn.Open strCnn
   
3. strPath_date = "[Excel 12.0;DATABASE=" & ThisWorkbook.Path & "\测试B.xlsm]." '日期表

复制代码

外外2

1. strCnn = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.Path & "\测试B.xlsm"
   
2. cnn.Open strCnn

复制代码

   
外内

1. strCnn = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.Path & "\测试B.xlsm"
   
2. cnn.Open strCnn
   
3. strPath_date = "[Excel 12.0;DATABASE=" & ThisWorkbook.FullName & "]." '日期表

复制代码

数据在红框展示，蓝框点击进行各种测试。

测试环境：Intel(R) Core(TM) i5-6500 CPU @ 3.20GHz，内存8GB，64位win10系统，Microsoft 365
附件说明（4个文件须放在同一文件夹内测试）：
1、测试A.xlsm，主测试文件
2、测试B.xlsm，配合测试文件的外部文件
3、测试A_只读.xlsm，主测试文件的只读版本
4、测试A.xls，主测试文件的另存为xls版本

试验结果：主测试文件测试A.xlsm所有涉及“内”的，都很久。
内内1、内内2、内外需要12秒
外外1、外外2需要0.1秒
外内需要24秒

在出现这个很慢的状况之前，我感觉所有这6种情况用时是几乎一致的，都是0.1秒左右，主测试文件的变体测试A_只读.xlsm和测试A.xls的确在6种情况下真的只需要0.1秒。

为什么我要考虑做主测试文件的变体文件测试呢？因为 [求助] excel的SQL查询最近出大问题了，有谁知道原因吗？ 这个帖子里有人也遇到了和我一样的烦恼。我没有用WPS测试过，这次顺便把楼主提到的“改成XLS格式”和“改只读模式”测试了，试验证明的确这两种情况不会出现卡顿。帖子回复里有网友说他依然查询得很顺畅，没有一点问题，是不是因为他的查询文件里没有涉及“内”的东西呢？

综上所述，基本确定这一波ADO+SQL+VBA源于查询时用到了查询文件xlsm里的某些部分，但为什么之前没事？是windows系统的问题还是office软件的问题，这个估计得问为微软了。

针对这个“内”问题，在微软不给我们解决方案之前，我们可以尝试以下方法：
1、把查询文件xlsm里的内元素全部放到外部文件中（我自己就是这么干的，虽然麻烦，但也不会非常麻烦）
2、把查询文件xlsm另存为xls，高级格式会丢失一些
3、先设定查询参数，保存，把查询文件xlsm改成只读模式，然后再开始查询，下次查询前去掉只读，重复之前的步骤（操作有点麻烦，但暂时来说最不需要改动的方式）

不足之处请指正，希望大家帮忙测试。

补充内容 (2025-4-14 16:25):
PS：2025-04-14，发现一个弱智兼神经的事实。如果有“内”的问题，如果在你查询文件的时候外部文件处在打开状态，那么也会被认为是“内”，查询也会变慢。

shiruiqiang

产品2021，未更新，模拟器，测试A.xlsm。i3-4160，16g内存

xrspook

shiruiqiang 发表于 2025-4-10 16:58
产品2021，未更新，模拟器，测试A.xlsm。i3-4160，16g内存

所以这个问题跟升级貌似有很大关系

limonet

阿珍！你来真的啊！

xrspook

limonet 发表于 2025-4-10 20:26
阿珍！你来真的啊！

今天19点多我的Microsoft 365家庭版就登陆不了了！一直说未授权！！！！！

limonet

xrspook 发表于 2025-4-10 20:41
今天19点多我的Microsoft 365家庭版就登陆不了了！一直说未授权！！！！！

看来阿普是来真的了。

morpheus126

那就是内和外的原理是不同的？

xrspook

morpheus126 发表于 2025-4-11 10:36
那就是内和外的原理是不同的？

不知道微软的某个更新为什么制造了这种不同。据说Office 2019升级到最新版本就没有这个问题了。

WANT-T

楼主的问题，我也遇到了，我的工具使用了vba+sql+access的形式，6种模式，我这里都有，确实，涉及内的都有问题，开始以为是access的问题，后来压缩，修复，新建了都不解决问题，只要链接字符串里，是xlsm的就很卡

xrspook

WANT-T 发表于 2025-4-11 11:39
楼主的问题，我也遇到了，我的工具使用了vba+sql+access的形式，6种模式，我这里都有，确实，涉及内的都有 ...

如果是2019，据说升级到最新就好了，但如果是365，我这边就还没好。