MS Access 2010 SQL按组性能的前N个查询(续) [英] MS Access 2010 SQL Top N query by group performance issue (continued)

问题描述

在MS Access 2010中，我遇到以下查询中的重大性能问题(直到超时).表TempTableAnalysis包含10'000-15'000条记录.我已经收到来自这个论坛的输入，用于处理前10个查询中的临时表(

I have signficant performcance issues (up to time-out) in MS Access 2010 with the query below. The table TempTableAnalysis contains between 10'000-15'000 records. I have already received input from this forum to work with a temporary table in the top 10 query (MS Access 2010 SQL Top N query by group performance issue)

有人可以解释如何在子查询中实现临时表以及如何将其联接吗?我无法正常工作.

Can anyone explain how to implement the temporary table in the subquery and how to join it? I can't get it to work.

任何其他改善性能的建议均受到高度赞赏.

Any other suggestions to improve performance are highly appreciated.

这是我的查询:

SELECT 
t2.Loc, 
t2.ABCByPick, 
t2.Planner, 
t2.DmdUnit, 
ROUND(t2.MASE,2) AS MASE, 
ROUND(t2.AFAR,2) AS AFAR

FROM TempTableAnalysis AS t2

WHERE t2.MASE IN  (
SELECT TOP 10 t1.MASE 
FROM TempTableAnalysis AS t1
WHERE t1.ABCByPick = t2.ABCByPick
ORDER BY t1.MASE DESC
)
ORDER BY
t2.ABCByPick, 
t2.MASE DESC;

推荐答案

针对大型数据集优化访问查询性能

根据发布的SQL查询，您可以使用一些选项来优化和加快性能.

Optimizing Access Query Performance For Large Data Sets

Based on your posted SQL Query, you have some options available to optimize and speed up the performance.

SELECT 
   t2.Loc, 
   t2.ABCByPick, 
   t2.Planner, 
   t2.DmdUnit, 
   ROUND(t2.MASE,2) AS MASE, 
   ROUND(t2.AFAR,2) AS AFAR

   FROM TempTableAnalysis AS t2
   ...

这是第一部分，其中TempTableAnalysis是数千记录子查询.如果您想通过使用此临时"表来降低性能，请不要将其用作动态查询(即，每次打开查询时都按需计算)，请尝试构造一个宏来推送输出到静态表:

This is the first part where TempTableAnalysis is the multi-thousand record subquery. If you want to squeeze a little more performance out of the use of this "Temp" Table, don't use it as a dynamic query (i.e., calculated on demand each time the query is opened), try constructing a macro that pushes the output to a static table:

将子查询数据追加到静态表:

1. 创建一个QUERY对象并将其类型更改为DELETE.设计它以删除临时"表对象的内容.如果您更喜欢使用SQL，该命令将如下所示:

1. Create a QUERY object and change its type to DELETE. Design it to delete the contents of your "temporary" table object. If you prefer using SQL, the command will look like:

DELETE My_Table.*
FROM My_Table;

创建一个QUERY对象并将其类型更改为APPEND.设计它以查询此OP的SQL语句定义的查询中的所有字段.同样，此任务的SQL版本具有以下语法:

Create a QUERY object and change its type to APPEND. Design it to query all fields from your query defined by the SQL statement of this OP. Again, the SQL version of this task has the following syntax:

INSERT INTO StaticAnalysisTable ( ID, Loc, Item, AvgOfScaledError )
SELECT t1.ID, t1.Loc, t1.Item, t1.AvgOfScaledError
  FROM TempTableAnalysis as t1;

下一步是自动化此静态表的填充，它是可选的.但是，它很简单，并且您不太可能会犯这样的错误:忘记刷新"并在静态表具有陈旧数据的情况下访问静态表...从而导致结果不准确.

The next step is to automate the population of this static table and it is optional. It's simple however and will make it less likely that you will make the mistake of forgetting to "Refresh" and accessing your static table while it has stale data... causing inaccuracies in your results.

创建包含两个步骤的宏.每个步骤将具有以下定义:OPEN QUERY.当提示您打开查询时，请按以下顺序(重要)引用您在前两个步骤中创建的对象:(1)DELETE Query: (your delete query name)然后(2)APPEND Query: (your append query name).

Create a macro with two steps. Each step will have the following definition: OPEN QUERY. When prompted for the query to open, reference the objects you created in the previous two steps in the following order (important): (1) DELETE Query: (your delete query name) then (2) APPEND Query: (your append query name).

SQL查询注释和建议

发布的SQL查询的以下部分可以使用一些帮助:

The following part of the posted SQL query could use some help:

...

WHERE t2.MASE IN  (

     SELECT TOP 10 t1.MASE 
     FROM TempTableAnalysis AS t1
     WHERE t1.ABCByPick = t2.ABCByPick
     ORDER BY t1.MASE DESC
     )

ORDER BY
   t2.ABCByPick, 
   t2.MASE DESC;

1. 子查询中有一个联接生成TOP-10数据，而最外层的查询将这些结果与补充的MASE表数据相关联.如果TempTableAnalysis.MASE表示键值，则不必要.

1. There is a join across the sub query that generates the TOP-10 data and the outermost query that correlates these results with the supplementing MASE table data. This isn't necessary if the TempTableAnalysis.MASE represents a key value.

ORDER BY

并不是必需的，除非它旨在强制某种选择标准(例如，在使用SQL分析函数时)，这看起来不像是其中一种情况.从大数据集订购记录也是一种浪费的CPU和内存接收器.

in the inner most query isn't necessary unless it is intended to force some sort of selection criteria (as in when using SQL analytical functions) this doesn't look like one of those cases. Ordering records from large data sets is also a wasteful cpu and memory sink.

编辑:正如一个反点参数一样，在TOP N查询旁边使用的ORDER BY子句实际上是有目的的，但是我仍然不清楚是否有必要.为了结束讨论，另一个SO线程讨论了

Just as a counter-point argument, the ORDER BY clause used beside a TOP N query actually has a purpose, but I am still not clear if it is necessary. Just to round out the discussion, another SO thread talks about How to Select Top 10 in an Access Query.

1. WHERE t2.MASE IN (...

您可能正在使用很大的列表设置操作而遇到性能障碍.在Oracle数据库服务器上，我与其他开发人员一起发现列表中查询运算符中离散元素的数量受到限制.该值成千上万...可能会根据服务器和数据库资源而进一步受到限制.

You may be experiencing blocks in performance with very large in-list set operations. On an Oracle database server, I have discovered with other developers that there is a limitation to the number of discrete elements in an in-list query operator. That value was in the thousands... which may be further limited based on server and database resources.

考虑使用SQL JOIN运算符.定义TABLE对象的位置也可以使用别名为INLINE VIEWS的SQL定义查询填充.由于您使用的是ACCESS，因此如果内联视图不能直接工作，只需定义另一个ACCESS QUERY对象，并在最终查询中将其引用为表即可.

Consider using a SQL JOIN operator. The place where you define TABLE objects can also be populated with SQL defined queries with aliases known as INLINE VIEWS. Since you're using ACCESS, if an inline view does not work directly, just define another ACCESS QUERY object and reference it in your final query as if it were a table...

可能会重写为原始查询的结尾部分:

A possible rewrite to the ending part of the original query:

SELECT 
   t2.Loc, 
   t2.ABCByPick, 
   t2.Planner, 
   ...

FROM TempTableAnalysis AS t2,
   (SELECT TOP 10 t1.MASE, t1.ABCByPick
      FROM TempTableAnalysis AS t1) AS ttop

WHERE t2.MASE = ttop.MASE
  AND t2.ABCByPick = ttop.ABCByPick

ORDER BY
   t2.ABCByPick, 
   t2.MASE DESC;

您肯定需要仔细阅读这些建议，并验证输出数据的准确性.这代表了捕获某些低挂的水果"(简单的项目)的方法，您可以使用这些方法来加快查询和报告操作的速度.

You will definitely need to run through these recommendations and validate the output data for accuracy. This represents approaches to capturing some of the "low-hanging fruit" (easy items) that you can pursue to speed up your query and reporting operations.

结论和结束语

作为其他读者的背景，数据库对象TempTableAnalysis是不是静态表.这是另一个SO帖子中出现的子查询的结果，该子查询请求使用

As a background to other readers, the database object TempTableAnalysis is not a static table. It is the result of a sub query presented in another SO post requesting help with a Access TOP N Query. The query comes from multiple tables approaching 10,000 records in size (each?).

提示:Access ALSO中的查询结果可能具有类似表的行为.您可以将输出推送到表中进行连接(如上所述)，也可以仅连接到查询对象本身(但是要小心，特别是当您链接"多个查询操作时...)

Tip: A query result in Access ALSO has potential table-like behaviors. You can push the output to a table for joining (as described above) or just join to the query object itself (careful though, especially when you get to "chaining" multiple query operations...)

此解决方案的策略是:

1. 要最大程度地减少通过这个超大表的一个或多个实例的行程次数.

1. To minimize the number of trips through one or more instances of this very large table.

要对数据进行预处理和索引优化，否则在分析期间这些数据都是静态"的.

To pre-process and index optimize any data that would otherwise be "static" for the duration of its analysis.

审核并查看用于获取最终结果的SQL代码.

To audit and review the SQL code used to obtain the final results.

一定要研究Access MACROS.结合识别数据集中的静态数据，您可以减轻复杂的后台分析查询的处理负担，以改善用户查看和查询最终结果时的体验.祝你好运！

Definitely look into Access MACROS. Coupled with identifying static data in your data sets, you can offload processing of your complex background analytic queries to improve the user experience when they view and query through the final results. Good Luck!

这篇关于MS Access 2010 SQL按组性能的前N个查询(续)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！