Concat majorly affecting query speed

[Kayleigh]

I have a summary form based on a complex join query. I tried adding a field to concat related records into each row (based it on a query which worked fine when run) but it takes approx half a min for form to display - a 'running query' message appears in status bar. Why is it taking so long to run this aspect of the query??

The data appears correctly when it does present itself! I have tried using less queries and changing method used but it is same story - works fine as query but when put together with the form's query it takes forever!

Any suggestions?

 

[Isaac]

adding a field to concat related records into each row

do you mean it's a VBA function?

 

[Kayleigh]

Yes the concat field in query is based on the VBA function

 

[Isaac]

Well, that answers your question then ... VBA functions to concatenate (transpose) related records are popular, but opening and looping through and closing recordsets - once for every time it runs on every row - is no small thing.

Anything, of any kind, that deviates, in any way, from set-based SQL processing is a drag on performance.

 

[Kayleigh]

I see - didn't realise it was opening recordsets for each row. Just checked my function and what you're saying makes sense.
Any other suggestions how to concatenate without requiring VB code?

 

[Isaac]

I see - didn't realise it was opening recordsets for each row

Well, I was just guessing at what it did.

Any other suggestions how to concatenate without requiring VB function?

Great question - I will think about this and post back if I have other ideas. Someone who is an expert at Access-based SQL may chime in here too.
I kind of don't think it's possible, otherwise the VBA based functions wouldn't be so popular and passed around.

Any chance the back end tables are SQL Server?

 

[MajP]

I had a very large DB, and there was no way a vba concat function would handle it. I persisted the results in a field in the table. I just had to make sure that every time I add/ edited/ deleted a child record that I updated that record. Not hard to do if all your inputs are through forms which mine are. I do not make dbs where the use enters data in queries or tables.

 

[MajP]

However most of those concat functions are super inefficient. This could be done with opening and closing a single rs, but you need a form to launch the query.

 

[Isaac]

was going to suggest some t-sql but only if the be is sql and if OP is willing to use t-sql and PT query

 

[MajP]

Based on what the OP has posted, I am assuming access backend.

 

[MajP]

One thing you may want to ensure. If you filter the query, I would do this in a query first. In fact I would do everything first. Then make a query based on that. You do not want to run an expensive function on all records and then apply a filter.

 

[Kayleigh]

Thanks for discussing BE is Access at moment though hope to move into SQL server eventually!
I was considering the data to be persistent however it may be updated...

Here is summary of query process:
query to create initials for student name and join with sessionID, couple of lookup queries and relevant data tables
Joined to create query to lookup sessions which is filtered acc to term. Added the concatRelated field into this query so it is probably going through all records before filter...
Would SQL of query help?

 

[MajP]

Would SQL of query help?

I would just try and see if it makes any difference. Take your query and remove the concat field. Then create a new query based on this query and add the concat field. This way you are doing any filtering and joining first. I think you are already using a UDF on the intials which will also slow things down.

 

[Kayleigh]

Good idea. Just tried it and definitely improved performance!

 

[Isaac]

you could also play around with doing the concatenation at the level of form current event, and not in query at all, just see how it goes.

 

[Kayleigh]

The form is in datasheet view so didn't think it was possible to do on current event for such fields?

 

[Pat Hartman]

All forms work the same way. They have the same events that operate on the same action. DS is just a different view of the data.

What are you concatenating/ Why can't you use a subform to show the data instead of concatenating it?

 

[Kayleigh]

All data is in datasheet view so I can only show in columnar structure - it is summary of all sessions this term. The concatenate field is joining all students displaying names as initials format. So I should still try using onCurrent for that column instead of querying?
There is already a sub-datasheet currently showing other info...

 

[MajP]

So I should still try using onCurrent for that column instead of querying?

No that was assuming you had a single record view not a datasheet.
How big is this db? I am suprised it is that slow. Can you post a screenshot of your setup? could you have another linked subform so if you want to see the concatenated list you select the record you want? My final solution would be to persist this information in the table. If you added the field you could update when the form loads or as I said you keep it in synch.

 

[Galaxiom]

All data is in datasheet view so I can only show in columnar structure

Put the main datasheet in a subform and add another subform for the related data. The LinkMasterFields of the second subform can refer to a field or control in the other subform using Parent.othersubform.fieldname