speeding up the performance of a split database

[Paul James]

I've got an Access 2002 database in an mdb file that being used on a
network. The back end is on a network server, and users in 4 different
network locations (cities) have the front ends in their local hard drives.
These front ends on the local drives have links to tables in the back end
database. However, there are never more than 4 to 6 users that would have
their front at any one time.

Nevertheless, the database runs slowly, whenever it opens. Forms and
reports can take a full minute to open, and other operations can take what
seems like a long time. I've read that some of the things you can do to
improve performance are:

1. Index all fields used for query criteria,
2. Use saved queries as record sources of form and reports instead of SQL
statements,
3. For data that doesn't change often, keep the table in the front end
instead of linking to a table in the back end and
3. Keep the back end database as close to the root directory of the network
server as possible instead of keeping it in a folder several levels below
the root.

Are there any other things I can do to speed up the performance of this
database?

Thanks in advance,

Paul

 

[Albert D. Kallal]

1. Index all fields used for query criteria, Good

2. Use saved queries as record sources of form and reports instead of SQL
statements,

This likely makes no difference.

3. For data that doesn't change often, keep the table in the front end
instead of linking to a table in the back end and

Yes...that is a possible help.

3. Keep the back end database as close to the root directory of the
network
server as possible instead of keeping it in a folder several levels below
the root. Yes...not bad.

Are there any other things I can do to speed up the performance of this
database?

Well, one thing to ask is how fast is your connection? I am surprised you
are getting useable performance already! (you must have some of the best
wide area connections that money can buy...as usually a wan is not even
close to the needed speed).

I outline the problems...and solutions of using a wan here:
http://www.attcanada.net/~kallal.msn/Wan/Wans.html

 

[Albert D. Kallal]

Oh, and some of the best tips for speeding this up can be found here:
http://www.granite.ab.ca/access/performancefaq.htm

from the above..try the keeping a persistent connection open..that is
usually the largest increase...

 

[John Buck]

Paul

We have a similar problem hover in our setup front and back ends reside in
the same server along with the MDW file. The users are in our building and
the server is across the complex centrally located. Our response time vary
and never for the same reason. There can one user accessing any of the
databases to several people in the same or multiple databases. Time of
occurrence varies. We are at a loss as to what to do. As you have we have
tried various methods to speed up the databases. We have approached our LAN
IT to solve the issue. What we get from them is it must be a MS Access
issue or a Jet Engine issue go put it on SQL Server.

I have a problem with their answer. Why does the response time vary so
widely? There aren't enough users to warrant going to SQL Server. How can
the Jet Engine be an issue? (all our users are using version 2000 same patch
upgrades). If the problem is Access then we will have to suffer through.
On the other hand if it's the network, router or server we may still be
stuck with the issue but in ether case we will have an avenue to pursue for
better results.

John

 

[Chris Mills]

If you're on a WAN, and depending on processing time (such as a bunch of
similar reports), it can be worthwhile to download critical tables to the
Front-End for temporary processing.

But realistically, I've just lost a customer with a multi-city environment,
from MS-Access to a web-based app. I'm not really sure of the details (and
dont care), but a web-based app was cheaper than, say, Terminal Server which
was another obvious option.
(TS has become rather popular here, but I don't know why because it is
expensive)

I regard MS-Access on a WAN as impractical, unless you want to get into
"tricks" like I suggested. I do not know how effective SQL Server (ie true
client server) would be.

I don't think (4) "disk organisation" is a major consideration when the
hold-up is the WAN.

Chris

 

[Albert D. Kallal]

Paul


I have a problem with their answer. Why does the response time vary so
widely?

Without knowing your setup, that answer is hard.

We all assume you have a good designed application? Right?

We all assume that you asked and looked into the speed of the lan (or
wan)...right?

And basic stuff like indexing on searching ing field is present?

On the other hand if it's the network, router or server we may still be
stuck with the issue but in ether case we will have an avenue to pursue
for
better results.

The #1 thing to try is to always force a persistent connecting

I am assuming you got a split database, and it is setup correctly by
the people who built the database application. So, you have
things like giving EACH user a mde file (NOT a mdb).

And, fields that are used in joins, and searching are indexed...right?

Ok...got it split..and each user gets a mde (not a mdb)

Next, I would check and go through Tony's list here:
http://www.granite.ab.ca/access/performancefaq.htm

And, assuming your lan speed is ok..(did you bother to ask???). You can read
about his issue here:
http://www.attcanada.net/~kallal.msn/Wan/Wans.html

Ok, at this point, we looked at the basic stuff,and have eliminated
the setup and know problems with the setup.

The next thing to look at is your designs, and are they
setup for efficient use of the "limited" network resources?

For example, I think we all agree it would be silly to pull ALL users
accounts into a instant teller machine BEFORE you ask the user what account
number to work on.

So, then why so often do we see ms-access forms bound to a table...but NO
restraints are placed on the records for that form? It really is the side
of laziness at work here! (and, I much guilty in this regards too!).

