Access 2013-使用ConcatRelated查询，其中包含50000个条目 [英] Access 2013 - query with ConcatRelated containing 50000 entries taking forever

问题描述

我下面有一个SQL查询，通过连接相似的条目将包含600000个条目的3列的表转换为50000个条目

I have a sql query I have below to convert a table with 3 columns containing 600000 entries to 50000 entries by concatenating similar entries

SELECT Users, ConcatRelated("Locations", "Report", "Users = '" & 
Report.Users & "' AND Roles = '" & Report.Roles & "'") AS NewLocation, Roles
FROM Report
GROUP BY Users, Roles;

查询生成，但现在我想将其作为表导入到另一个访问文件中. 我试图将其导出为ex​​cel文件，但即使2小时后它仍继续运行. 我尝试将其转换为表格，然后使用

The query generates but now I wanted to import it as a table into another access file. I tried to export as an excel file but even after 2 hours it kept going. I tried to convert it into a table and import that table to an excel or access file by using the

INTO NewTable

在上面的查询中，但这也需要很长时间，并且没有完成的迹象.

in above query but that is also taking a long time and shows no sign of completion.

ConcatRelated是 HERE

ConcatRelated is a function from HERE

任何帮助都将非常棒！ 预先感谢

Any help would be really great! Thanks in advance

推荐答案

ConcatRelated不是便宜的函数.它必须为每个调用打开并循环一个记录集.

ConcatRelated is not a cheap function. It has to open and loop a recordset for each call.

当前，您为Report表中的每个记录调用它，但是每个User&角色.

Currently you call it for every record in the Report table, but you need to do it only once per User & Role.

因此:首先使用INSERT或SELECT INTO临时表进行分组:

So: first do the grouping with INSERT or SELECT INTO a temp table:

SELECT Users, Roles
INTO TempUsersRoles
FROM Report
GROUP BY Users, Roles

然后根据临时表中的记录ConcatRelated:

And then ConcatRelated based on the records in the temp table:

SELECT Users, 
    ConcatRelated("Locations", "Report", 
        "Users = '" & t.Users & "' AND Roles = '" & t.Roles & "'") AS NewLocation, 
    Roles
FROM TempUsersRoles AS t

它可能仍会运行一段时间(取决于有多少不同的用户+角色)，但应该比以前好很多.

It will probably still run some time (depending on how many distinct users+roles there are), but should be much better than before.

(您告诉我们，50k与600k，所以它的运行速度至少要快12倍.)

( you told us, 50k vs. 600k, so it should run at least 12 times faster.)

在Report中的Users + Roles上添加索引可能有助于使该功能更快.
用户+角色也应该是TempUsersRoles中的主键(不确定是否有区别).

Adding an index on Users+Roles in Report might help to make the function faster.
Users+Roles should also be the primary key in TempUsersRoles (not sure if it makes a difference).

这篇关于Access 2013-使用ConcatRelated查询，其中包含50000个条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！