访问:ConcatRelated适用于表，但不适用于查询 [英] Access: ConcatRelated works on a table, but not on a query

问题描述

我一直在使用艾伦·布朗(Allen Browne)的ConcatRelated 函数，并且当数据到来时它可以正常工作从表中获取数据，但是当数据来自查询时则无效.

I have been using Allen Browne's ConcatRelated function, and while it works fine when the data comes from a table, but it doesn't work when the data comes from a query.

绿色的正在运行的查询"栏会显示几秒钟，但随后尝试显示数据时，它仅显示第一行中的一个字段，运行非常缓慢，可能需要几分钟才能显示的第一个屏幕.记录.我没有将其保留足够长的时间来完成结果集，不得不使用任务管理器关闭Access.

The green 'running query' bar appears for a few seconds, but then when it tries to display the data it shows only one field from the first row, runs very slowly and can take a few minutes to display the first screen of records. I've not managed to leave it long enough to complete the result set, have to shut down Access using the task manager.

每次调用该函数时查询是否都在运行?那可以解释为什么要花这么长时间但似乎不太可能.

Is the query getting run each time the function is called? That could explain why it takes so long but seems unlikely.

函数，调用函数的查询或源数据来自的查询是问题吗?

Is this a problem with the function, with the query that is calling the function, or the query that the source data is coming from?

推荐答案

以下是在即时"窗口中使用该功能的示例.

Here is an example using that function in the Immediate window.

CompanyID = 7
? ConcatRelated("OrderDate", "tblOrders", "CompanyID = " & CompanyID)
12/11/2012, 12/12/2012, 12/13/2012

功能...

1. 创建此SQL语句

1. creates this SQL statement

在公司ID = 7的tblOrders中选择OrderDate

SELECT OrderDate FROM tblOrders WHERE CompanyID = 7

基于该语句打开记录集

每次调用它时，

IOW， ConcatRelated()都会执行大量工作.当您将其作为查询中的字段表达式调用时，它必须对查询结果集的每一行再次执行所有工作.

IOW, ConcatRelated() performs quite a bit of work each time you call it. And when you call it as a field expression in a query, it has to perform all that work again for each row of the query's result set.

除了固定开销"之外， ConcatRelated()可能会导致额外的性能成本:如果在 CompanyID 上没有索引，则数据库引擎将不得不使用对 tblOrders 进行全表扫描，以找到满足 WHERE 子句的行.

In addition to that "fixed overhead", ConcatRelated() may lead to an additional performance cost: Without an index on CompanyID, the db engine would have to use a full table scan of tblOrders to find the rows which satisfy the WHERE clause.

您的问题询问使用查询代替带有 ConcatRelated()的表的影响.然后，函数的内部SQL语句将为:

Your question asks about the impact of using a query instead of a table with ConcatRelated(). Then the function's internal SQL statement will be:

SELECT OrderDate FROM YourQuery WHERE CompanyID  = 7

除了 ConCatRelated()对一个表带来的性能挑战之外，您还要承担2倍的风险，这可能会大大增加工作量.

In addition to the performance challenges imposed by ConCatRelated() with a table, you risk 2 more which may dramatically increase the workload.

1. 如果 YourQuery 需要数据库引擎付出很多努力，则它必须付出这种努力来创建父查询结果集的每一行.
2. 您增加了数据库引擎无法在函数的内部SQL语句中的 WHERE 子句中使用索引的可能性.即使在 YourQuery 的基础表源中的 CompanyID 上有索引，数据库引擎也可能无法从使用它获得足够的好处.

1. If YourQuery demands a lot of effort by the db engine, it must put in that effort to create each row of the parent query's result set.
2. You increase the likelihood that the db engine will not be able to used an index for the WHERE clause in the function's internal SQL statement. Even if there is an index on CompanyID in the underlying table source of YourQuery, the db engine may not see enough benefit from using it.

因此，尽管 ConCatRelated()很有用，但使用起来却很昂贵.这不是因为函数中有任何设计错误.确切地说，无论您使用哪种方法完成任务，任务的性质都是如此昂贵.并要求该函数使用查询而不是表，可能会增加成本.

So while ConCatRelated() is useful, using it is costly. That's not because of any design error in the function. Rather the nature of the task is just so expensive regardless of what approach you use to accomplish it. And asking that function to use a query instead of a table likely inflates the cost.

这篇关于访问:ConcatRelated适用于表，但不适用于查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！