Help....database working very slow

  • Thread starter Thread starter Rajesh Candamourty
  • Start date Start date
R

Rajesh Candamourty

Dear ALL,

I have an access database with multi user environment for about 75 users.
Everything works fine except the speed. The screens take at least 5-9 sec to
open, I don't know why. Any help is highly appreciated.

This database resides on a network drive of a server. Every user has given a
shortcut in their desktop. It worked fine for the first couple of days, now
it got slower. So I decided to load the front end on the users machine to
make it run fast. I don't see much difference in opening the forms.

Details:
tables - 53
queries - 25
forms - 24 with LOC around 30K
reports - 20
modules - 3 small
I have a jpg displayed in every form with 50kb size. I use a Microsoft
character Peedy for errors and comments.
File sizes - frontend 103Mb, backend 20Mb.

Is there something I am doing wrong to make this work slow.
I am very tired of trying many ways. Any help will be highly appreciated.

Thank you.
Raj.
 
You are extremely lucky that the database hasn't been corrupted! Having more
than one user opening the same database (which is what your shortcut does)
is normally a recipe for disaster!

Split the database into a front-end (containing the queries, forms, reports,
macros and modules), linked to a back-end (containing the tables). Give each
user his/her own copy of the front-end, and put it on their hard drive. Only
the back-end should be on the server.
 
I have already split the database and put the front end in the users
machine, but find no difference. Do you suggest compacting the databse?
Am bit scared about that.
 
I second the Doug response but also, do you have a lot of record deleting or
appending? I would think with so many users that would be a possibility.
You may have to have a routine to compact the data portion on the server on a
regular basis to help with speed.
 
yes, there are a lot of appending, inserting, but no deleting as my boss
wants to have a history of records.

Jackie L said:
I second the Doug response but also, do you have a lot of record deleting or
appending? I would think with so many users that would be a possibility.
You may have to have a routine to compact the data portion on the server
Click to expand...
You are extremely lucky that the database hasn't been corrupted! Having more
than one user opening the same database (which is what your shortcut does)
is normally a recipe for disaster!

Split the database into a front-end (containing the queries, forms, reports,
macros and modules), linked to a back-end (containing the tables). Give each
user his/her own copy of the front-end, and put it on their hard drive. Only
the back-end should be on the server.



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


sec
to given
a days,
now
Click to expand...
[/QUOTE]
 
Check the file size of your data on the server, compact, and check the size
again. This will give you a good indication if the compacting is helping.
Also, backup before compacting if it makes you nervous.

Hope this helps.

Rajesh Candamourty said:
yes, there are a lot of appending, inserting, but no deleting as my boss
wants to have a history of records.
Click to expand...
[/QUOTE]
 
Rajesh,

So far, you have been given some good advice. There are a multitude of
things that can affect speed. One that is often overlooked is the field
sizes in tables. when you create a new table, the default field length
(defined in Tools->Options->Tables/Queries) is assigned to a text field. Out
of the box, Access assigns a value of 255. Many developers ignore this when
defining a field. A lot of long text fields takes up a lot of memory and
unnecessarily increases network traffic. The result is S L O W
Check all your text field lengths in all your tables. Make all of them only
long enough to accept the data required.

The same is true for numeric fields. A Double numeric takes more space than
a Single. A Long Integer takes more than an Integer. Do you really need a
double for a specific field, If not, make it a single.

Another common speed problem is using Domain Aggregate Functions in queries.
They are sometimes necessary, but use them sparingly.

Get a good book on implementing Access in a Multi User Environment. Your
user base is large enough that you need to become familiar with all the
issues surrounding such an environment.

Good Luck
Rajesh Candamourty said:
yes, there are a lot of appending, inserting, but no deleting as my boss
wants to have a history of records.
Click to expand...
[/QUOTE]
 
Just to add to the last reply which hits on quite a few important issues,
make sure in your table structure that your fields are indexed. For non-key
fields have Indexed Yes - Duplicates ok. For Key fields, the property would
be set to Yes - No duplicates.
 
