1

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?

Community
  • 1
  • 1
JBurace
  • 5,123
  • 17
  • 51
  • 76
  • 2
    User defined functions are slow. For the most part, you may as well use pure VBA , it may even be faster. – Fionnuala Mar 21 '13 at 14:50
  • Perhaps try running this query as a `Snapshot`. Or output your results to a temp. table. – Mike Mar 21 '13 at 18:00
  • @Mike A Snapshot still left it fairly laggy, although helped maybe about 20%. – JBurace Mar 21 '13 at 18:33
  • @JBurace How are you going to use the results? If you bind them to form that displays just one record at at time the issue might disappear. If you can export them to a file or temporary table. – Mike Mar 21 '13 at 19:00
  • @Mike The intention is for a user to Ctrl+F through the entire results. But at the same time, an "admin" may be updating the original table the query pulls from fairly regularly. – JBurace Mar 21 '13 at 19:19
  • Is `DocumFrag.title` indexed? – HansUp Mar 22 '13 at 02:53
  • @HansUp I tried that now, didn't seem to help at all. Probably because as Remou said, it's the function. – JBurace Mar 22 '13 at 14:21

2 Answers2

2

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;
David
  • 943
  • 1
  • 10
  • 26
  • Sorry, updated my question. I do need to select all in the end, top 10 was only done to test how laggy it would be. – JBurace Mar 21 '13 at 15:01
  • Have you checked if the query takes the same time to complete if you remove 'top 10'? – David Mar 21 '13 at 15:07
  • I don't know about query completion time, that's not what the issue is. The issue is that all of Access lags when this query is open (but complete). – JBurace Mar 21 '13 at 15:08
  • If the problem is that every row is running ConcatRelated, the 20-second lag should be much shorter with my query; and unchanged if you only remove the 'top 10'. – David Mar 21 '13 at 15:15
0

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.

Ian P
  • 1,724
  • 1
  • 10
  • 12