Long Reconnect Times w/Multi-Users?

[(PeteCresswell)]

This is a variation on my prior attempt at a thread "App Runs
Slower w/2 Users Than w/1?".

Front end on C:, back end on a LAN file server.

Code to reconnect tables runs in about 2.5 seconds when I'm the
only one logged in to the front end.

But when one other person has their copy of the front end up and
running, that same re-connect process takes 207 seconds.

It's not a religious issue bc the only time that process runs is
when I prep the front end for distribution before elevating a new
version.

OTOH, if the reconnects are that slow... I wonder if the rest of
the app is similarly affected.

Beeeeg diff there. I don't recall that kind of difference in
other apps I've developed.... could be wrong... but my first
thought is that I'm doing something dumb.

But what?

 

[Tony Toews [MVP]]

But when one other person has their copy of the front end up and
running, that same re-connect process takes 207 seconds.

Once you've relinked the first table open a recordset against that
table. Close it once you've finished relinking the tables.

OTOH, if the reconnects are that slow... I wonder if the rest of
the app is similarly affected.

Beeeeg diff there. I don't recall that kind of difference in
other apps I've developed.... could be wrong... but my first
thought is that I'm doing something dumb.

No, you're not doing anything dumb. This is just one of those tricks.

But while I'm posting visit my Access Performance FAQ page at
http://www.granite.ab.ca/access/performancefaq.htm for other tips.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[(PeteCresswell)]

Per Tony Toews [MVP]:

Once you've relinked the first table open a recordset against that
table. Close it once you've finished relinking the tables.

Wow!...

Execution clock time decreased from over 200 seconds to less than
10.

Thanks.


Any idea why it works?

When I get some time, I'm going to try opening up a little
recordset in frmHome when the app starts up and just leave it
open until the app closes and see if it speeds up overall
performance any. My apps tend tb unbound - grabbing the info
necessary, populating work tables, and then closing the tables on
the back end... so maybe that's sort of analogous the reconnect
scenario... constantly opening/closing tables...

 

[(PeteCresswell)]

Per (PeteCresswell):

When I get some time, I'm going to try opening up a little
recordset in frmHome when the app starts up and just leave it
open until the app closes and see if it speeds up overall
performance any.

At first glance, it *seems* to speed it up a little like 1.2
seconds for a given operation vs 1.3 or 1.4.

But I haven't got a clue as to whether that is significant -
having only done 5 iterations of said operation (loading a screen
with a parent record and three child tables).

Can you think of any reason why I should be hopeful in this
regard?

 

[Guest]

Per Tony Toews [MVP]:

Wow!...

Execution clock time decreased from over 200 seconds to less than
10.

Thanks.


Any idea why it works?

When I get some time, I'm going to try opening up a little
recordset in frmHome when the app starts up and just leave it
open until the app closes and see if it speeds up overall
performance any. My apps tend tb unbound - grabbing the info
necessary, populating work tables, and then closing the tables on
the back end... so maybe that's sort of analogous the reconnect
scenario... constantly opening/closing tables...

 

[Tony Toews [MVP]]

Wow!...

Execution clock time decreased from over 200 seconds to less than
10.

Thanks.

You're very welcome. Glad it worked for you.

Any idea why it works?

It's the time spent in creating and delete the .ldb file and locking
issues, etc. This forces the .ldb file to stay around all the time.

When I get some time, I'm going to try opening up a little
recordset in frmHome when the app starts up and just leave it
open until the app closes and see if it speeds up overall
performance any.

It should.

My apps tend tb unbound - grabbing the info
necessary, populating work tables, and then closing the tables on
the back end... so maybe that's sort of analogous the reconnect
scenario... constantly opening/closing tables...

That is the #1 recommendation for better performance with Access.
<smile> But I'm sure you're familiar with the Performance FAQ at
http://www.granite.ab.ca/access/performancefaq.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[(PeteCresswell)]

Per Tony Toews [MVP]:

But I'm sure you're familiar with the Performance FAQ at
http://www.granite.ab.ca/access/performancefaq.htm

Thanks for the compliment, but no.

Either by accident or design I do most of the stuff describe
there, but

"Note that if you have Name AutoCorrect on this will change the
Subdatasheet property back to [Auto] for each table to which you
make a change. Thanks to Tom Wickerath for pointing this out."

was a revelation to me. Always wondered why my code's changes
reverted sometimes.

Thanks for that one too.


"Server Message Block Signing" sound like a train wreck waiting
to happen.

 

[(PeteCresswell)]

Per Tony Toews [MVP]:

http://www.granite.ab.ca/access/performancefaq.htm

Oops!.... just noticed another one: the bit about deleting and
re-creating links instead of just refreshing them.

I do the delete/recreate bit with my temp table links, but never
got around to modifying my "production" table refresh code. I
guess now's the time....

 

[Tony Toews [MVP]]

"Server Message Block Signing" sound like a train wreck waiting
to happen.

As was the OpLocks problem when it first came out. And for the next
few years.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

Per Tony Toews [MVP]:

Oops!.... just noticed another one: the bit about deleting and
re-creating links instead of just refreshing them.

I do the delete/recreate bit with my temp table links, but never
got around to modifying my "production" table refresh code. I
guess now's the time....

My logic still does the refreshing but then many of my client
databases are relatively small.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/