Compacting does not delete data, but removes un-wanted leftover junk.
Compacting is a regular thing that you must do to keep your database
performance correctly. For example, while your front end is 100 megs, I have
a application with 160 forms, 30k loc, and it is only about 6 megs in size,
where yours is 100 megs. The difference in size here is astounding!

So, you need to frequently compact the database to remove extra garbage that
builds up. This is kind like doing some cleaning!

note that compacting also re-indexes and re-organizes the data files, and
can improver performance DRAMATICALLY.

note that you need to compact the back end database on a regular bases. The
front ends really don't need much, and you can set it to compact on close if
you have any code that uses temp tables (temp tables is a bad practice, but
at least a compact and repair on close will negate much of this bad
practice).

Further, for your split database, your users get a front end placed on EACH
pc, and that SHOULD BE a mde, not a mdb file. (a mde ensures that no compile
errors on in your code, and the fact that a mde KEEPS the program in a
compiled state often fixes MANY slow loading form problems.

Further, to eliminate the long delay when forms open, you need in your
startup code to open a table in the back end, and KEEP IT open. Forcing a
persistent connection usually resolves slow response on a network.

You can read up on the other performance things to check here:
http://www.granite.ab.ca/access/performancefaq.htm
(go through the above check list, the results are useally amazing if you
follow the above)

Further, perhaps you network is just slow. Did you check the speed of the
network? You can read about networks, and ms-access, and speed here:
http://www.members.shaw.ca/AlbertKallal//Wan/Wans.html
 
So far, you have been given some good advice. There are a multitude of
things that can affect speed. One that is often overlooked is the field
sizes in tables. when you create a new table, the default field length
(defined in Tools->Options->Tables/Queries) is assigned to a text field.
Out
of the box, Access assigns a value of 255. Many developers ignore this
when
defining a field. A lot of long text fields takes up a lot of memory and
unnecessarily increases network traffic. The result is S L O W
Check all your text field lengths in all your tables. Make all of them
only
long enough to accept the data required.
Click to expand...

The above is complete wrong. ms-access uses variable length fields for all
of the text fields. If you set the length to 5, or 255, and only enter 1
character, then only one character is stored. Old fashion database systems
like the old dos FoxPro, and dbaseeIII, etc DID STORE blanks, so you advice
applied way back then.

however, today, most modern data systems today do NOT pad or store the extra
characters. . So if you leave all of your fields at 255, YOU WILL NOT
EXPERIENCE ANY DIFFERENCE in performance. The really nice feature of this
fact is that you save a lot of disk space. By the way, the max length of a
ms-access record is 2000 characters. If you have 10 fields at 255
characters, then you already exceeded this max length *IF* ms-access did in
fact store the extra blanks, which it does not...
 
Albert,
You are half right. If you do some reading, you will find recommended
exactly what I propose. Although it may not store 255 characters on disk, it
does allocate 255 characters in memory, so 10 fields in memory would be 2550
charcters in memory. Please don't try to say that is not important. Now, in
addition to that, because she has the front end on the server, that means
that each of those extranious characters has to travel up and down the
network. You will experience a difference in performance.

Also, reread my post more carefully. Where did I say anything about disk
space? All I addressed was memory and network traffic. I stand by my
statements.
 
Raj,

In addition to the suggestions you have received from others, here are a few
of my own (some of which might be repeats):

This list is just what comes to mind at the moment--it is not a complete list.

1.) Slow Performance When User Opens an Object with Name AutoCorrect Enabled
http://support.microsoft.com/?id=290181

2.) ACC2000: Slower Performance on Linked Tables
http://support.microsoft.com/?id=261000
Applies to Access 2002 and 2003 as well.

3.) Keep a persistant connection open
http://www.granite.ab.ca/access/performanceldblocking.htm and
http://support.microsoft.com/kb/303528#15

4.) Compact the database often
This causes the queries to be recompiled, with a new optimization plan, the
first time they are run following a compact operation. Queries can become
very slow if they are based on an old optimization plan that is no longer
valid for the data at hand.

