Concat majorly affecting query speed

[Minty]

Lots of good suggestions here.
I agree with @Pat Hartman - If this simply a "nice" display feature to have but no one is really benefiting from it, then remove it.

I recently had a client that wanted a list of suppliers and part numbers concatenated for use into an Excel export.
If I ran the query on one part it was fine. On an assembly with 100 + parts in it, it was slow.
And at the client end unuseable.

So I ended up doing what @MajP suggested, created a table with all the concatenated results in it.
It wouldn't run with the BE data on a network, I had to copy it on local copy tables then copy it back to the backend, and then manage any updates on the forms concerned. Real PITA, but the only viable solution.

The same process on SQL server BE was almost instantaneous by comparison, as it has a native function for it (String_Agg) .

 

[Kayleigh]

I don't know if anyone has suggested this but if you write a procedure to sort the many-side data into the correct order, you can loop through it ONCE, concatenating the matches and write out a record with two fields, the ID and the string to a temp table. This will be the fasted method since it only process the recordset ONCE. Doing it in a function in a query incurs a lot of overhead opening and closing the recordset and fetching the data to concatenate.

Is it possible to do this in SQL or only DAO?

 

[Kayleigh]

I see so you would implement the criteria check into the concatenate function and then this would be output into query for FE. So it is just about reordering procedure?

 

[Isaac]

Is it possible to do this in SQL or only DAO?

No, I don't think it's possible to do that in SQL against an Access back end

 

[CJ_London]

it's not been mentioned, so I'll ask these questions.

Are your tables properly indexed?

what is the code to your concat function?

don't know your data or what the output looks like but have you considered using a crosstab query instead of a concat function? you would still need a vba function to give each initials a 'column number' but at least you are not working with record sets.