Slower while backend is in use

[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

 

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

 

[Lars Brownies]

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

 

[Arvin Meyer MVP]

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.

 

[Tony Toews [MVP]]

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/

 

[David W. Fenton]

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

I thought that recent versions of Access (2002 and later?) fixed the
problem of slow domain lookup functions on linked tables?

In any event, you don't have to write your own, just use Trevor
Best's tLookup functions:

http://easyweb.easynet.co.uk/~trevor/AccFAQ/downloads.htm#baslookup

 

[Arvin Meyer MVP]

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.

 

[Lars Brownies]

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.

Tony

Lars

 

[Lars Brownies]

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

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.

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Lars

 

[Tony Toews [MVP]]

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/

 

[Arvin Meyer MVP]

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

 

[Arvin Meyer MVP]

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

 

[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).