I have this query in Access:
SELECT TOP 10 title,
ConcatRelated("DOCTEXT","DocumFrag", "title='" & title & "'" ) AS result
FROM DocumFrag
GROUP BY title;
DocumFrag contains about 9000 records, ConcatRelated is VBA code found here: http://allenbrowne.com/func-concat.html
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).
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)
Or is there a way I can accomplish this using a query, instead of VBA?
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;
yes, 1st make sure your data table has a clustered index (this determines the order the data is stored on disk), otherwise you have a heap and the sql engine needs to query the entire table as the data can be anywhere in the table. 2nd put a covering index on the querying parameters and the data you want to return. 3rd you are trying to group text? It would be better to Find the top 10 items and then conconate the text associate with them rather than conconate every group item as your code is doing and then select the top 10.