However, I have NEVER talked to anyone who did not agree that loading up a
teller machine without restrictions on what account number is good idea.
And,
from that logic, it don't make sense to load up a ms-access form without
some restraints, or telling the form what record to load. Restricting makes
perfect sense.

So, in place of simply opening up a form to a bunch of invoices in a
table...we ASK the user what invoice to work on, and then use the
docmd.OpenForm "where" clause to restict the form to the ONE INVOICE record.

I give some ideas on this approach here:
http://www.attcanada.net/~kallal.msn/Search/index.html

So, the best common sense rule we have is to work with as few records as
possible at a given time.

If you only got a few users..and small tables (say only 50,000 records),
then your application should run fast.

It is possible that you network is not up to this task....

 

[Albert D. Kallal]

I regard MS-Access on a WAN as impractical, unless you want to get into
"tricks" like I suggested. I do not know how effective SQL Server (ie true
client server) would be.

Using sql server as the back end gets you true client to server...so, that
approach works VERY well
(even on a lan). Of course, this assumes one knows how to use sql sever, and
has good designs that
limit the records being transferred. Hence, moving the back end data to sql
sever is certainly one
good solution to using ms-access on a wan...and it is usually very cost
effect.

As for Terminals Services being too expensive? It really depends. If you are
faced with re-writing an applications, then $3000 dollars worth of
development time don't go very far at all. Yet, $3000 worth of terminal
services clients gets you a good number of remote users.

So, for allow a few remote users...then the cost of TS can OFTEN easily be
justified..and is often MUCH cheaper then re-writing the application.

It really depends on the environment you have. So, using remote desktop,
sql server, replication etc just means that ms-access gives you a LOT of
choices...and which one is the best will dependent on the situation.

 

[Tony Toews]

We have a similar problem hover in our setup front and back ends reside in
the same server along with the MDW file. The users are in our building and
the server is across the complex centrally located. Our response time vary
and never for the same reason. There can one user accessing any of the
databases to several people in the same or multiple databases. Time of
occurrence varies.

Would the common factor be the 2nd and subsequent users into the app
have terrible performance? If so the first item on the following
standard blurb is likely the answer. But the other two can also be
troublesome.

The three most common performance problems in Access 2000 or newer
are:
- LDB locking which a persistent recordset connection or an always
open bound form corrects (multiple users)
- sub datasheet Name property set to [Auto] should be [None]
- Track name AutoCorrect should be off

If the problem is for everyone when starting up the MDB then it likely
needs a decompile.

For more information on these, less likely causes, other tips and
links to MS KB articles visit my Access Performance FAQ page 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

 

[Paul James]

Thanks for your reply and the link to your web page, Albert.

Our WAN (between cities) is a T1 running at 1 megabytes, and our intraoffice
LAN is 100 megabytes.

question: you said "try the keeping a persistent connection open." Could
you shed any light on how to do this? I couldn't find it in Access help.

Paul

 

[Paul James]

Thanks for your reply, and the link to your MS Access Performance FAQs,
Tony.

I have a couple of questions about what you said in your response:

You said that one problem was "LDB locking which a persistent recordset
connection or an always open bound form corrects (multiple users)."

1. How do you set up a persistent connection?

