dconcat runs very slow on network

hey guys,

i'm using matthewspatrick's dconcat function in my query. it's a fantastic, fantastic function and really helps me so much. however now when i'm uploading my database onto the network splitting it, it's running very very slowly. i'm quite sure the dconcat function is the cause of it.

could yall help me take a look at why this is running so slowly?

the query name is opxTopContainer_Sorted

here's my database attached = )
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
Same issue, I guess, as for your other question about opening queries.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Sounds like you have some networking issues possibly.

But I'll add two things:

1. Any type of Domain Function or sub select inside of a query is going to cause it to perform poorly.   I don't remember what DConcat does off hand (believe it flattened data), but the built-in domain functions should never be used in a query.

2. One of the tricks you can do to help with BE performance over a network is to always keep a connection open to the BE (i.e. keep a recordset open on a table - doesn't even need to have any records - just do a SELECT * FROM <some table> WHERE 1=0)

  This avoids all the overhead of repeatedly closing and opening the BE DB.   Depending on the server, that can be a slow operation (this ties into what gustav mentioned about a shared drive mapping vs UNC).

  Try that and see if your switches in/out of query design are faster.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
<i'm quite sure the dconcat function is the cause of it.>
Then remove it, then test...

As effective as it is, Dconcat is still a user defined function.
This means that it will always be a tiny bit slower than a built in function.
But this should effect should be negligible. (un-noticable)

*IF*, in fact, it is determined that Dconcat is the source of the perceived slowness, then remember that in order to work its magic, it has to loop through all the values to concatenate them.
So if you are using this on a great many records, it may never be "instantaneous".

Also remember that "Slow" is a relative term.
So you may have to define "Slow" in real chronological terms (also, noting how many records are effected)
Also consider how you are using this function, ...are you using this function in an already complex query?
Are all of your indexes set to optimize performance?

You seemed to have simply posted your database here.
It fails immediately upon opening, with your custom error code...
You also did not post any instructions on how to use the database, in order to see the issue?
Finally, the db seems to need the linked tables, so I am not quite clear on how we would be able to see the issue.

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Jeffrey CoachmanMIS LiasonCommented:
When posting a sample database, follow these general rules:

1. Back up your database(s).
2. If the database is split, combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Remove any extraneous records unless they are relevant to the issue.
5. Delete any objects that do not relate directly to the issue.
6. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
7. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
8. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
9. Unhide any hidden database objects
10. Compile the code. (From the VBA code window, click: Debug-->Compile)
11. Run the Compact/Repair utility.
12. Remove any Passwords, Security and/or login prompts.
13. If a form is involved in the issue, set the Modal and Popup properties to: No
    (Again, unless these properties are associated with the issue)
14. Post the explicit steps to replicate the issue.
15. Test the database before posting.

In other words, ...post a database that we can easily open and immediately see and/or troubleshoot the issue.
Post the explicit steps to see the issue.
And if applicable, also include a clear, graphical representation of the *Exact* results you are expecting, based on the sample data.

developingprogrammerAuthor Commented:
thanks Jim and Jeff for your help on this!

yes Jeff you're right i definitely shouldn't make yall work to see the issue i'm face - after all yall are already helping me so much with my questions. i will bear in mind next time i upload a database to ensure that it follows the general rules you laid out above. thanks!

yup guys i removed the dconcat function and it's better but it's still quite slow i'm not sure why and i've got to investigate i guess but as for the dconcat function causing the slowness, yall are right here so for this dconcat part, it's closed = ) thanks for your help guys!
developingprogrammerAuthor Commented:

i just implemented the always open recordset (i did read about this when reading through the whole of Allen Browne's site but didn't know the performance increase will be so much greater!!!) and it cut down my form opening time by like 5 seconds!!!!! really helps so so much, thanks Jim!!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.