如何提高查询效率VBA? [英] How to improve efficiency of this query & VBA?

问题描述

我在Access中有此查询:

I have this query in Access:

SELECT TOP 10 title,
  ConcatRelated("DOCTEXT","DocumFrag", "title='" & title & "'" ) AS result
FROM DocumFrag
GROUP BY title;

DocumFrag包含大约9000条记录，有关Concat的信息是在这里找到的VBA代码: http://allenbrowne.com/func-concat.html

DocumFrag contains about 9000 records, ConcatRelated is VBA code found here: http://allenbrowne.com/func-concat.html

当我仅对前10个查询运行查询并完成查询时，它会持续滞后于20秒响应时间(单击，键入等).

When I run this query for only TOP 10 and it completes, it continually lags to the point of 20 second response time (clicking, typing, etc).

有没有一种方法可以使它变得更稳定?我以TOP 10为例，测试它是否滞后；最后，我需要全选.

Is there a way I can improve this to be more stable? I'm doing TOP 10 as an example to test if it lags; in the end I'd need to select all.

此查询的目标与

My goal of this query is the same as Concatenating record values in database (MS Access) or in server side code (ASP.NET) (except in Access, not ASP.NET)

还是有一种方法可以使用查询而不是VBA来完成此操作?

Or is there a way I can accomplish this using a query, instead of VBA?

推荐答案

我的最佳猜测是ConcatRelated会为'DocumFrag'中的每个'title'求值.在应用此功能之前，请先在内部查询中选择前10名:

My best guess is that ConcatRelated evaluates for every 'title' in 'DocumFrag'. Select the top 10 in an inner query before you apply the function:

SELECT q.title, ConcatRelated("DOCTEXT","DocumFrag", "title='" & q.title & "'" ) AS result
FROM 
    (SELECT TOP 10 title FROM DocumFrag) AS q
GROUP BY q.title;

这篇关于如何提高查询效率VBA?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！