2. When you use the term "always open bound form" do you simply mean that
once a form (that's bound to an underlying recordset) is open, you keep it
open without closing it, so you don't have to requery the recordset?

3. I know how to compile the VBA code, but what do you mean when you say the
mdb file needs to decompile?

Thanks again

Paul

 

[Albert D. Kallal]

Thanks for your reply and the link to your web page, Albert.

Our WAN (between cities) is a T1 running at 1 megabytes, and our
intraoffice
LAN is 100 megabytes.

Yes...so, you got 100 times less speed!!!

question: you said "try the keeping a persistent connection open." Could
you shed any light on how to do this? I couldn't find it in Access help.

That "persistent" connection trick is from Tony's list. What this simply
means that when the front end starts up, it opens a table in the back end
(any table is just fine here....). You then KEEP this connection open (ie:
don't close the table while the rest of the application runs). You can even
just open a form that attached to a table, and then minimize that
form.....this will keep a connection open to the back end at all times (and,
thus the HUGE overhead of network open, network connecting, and security
checks by the os is eliminated..since the file is already open. It will not
fix the bandwidths issue in terms of data transfer rate...but it certainly
can drop the open time for a form etc. by dramatic amounts when a form or
recordset needs to be opened.

So, a persistent connection simply means to keep some table open at all
times, and thus eliminate the slow network "open" and "close" and "security"
stuff that occurs each time something is opened. So, any old table needs to
be kept open...and it will speed up the opening of all other tables....

 

[Albert D. Kallal]

Thanks for your reply, and the link to your MS Access Performance FAQs,
Tony.

I have a couple of questions about what you said in your response:

You said that one problem was "LDB locking which a persistent recordset
connection or an always open bound form corrects (multiple users)."

1. How do you set up a persistent connection?

Just open a table to the back end..and keep it open (how you do this is up
to you...you can have a small form opened at startup, and then make it
invisible, or simply have some code that opens a reocrdset to a global var).

2. When you use the term "always open bound form" do you simply mean that
once a form (that's bound to an underlying recordset) is open, you keep it
open without closing it, so you don't have to requery the recordset?

No, not really. The persistent idea is simply to always FORCE and KEEP the
connection from the front end to the back end opened. This issues is NOT
related to keeping a PARTICULAR form opened. So, no, we are NOT trying to
change your designs, or trying to eliminate some re-querying of the forms
reocrdset. The thing we are trying to eliminate is the VERY SLOW open
processing, and the large number of things that are done when the connection
is created. So, the table that we open can be any old table...and not for
some particular form that you have.

3. I know how to compile the VBA code, but what do you mean when you say
the
mdb file needs to decompile?

Because this is an issue...often we suggest using a mde (that forces your to
compile your code!!). Anyway, to compile your code, just whack ctrl-g, and
bring up the development (code window). You can now go debug->compile (you
mentioned, that is clear, and you know how to do this). Using a mde also
ensures that your application is in a compiled state..

If you don't want to de-compile, then just create a mde...and try that....

You can find more about de-compile here:

http://www.mvps.org/access/bugs/bugs0008.htm

 

[Paul James]

Thanks for all the info about the persistent connection, Albert.

You're saying that using an mde file is better than and mdb file for the
front end. Why is that? Does it speed things up?

Are there any disadvantages to using mde files? I've never created them,
because I'm not a contract developer, and I don't have the Access Developer
version that I understand I need to create mde files. But I'd be willing to
get it if there were a substantial advantage, especially if it would speed
things up.

Using a mde also ensures that your application is in a compiled state..
If you don't want to de-compile, then just create a mde...and try that....

I'm still confused about this, but maybe it's just semantics. Are you
saying that I SHOULD compile my VBA code to speed things up? It sounds like
it, but then you also use the word de-compile, which would seem to be the
opposite of compile. Please clarify.

Thanks much.

Paul

 

[Albert D. Kallal]

Thanks for all the info about the persistent connection, Albert.

You're saying that using an mde file is better than and mdb file for the
front end. Why is that? Does it speed things up?

It does speed things up a bit. Computers are so fast today, that often it is
not much of a issue. However, the mde is smaller (no source code), the mde
cannot be modified by end users, and a mde is ALWAYS in a compiled mode.
Often, with a mdb, it can become un-compiled for many reasons. When this
happens, then the speed difference is noticeable, and even some bloating of
the FE can occur. So, a mde is simply a tighter and safer package for
distribution. It is you "final" product. It also ensures that NO compile
errors exist in the final product for your uses.

Are there any disadvantages to using mde files? I've never created them,
because I'm not a contract developer, and I don't have the Access
Developer
version that I understand I need to create mde files. But I'd be willing
to
get it if there were a substantial advantage, especially if it would speed
things up.

You don't need the developers edition. Just go tools->database
utilities->create mde file (try it). (of course, you always keep the mdb for
you..and don't loose it!!).

I'm still confused about this, but maybe it's just semantics. Are you
saying that I SHOULD compile my VBA code to speed things up? It sounds
like
it, but then you also use the word de-compile, which would seem to be the
opposite of compile. Please clarify.

The suggestion to de-compile is kind of a hammer..and it can often fix some
strange behaviors. So, de-compile does not apply to a mde..but just a way to
un-scramble a bad behaving mdb file. Don't worry about the de-compile issue
too much.

 

[Paul James]

Again, you've enlightened me to some things I wasn't aware of. I didn't
know you can create an mde from a Professional version of Access. I'll have
to try it out.

Are there any disadvantages or "issues" in using an mde, other than the
small amount of admin overhead in creating and tracking the extra file for
deployment?

 

[Albert D. Kallal]

Are there any disadvantages or "issues" in using an mde, other than the
small amount of admin overhead in creating and tracking the extra file for
deployment?

Hum, I can't think of any.

In some cases, I seen applications where forms or reports are modify by
code..and then saved. You can't do that with a mde...but then again,
applications should not modify things like that anyway. I mean, if the final
users need to modify reports..then obviously a mde can't be used...

 

[Paul James]

Sometimes I use VBA to make form controls visible or not visible, and also
to enable or disable command buttons. Can this be done in an mbe?

 

[Albert D. Kallal]

yes, that works fine.

And, in a report, if you have a bound text box control, you can still modify
the "source" of the textboxes....

And, the same goes for a sub-form contorl...are you free to change what
sub-form displays via code...

 

[Paul James]

Great.

Thanks (again) for all this info, Albert.

Paul

 

[Tony Toews]

Are there any disadvantages or "issues" in using an mde, other than the
small amount of admin overhead in creating and tracking the extra file for
deployment?

One is that you can't use PrtDevMode to update printer info. However
this is highly unusual and seldom used. As well as being only for
advanced VBA types.

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