我的天

昨天说到一个很简单的SQL语句引用的数据库就只有一个字段两行记录，居然需要24秒才能得出结果。这让我觉得非常不可思议。首先可以肯定的是数据量非常少，为什么会出现这种问题呢？那只能是连接方面是不是出了什么故障，也不能说，那是失效的，因为的确还能查询得到想要查询的东西。在我测试的那个宏里面。我引用了两个文件，一个是外部文件，一个是内部文件。外部文件是含有比较多的数据，而内部文件，也就是我一开始说的那个只有两条数据。我感觉如果我的SQL再厉害一些，我对VBA再熟悉一些的话，那个内部文件可能我就不需要引用了，我直接就在VBA里创建一个数据库，然后把两条数据给写进去，用完以后就删掉，但显然现在我还没有很大的把握，一定能完美地做这件事情。把我某个文件里面的数据转化为数据库的数据我又烂熟，所以我采取了现在使用的这种方式。

ADO+SQL的这种方式，因为我们是跨表引用，所以意味着数据肯定来源于多个文件。他们有可能是同一个工作簿的不同工作表，也有可能是在不同的工作簿里。对我来说，只要是在一个工作簿里，那么起码一开始设定指向的时候就得有一个数据源。最经典的方式引用的那个数据源在使用数据的时候，在from后面不需要进行进一步的引用，其它的就得麻烦一些。我的第一个反应是，是不是引用数据的那个语句出现了变动呢？比如说现在我用的是Excel12。在数据源引用方面，我又折腾了一番，发现好像还是那样，没什么进展。会拖慢查询的那个数据源，我甚至把它放到了主数据源里，结果发现还是很慢，于是这就排除了是数据源引用语句变动导致缓慢。

所以这到底是什么原因造成的呢？因为我有很多个跨表引用的查询。有些查询是内部数据外部数据都有，有些只有外部数据，经过测试后我发现好像只有引用了内部数据的查询才会变慢。

为了证明我这个想法，星期三的晚上我编造了一些数据做测试。主要原理就是研究是不是数据源的关系导致这种变慢。一开始我的设计就是一个排列组合的方式，因为我默认的数据引用是要跨表的，所以我把数据源根据内内、内外、外外和外内这4种方式测试，实际上内内和外外是一回事，也就不需要进行两个引用了，所以我又把那两个东西拿了出来，同样进行测试。结果让人有点吃惊，凡是有内部数据参与的查询都会变慢。我测试的数据就只有一个字段几条记录，内内和内外需要12秒，外外需要0.1秒，外内需要24秒。这就能解释为什么我的那些变慢的查询起码都要24秒才能出结果。因为我永远把内部数据放在后面。究其原因是因为我设计那些查询的时候，我后来才想到要在那个查询文件里面搭一个加脚手架，把一些基础的东西加上去，在这种情况下我加得最多的是日期表。

关于这个测试的来龙去脉以及最终的结果，我在ExcelHome里面做了一个详细的帖子，在这里就不再具体阐述了。

折腾了这么一番以后，我发现这个锅还真不是我整出来的。造锅的是微软，不知道更新出了什么状况导致了。

Excel用多了，不知不觉我也居然能挑出微软的毛病。

近几天都沉醉于做Excel的数据合并，实际上就只是两个表，一个是交易情况另外一个是出入库明细。出入库明细是一个大表，交易情况里面除了交易本身以外还有货款明细以及损溢明细，所以原始表格合计有4个。相对于我之前纠结过的那些东西来说，这几个表有一个数据是必定唯一的，也就是交易情况里面的合同号。损溢明细里一个合同号可能出现多次，货款明细里一个合同号可能会出现多次，出入库明细里一个合同号绝大多数情况会出现多次。所以这些表里，交易情况的合同号是1，其它是多。以前在Excel里面用Power Pivot建立表的关系的时候，我是直接把某一个列拉到另外一个表的列建立关联，但这一次我发现交易情况和损溢明细无论我怎么拉，PP都默认，一是损溢明细，多是交易情况。直到昨天我才知道原来建立表关系的时候是可以不用手拉的。理论上我已经把PP的界面看过一遍又一遍，不仅仅是软件本身，关于它的书我也看过一些，但是好像在昨天之前我就从来没有发现原来有那么个界面。1对多到底谁是一，最重要的一点是哪个表先选定，后选定的表在Excel的Power Pivot里默认是多，但如果二者关系不成立，会建立不了关系。我不知道Excel的PP为什么会这么设定，理论上应该还有1对1，多对多之类的关系，显然在Power BI里面就有，但Excel就是这么神经。用Excel PP默认给的那个1对多的关系，在用透视表的时候，无论如何都得不出我想要的结果，但实际上我想要的那个结果我想了半天也都是合理的。结果手动建立正确的1对多关系后，透视表的数据就正常了。之前，我在Excel的PP上耗了好长时间研究其它东西，之所以有些坎无论如何迈不过，无论如何觉得有毛病我猜是不是也跟这个1对多的关系自动默认给我生成错了。

在玩PP之前，其实我已经折腾了一天的Power Query，之所以从PQ转投PP，是因为我发现虽然我的原数据很简单。最大的那个表才几千行，载入以后进行初次筛选，剩下700多行，另外的那些表只有几行和几十行。但即便这样，它们关联了以后，做全体刷新的时候依然会出现卡顿，依然会偶尔告诉我数据不是我期待的那个格式，所以刷新失败，但实际上当我重新再去刷新失败的那个表，又会成功。在不修改参数的情况下刷新同一个查询，有时都能刷出来，有时一些刷不出来，有时刷的时间要长一点，有时刷的时间挺短，到底这是为什么？各种缓存的参数我都试过了，各种PQ本身设置相关的参数我也试过了，但是PQ自己就是这么不稳定。快的时候可能5秒就出来了，但是慢的时候可能30秒都出不来。折腾了我一天后，当我用PP做出跟PQ完全一样的功能的时候，发现PP的稳定性好很多，几乎不会出现数据刷新失败，大多数情况下，PP刷新需要10秒钟，但是它俩最大的区别在于PQ这个查询文件只有50KB，但是PP的查询文件是800KB，之所以这样，是因为实际上PP把整个原数据都抓过来了。考虑到这些查询文件我肯定会在不同的机器上运行，所以虽然PP可以直接连接某个外部源文件，但我还是选择先在PQ里做一个动态接口，然后再转到PP的模型上，同时在一开始的时候让PQ把3000多条数据出筛成700多条。

10秒钟和不知道到底要多少时间，以及50KB和800KB这两个答案我都不太满意。所以接下来估计我会尝试一下python方案。