5.) Use indexes
You should establish indexes on any fields that are used for sorting or as
criteria in queries. Without indexes, the entire recordset is transferred
over the network prior to any filtering. Note: You do not need to index
foreign key fields, when these fields participate in a relationship created
in Tools > Relationships. In this case, Access automatically indexes the
foreign key fields, although these indexes will not show up in the indexes
window. See attached Word document.

6.) Avoid using Domain Aggregrate functions (DCount, DLookup, DMax, DMin,
etc.)
These are known to be really slow.

7.) If you have a tab control that includes subforms, implementing the
Tab-on-demand technique can be very helpful. I have a Word document at home
that I can send to you that discusses this issue. It is based on a tip
published on the FMS web site:
http://www.fmsinc.com/free/tips.html#accesslatebind. Similar to this tip, do
not have bound combo or list boxes with thousands of records.

Other issues to be aware of include the path specified should not include
several folders that must be traversed. In addition to making this path as
short as possible, make sure that each folder name does not exceed 8
characters in length for the BE path. Also, make sure that your antivirus
software is not configured to scan network files. These tips, and others,
are discussed here:

Microsoft Access Performance FAQ
http://www.granite.ab.ca/access/performancefaq.htm

To avoid page locking, you may want to look into implementing the following
KB article:
PRB: Jet 4.0 Row-Level Locking Is Not Available with DAO 3.60
http://support.microsoft.com/?id=306435


ACC2000: Optimizing for Client/Server Performance
http://support.microsoft.com/?id=208858
Applies to Access 2002 and 2003 as well. This includes the following advice:
"NOTE: If you make changes to fields or indexes on the server, you must
relink the remote tables in Access."

Files on Network Shares Open Slowly or Read-Only or You Receive an Error
Message
http://support.microsoft.com/?id=814112

How to keep a Jet 4.0 database in top working condition
http://support.microsoft.com/kb/303528

And, for good measure, if you have any laptop users who plug their laptops
into a docking station, you might want to be aware of an issue discussed
earlier, which I have reprinted below.

From: Scofield, Calvin D
Sent: Thursday, August 21, 2003 10:48 AM

Hi Melinda,

I had a user in Long Beach that had the same situation. The user had a
laptop, when he plugged the laptop in without the docking station, it worked
fine, but when docked, it slowed to a crawl. It turned out to be the network
cards. The profile had to be changed because the network cards were working
against each other. They disabled the internal network card for the "IN
DOCK" profile. Once that was done, the connectivity was great.

Hope this helps!

Also, I would strongly encourage advising laptop users with wireless
connections to not open the database until they establish a hard-wired
network connection.

Tom
____________________________________________

:

Dear ALL,

I have an access database with multi user environment for about 75 users.
Everything works fine except the speed. The screens take at least 5-9 sec to
open, I don't know why. Any help is highly appreciated.

This database resides on a network drive of a server. Every user has given a
shortcut in their desktop. It worked fine for the first couple of days, now
it got slower. So I decided to load the front end on the users machine to
make it run fast. I don't see much difference in opening the forms.

Details:
tables - 53
queries - 25
forms - 24 with LOC around 30K
reports - 20
modules - 3 small
I have a jpg displayed in every form with 50kb size. I use a Microsoft
character Peedy for errors and comments.
File sizes - frontend 103Mb, backend 20Mb.

Is there something I am doing wrong to make this work slow.
I am very tired of trying many ways. Any help will be highly appreciated.

Thank you.
Raj.
 
Klatuu said:
Albert,
You are half right. If you do some reading, you will find recommended
exactly what I propose. Although it may not store 255 characters on disk,
it
does allocate 255 characters in memory, so 10 fields in memory would be
2550
charcters in memory. Please don't try to say that is not important.
Click to expand...

It is NOT important. You will NOT find ANY measurable difference in a form
load
time buy reducing 10 field lengths in this fashion. it is just not going to
be notable here. Further, the forms size does NOT increase here.
ms-access typically uses up 15 or more megs of ram to run. Lets see, 2550 -
500 (say, 10 fields of 50 vs that of 255). we get: 2050 / 15,000,000 =
0.000136. That represents one 10 thousands of a percent. This is not even
measurable in terms of how much ram ms-access users. You are simply chasing
a wild goose with this issue. Even 10% of a form load would be about .2 of
second for a form that takes two seconds. We are not talking about even 1%
here,
but 1/10,000 of a % here!!! This suggeston is of NO use to improve
perfoamcne
in ANY noticitable fashion here.

