Slower while backend is in use

L

Lars Brownies

Hello,

My app gets started by a starter mde that runs some checks, updates the FE
if a newer version is
present, and then opens the FE.

The checks consists of a few dlookups. I notice that if I'm the first user
to start the app, the app starts very rapidly. However, when another user is
already logged in, it takes far more time (about 20 seconds).

The same behavior I see when I'm automatically relinking the front end.
Relinking when I'm the only Back end's user is fast. Relinking while backend
is in use by someone else, takes much longer.

I'm quite interested in why this behavior occurs. What happens internally
that Access becomes slower. Someone have a clue?

Lars
 
A

Arvin Meyer MVP

A few things to try:

1. For the duration of the process, close your email client and turn off
your AV program.
2. Login first, then go to the second user's machine and log in. Is the
process longer?
3. Isolate the process which is taking longer, i.e. running the checks,
replacing the front-end, relinking.

I would suggest that if the Front-End of the replacement source database
file is linked to the Back-End file before using the code to relink, that
relinking can be avoided altogether, since it will have already been
accomplished.
 
L

Lars Brownies

Arvin Meyer MVP said:
A few things to try:

1. For the duration of the process, close your email client and turn off
your AV program.
2. Login first, then go to the second user's machine and log in. Is the
process longer?
3. Isolate the process which is taking longer, i.e. running the checks,
replacing the front-end, relinking.

I would suggest that if the Front-End of the replacement source database
file is linked to the Back-End file before using the code to relink, that
relinking can be avoided altogether, since it will have already been
accomplished.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I think it is the dlookups that take a lot more time, but I will do the
checks you proposed. Thanks.

The relinking part is a separate process which is only being done when the
new mde is copied to other locations since this app runs separately on
multiple locations for different groups of users.

Lars
 
A

Arvin Meyer MVP

Lars Brownies said:
I think it is the dlookups that take a lot more time, but I will do the
checks you proposed. Thanks.

The relinking part is a separate process which is only being done when the
new mde is copied to other locations since this app runs separately on
multiple locations for different groups of users.

If it truly is the DLookups, change them to recordsets which should run
faster, particularly on larger recordsets.

As far as relinking goes, it is to the same data source(s), you can still
relink them regardless of what the front-end changes are.
 
T

Tony Toews [MVP]

Lars Brownies said:
The checks consists of a few dlookups. I notice that if I'm the first user
to start the app, the app starts very rapidly. However, when another user is
already logged in, it takes far more time (about 20 seconds).

Open a recordset based on a small table in the backend or open the BE
MDB as a database then do your Dlookups.
The same behavior I see when I'm automatically relinking the front end.
Relinking when I'm the only Back end's user is fast. Relinking while backend
is in use by someone else, takes much longer.

To relink either
1) open the BE MDB as a database
2) after you relink the first table open that table as a recordset.
Then continue relinking the tables.
I'm quite interested in why this behavior occurs. What happens internally
that Access becomes slower. Someone have a clue?

The problem is that Access wants to work with the LDB file and takes a
chunk of time doing it. So you do that work once and let the other
DLookups or relinking tables reuse that same connection.

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/
 
A

Arvin Meyer MVP

David W. Fenton said:
I thought that recent versions of Access (2002 and later?) fixed the
problem of slow domain lookup functions on linked tables?

That was my understanding as well, but the OP didn't post his version, and
remained convinced it was the DLookups.
 
L

Lars Brownies

Tony Toews said:
Open a recordset based on a small table in the backend or open the BE
MDB as a database then do your Dlookups.

I will try this suggestion.
To relink either
1) open the BE MDB as a database
2) after you relink the first table open that table as a recordset.
Then continue relinking the tables.


The problem is that Access wants to work with the LDB file and takes a
chunk of time doing it. So you do that work once and let the other
DLookups or relinking tables reuse that same connection.

So when other users already have logged in, it seems that it takes more time
for Access to work with the LDB file.

Lars
 
T

Tony Toews [MVP]

Lars Brownies said:
So when other users already have logged in, it seems that it takes more time
for Access to work with the LDB file.

Exactly.

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/
 
A

Arvin Meyer MVP

Lars Brownies said:
That's a bit strong :) I wrote:
"I think it is the dlookups that take a lot more time, but I will do the
checks you proposed."

This is Access2002.

Originally, DLookup did take several times longer to run than a recordset,
so they were avoided. I'm pretty sure that by Access 2002, that problem was
alleviated. I find that indexes are usually the cause of performance
problems. Either there aren't any, or they're improperly designed. In any
case, Tony Toews has an excellent web page FAQ on performance:

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

David W. Fenton

Originally, DLookup did take several times longer to run than a
recordset, so they were avoided.

Recordset? No, DLookups were fine on local tables but had speed
problems on linked tables.

Of course, in a lot of contexts, they don't make sense (particularly
in SQL).
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top