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;
查询生成,但现在我想将其作为表导入到另一个访问文件中. 我试图将其导出为excel文件,但即使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屋!