Now, in
addition to that, because she has the front end on the server, that means
that each of those extranious characters has to travel up and down the
network. You will experience a difference in performance.
Click to expand...

Changing the location of the front end WILL NOT CHANGE the
amount of local pc memory used. And, changing the location of
the front end does NOT change the ram used for data.

Again: Changing the field length settings WILL NOT change the
load time, nor the amount of data transferred
here.

The 'extra' field length setting for fields does NOT travel up and down the
network, and you will not see ANY difference here.

Those strings in memory are NOT transmitted (and, when the data is
sent, the strings are NOT padded).

It is ONLY
when you put in additional data into those fields does extra network traffic
occur.

If you have a document, or any reference that says otherwise, I would be
most interested to see it, as it is just plain wrong...
 
2550 characters in memory are unimportant these days, by three or more
orders of magnitude depending on the hardware.

I tried an unscientific test, changing a Text field from length 1 to 255
in a table with 100,000 records, compacting the database and restarting
Access 2003, then opening the table datasheet and scrolling to the last
record.

There was no obvious difference in memory usage in these circumstances,
and certainly not the tens of megabytes or more that one might expect
from allocating memory for 100,000 * 255 Unicode characters as opposed
to 100,000 * 1.

Klatuu, what sorts of saving in memory usage do you experience by
minimising the length of text fields? How much is it affected by whether
the fields are indexed or not?
 
Now, in
addition to that, because she has the front end on the server, that means
that each of those extranious characters has to travel up and down the
network. You will experience a difference in performance.
Click to expand...

Uhm.. I'm not sure about this. I dont think the "extranious characters"
travel up and down the network wire regardless of whether the FE is on the
network or local. I'd like to see a reference to this. But in the scheme of
things, this is all minuscule.


Immanuel Sibero
 
Well, for sure, one should actually "set" the correct feild length. If you
came along and said that is lazy, and dumb not to set the correct length for
a field, then I would most certainly agree 100% with that advice. I really
don't want to come down hard here, or somehow say that the advice is really
bad. My only point here, is that if you have a bad performing database,
tweaking, or changing the field lengths is NOT going to fix a performance
problem here.

It is always hard to come in and correct wrong advice, as the person is
honestly trying to help, and give advice. This whole newsgroups is built
around people like you and me trying to help others. And, many years ago,
most, if not all pc databases systems did in fact store, and transfer the
extra characters used. However, ms-access does NOT transfer these extra
characters, and will NOT change performance if you forget to fix, or change
the default field length. I still think it is good advice to set the field
length to the proper length, but with ms-access, this will not change
performance.

At the end of the day, I probably should have left this alone, and not said
anything.....
 
Albert D.Kallal said:
My only point here, is that if you have a bad performing database,
tweaking, or changing the field lengths is NOT going to fix a performance
problem here.

I still think it is good advice to set the field length to the proper
length, but with ms-access, this will not change performance.
Click to expand...


If the field is indexed, it will affect performance. If it isn't indexed,
you are correct.
 
Albert,

All you have to do is to use UNC (Universal naming Convention), change the
path of all your linked tables, example:

If you link tables path are:

g:\Sharedfolder\MyDBBE\dbbe.mdb

change to:

\\servername\sharedfolder\MyDBBE\dbbe.mdb

You will notice a big change,,,,,
 
Cesar,
All you have to do is to use UNC
Click to expand...

UNC? Maybe so
But is it ALL you have to do? I dont think so.
For example, does the use of UNC cure the file lock "dance" that multiuser
Access does?


The performance FAQ at Tony's site is tried and tested.




Immanuel Sibero
 
Immanuel,

Thanks for your reply...

my reply was for Rajesh, not for you Albert... Sorry,

I was focused only on the original main question (Slow database access),

Thanks
 
Back
Top