Concat majorly affecting query speed (1 Viewer)

[Pat Hartman]

Sit back and think about the actual necessity of having the concatenated field. Are people really going to do anything with this information or are you just showing the initials because you think it is cool and you figured out how to do it. I can assure you, if there are more than 2 or 3 items mushed together, people are not going to be doing anything with this field.

 

[Kayleigh]

Valid point Pat.
So what do you say to adding an IF to only display those will less than five values? Or will that slow down running further?

 

[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) .

 

[Pat Hartman]

display those will less than five values?

The only way to determine if there are only 5 variables is to run the function. That defeats the entire purpose.

If the data is critical to the process, you have to do whatever you can to speed it up. Otherwise, you're just wasting time. Move on.

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.

I don't like temp tables, they bloat the database but the do have uses. There are a number of posts here suggesting using a template database in which you load temporary data so that you always recreate the file each time you want to run the process. This eliminates the bloat problem from the FE. Or, if you are using a distribution method that copies a fresh FE from the server and downloads it each time the user opens the app, make the temp table in the FE since you won't have to worry about bloat.

 

[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?

 

[Pat Hartman]

LOOPS are VBA against a recordset (DAO or ADO). Queries work on sets. They do not loop. You use criteria to select a set of records and the same thing happens to all members of a set.

 

[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?

 

[Pat Hartman]

No. I would not use a function in a query. That's what you're doing now and that is what is slow. I would create a procedure in a module and call it from a form. The procedure would open a query that sorted the table in the order that would allow it to be concatenated. The loop would read through the query, record by record, when the "id" changed, it would write the previous record and start concatenating in a new one. Your query would then join to the temp table.

But, I wouldn't actually do this at all unless the client insisted that the concatenated values were somehow critical to whatever he was doing with this data. I'm just giving you a solution that will probably be more efficient than doing the concatenation in a query. I don't know how many rows you have but if you have a thousand rows, the function was opening and closing the recordset 1000 times. That is a lot of overhead. If your one side table is small and your many-side table is also small, then doing this with the concat function will be slightly sluggish but not annoyingly so.

I haven't looked at the concat function in a long time but the code I'm suggesting is essentially the same logic with one difference, instead of taking in the ID of the 1-side record and reading only the many-side table looking for records with that ID, you are reading the entire many-side table, SORTED (this is critical) and concatenating the values for ALL ids and saving each set as a record in a temp table.

 

[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.