如何让 MS-Access 为我的查询选择不同/正确的执行计划
我遇到一个相对简单的查询和 Access 为其选择的执行计划的问题。
查询采用这种形式，
SELECT somethings
FROM A INNER JOIN (B INNER JOIN (C INNER JOIN D ON ...) ON ...) ON ...
WHERE A.primaryKey= 1 AND D.d = 2;
C 和 D 的行相对较少。 A 和 B 有几千行。
返回 2 行的查询（不确定这是否相关）非常慢。运行时间为 17 秒。如果我删除 where 子句的 AND Dd = 2 部分，查询现在将返回 4 行并立即运行。
所以我的理解是，JET引擎可以立即在Dd上运行没有过滤器的查询，然后立即执行所述过滤器（仅过滤4行）。因此，使用 Dd = 2 过滤器运行查询的时间不应太长。
我尝试创建一个不带过滤器的子查询，并将其包含在另一个仅过滤结果的查询中，但它仍然很慢。我的猜测是，JET 引擎足够智能，可以“扁平化”子查询，因此结果是相同的。
由于我无法按照我希望的方式运行查询，所以我使用了 JETSHOWPLAN 东西，以便 Access 可以输出它的执行计划。以下是我的发现：
对于快速查询（没有 Dd = 2 的查询），查询计划的第一步是应用 A.primaryKey = 1 过滤器A 表。这导致数据集超过 30000 行，其中只有 1 行。然后，连接似乎是使用索引从 A 到 D 执行的，数据集永远不会超过 4 行。
慢查询似乎是按相反的顺序执行的。首先连接 D 和 C，然后测试 Dd = 2。之后，执行从C到A的连接。通过这种方式，需要连接的从 D 到 C、从 C 到 B、从 B 到 A 的数据要大得多。当所有 JOIN 执行完毕且在执行 A.primaryKey=1 之前，数据集将有 120K 行。
有没有办法可以在 Access 上强制执行正确的查询计划？
我希望我说清楚了。让我知道是否应该发布查询计划。我没有，因为它们很大。
预先感谢，
国会议员
I have a problem with a relatively simple query and the execution plan Access choose for it.
The query is of this form
SELECT somethings
FROM A INNER JOIN (B INNER JOIN (C INNER JOIN D ON ...) ON ...) ON ...
WHERE A.primaryKey= 1 AND D.d = 2;
C and D have relatively few rows. A and B have a few thousands rows.
The query, which returns 2 rows (not sure if this is pertinent) is really slow. It runs in 17 seconds. If I remove the AND D.d = 2 part of the where clause, the query now returns 4 rows and run instantly.
So my understanding is that the JET engine could run the query without the filter on D.d instantly, then execute the said filter instantly (only 4 rows to filter). Therefor it should not be too much longer to run the query with the D.d = 2 filter.
I tried to create a sub query without the filter and include this in another query that would just filter the result, but it's still slow. My guess is that the JET engine is smart enough to "flatten" the sub-queries so the result is the same.
Since I was unable to make the query run as I wished I used the JETSHOWPLAN thingy so that Access would output it's execution plan. Here is what I found:
For the fast query (the one without D.d = 2) the first step of the query plan is to apply the A.primaryKey = 1 filter on the A table. This result in a data set of 1 row out of more than 30000. Then the joins seems to be executed from A to D using index with a data set that never goes over 4 rows.
The slow query seems to be executed in the revers order. D and C are joined first then D.d = 2 is tested. After that, the joins from C to A are executed. By doing this this way the data that needs to be joined from D to C, from C to B and from B to A is much larger. When all the JOIN are executed and before A.primaryKey=1 is executed the data set will have 120K rows.
Is there a way I could force the right query plan on Access?
I hope I was clear. Let me know if I should post the query plans. I did not because they are quite large.
Thanks in advance,
mp
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
用VBA代码做吗？这个想法是取出慢的部分并执行快速返回的查询，然后将慢的部分附加到sql中。
不，模块中的 VBA 代码与子查询不同。 @HansUp 已经向我们澄清，如我上面所示，一步执行代码不会提高性能。如果您熟悉在模块中编写代码，那么您应该能够快速在内存中获取结果，但是在需要的地方获取输出可能会减慢您的速度。
换句话说，您应该能够快速将 qryFast 的结果获取到内存中的记录集，然后对 qryFast.dkey = d 应用过滤器，并且还可以从 'select * from tableD where d=2' 快速获取记录集从 tableD 中查找所需的相关信息，但是将所有这些内容从内存中取出并到达前端可以访问的位置可能需要比他们现在等待的 17 秒更长的时间。
它可能会让人感到很震惊
事实上，如果您更改 qryFast 以包含 dkey = 2 的条件（或 tableD 上的 pk）另一个想法：有 3 个查询，qryFast、qryD 和 qryFastWithD 连接这两个查询，那么 。我只是在这里抛出想法。
或者，正如您在评论中所说，尝试在子查询中包含查询的不同部分，但我认为优化器不会被这样的技巧所愚弄，如果将其中一部分移动到子查询中没有'不工作。
无论如何，无论什么有效，就采取它。
Do it in VBA code? The idea would be to take out the part that's slow and execute the fast-returning query, then append the slow part in sql.
No, VBA code in a module is different from a sub-query. @HansUp has clarified for us that executing the code in one step, as I've shown above, won't improve the performance. You should be able to get the results in memory quickly, if you're familiar with writing code in modules, but then getting the output where you need it to go might slow you down more.
in other words, you should be able to get the results of qryFast into a recordset in memory quickly, and then apply a filter on qryFast.dkey = d, and also get a recordset quickly from 'select * from tableD where d=2' to look up the related info you want from tableD, but getting all that stuff out of memory and to a place where your front-end can access it might take longer than the 17 seconds they're waiting now.
In fact, it might kick it in the pants enough if you change qryFast to include a condition where dkey = 2 (or whatever the pk is on tableD)
another idea: have 3 queries, qryFast, qryD, and qryFastWithD joining the two. I'm just tossing out ideas, here.
or, as you say in your comments, try containing different parts of the query in sub-queries, but I would think the optimizer wouldn't be fooled by such a trick, if moving a piece of it into a sub-query didn't work.
By all means, whatever works, take it.
我最终通过混合各种东西来让它工作，直到查询计划者同意我的意见。我在子查询中隔离了“A.primaryKey= 1”，以确保它在 A 连接到 B 之前执行。它是这样的：
I finally got it to work by mixing things up until the query planner agreed with me. I isolated the "A.primaryKey= 1" in a sub-query to ensure it's executed before A is joined to B. It's something like this :