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

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

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.

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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!!!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.