response times too slow

[John Milbury-Steen]

Hi Access gurus,
I have a database at work, and sometimes it takes up to 100 seconds for
some of the forms to load. How can I speed it up?
Details. The database is divided into a front end and back end.
Standard client-server setup. Eight workstations, usually 3 or 4 using it
simultaneously. Front and back end are each about 8 MB. Access 2003 for
XP. I Compact and Repair front end and back end once a week. The tables
are indexed on the key field. The server uses Windows NT. The response
time does not seem to depend on how many users are logged on. My own
workstation always has excellent response times of 1 -3 seconds. I have 1
GB of RAM; the other workstations have 512 MB. About a month ago,
everybody's response time was equally good: 3-5 seconds. Something changed.
Client solutions. Increase everybody's RAM? Speed up each workstation
with CleanDisk and Defrag? On each workstation eliminate unnecessary
processes?
Server solutions: Get somebody to defrag the server regularly?
What is most likely to work?

 

[Allen Browne]

John, a good place to start would be the Performance FAQ by Tony Toews:
http://www.granite.ab.ca/access/performancefaq.htm

There are lots of issues there, such as Name AutoCorrect and Subdatasheets.
Work though the list, and it should get you back on top again. Unless these
users are running heavy duty apps like Photoshop the need buckets of RAM at
the same time as their databases, 512MB should not need upgrading.

 

[Albert D.Kallal]

I Compact and Repair front end and back end once a week

Hum, I still assume that you got the Front end on each pc..right??

The best trick you can do here is to keep a persistent connection open from
the FE to the BE, I find 9 out of 10 times this fixes the "delay" in
loading a form...

 

[John MilburySteen]

"> Hum, I still assume that you got the Front end on each pc..right??
Yes.

The best trick you can do here is to keep a persistent connection open
from the FE to the BE, I find 9 out of 10 times this fixes the "delay" in
loading a form...

You mean, have one user always logged in?

 

[Albert D.Kallal]

"> Hum, I still assume that you got the Front end on each pc..right??
Yes.
You mean, have one user always logged in?

No, the above will not fix delays

A persistent connection means that your application in the start code opens
a table (any table will do) in the back end file. You KEEP that table open
at all times. This will thus "force" a connection between the FE and the BE
at all times.

Some people actually open a form that is simply bound to a table in the BE,
and then immunize it.

Some just declare a global recordset, and open to that in their startup
code.

Regardless, keeping open the connection thus eliminates the LONG delay of
the locking (ldb) files that occurs when you load a form, or open a table. I
seen it take forms form 45 seconds to less then one second when you do
this...

So, make sure each user has a copy of the FE on each computer, and do the
persistent connection....

 

[connie]

My Main startup form has a subform tied to a table, T_VersionNbr. This
Startup form is minimized when a button opens another form. The Main form
remains open, but minimized. When the second form is closed, the main form
resizes.

Since there is a Table open (T_VersionNbr) - linked - on the main form, does
this meet the criteria of keeping an "open" connection"?

I have a FE/BE setup and the first time a report is opened, there is quite a
long delay previewing it. Does the "open connection" help with this problem
also?

Thanks,
Connie

 

[Albert D.Kallal]

linked - on the main form, does
this meet the criteria of keeping an "open" connection"?

Yes, if that table is from the back end...and it is open, then yes, you got
a open, or "persistent" connection.

I have a FE/BE setup and the first time a report is opened, there is quite
a
long delay previewing it. Does the "open connection" help with this
problem
also?

It does if you are experiencing that long delay in opening most forms. Since
you got a connection open, then likely elsewhere needs to be looked at for
the performance problem. However, you likely have this problem with a
un-split database also. So, it is possible that you just got a lot of data.
If most forms load very fast and ok, then you likely just got a report that
is slow.

Perhaps you have some sub-reports that linked on fields that are not
indexed. Or, perhaps more then one table involved, and again no indexes
between fields used for a join etc. (same goes for a filter condition).

You also want to turn off the sub-datasheet options. I would work my way
through the follwing list:

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

If you exhuased the above list, then start looking at indexes...

 

[connie]

Thank you for your thoughtful reply. I will look into each of the
suggestions you
put forth. I haven't used indexing very much so that may be one of the
major slowdowns. I also used Lookup fields in my tables, and since I've been
reading on here, I now realize that this SHOULD be a no-no. At this time, I
don't think I'm able to change my database to remove these lookups without a
lot of reprogramming of forms, queries and reports. But any new tables
added, or new databases created will surely take this into consideration.

Meantime, I will review other things that I might do to hasten the first
viewing. I do tend to use a lot of forms and subforms, and reports with
subreports.

Thanks again for your review.
Connie