Performance issue

[Rabbit]

Dear all,

recently i have big performance problem reported by my client, basically
they have network of around 80 PCs/Laptops, only around max. of 10
concurrent users accessing the database.

Right now when they access a Form(Master/Child design, less than 10,000
records on both Master and Child tables), they experience extremely low
speed even for locating a master record and its child records into the form.
I have no idea why would this happened, because i have deployed many
programs on various client site, this is my first time having this problem.
The following are the things I have done for problem diagnosis:
1) remove all Dcount() or Dsum() calling, all using recordset to retrieve
records
2) make sure the master and child tables have proper indexes defined

Now, I'm suspecting some problem on client's network? does anyone have
experience on this issue, any best practise I should do for performance
issue?

Any help will be very appreciated!

Rabbit

 

[Guest]

Hi Rabbit,

Are you saying that you are allowing users to open a bound form, with a
recordset that includes 10,000 records? If so, ouch! That's an incredible
amount of network traffic that would be generated even if only one page of
records was returned at a time. In general, you want to use an unbound form
that allows a user to find the record that they want to work on, and then
just open the form with that single record.

Try the suggestions that I've written here:
Implementing a Successful Multiuser Access/JET Application
http://www.access.qbuilt.com/html/multiuser_applications.html


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Dear all,

recently i have big performance problem reported by my client, basically
they have network of around 80 PCs/Laptops, only around max. of 10
concurrent users accessing the database.

Right now when they access a Form(Master/Child design, less than 10,000
records on both Master and Child tables), they experience extremely low
speed even for locating a master record and its child records into the form.
I have no idea why would this happened, because i have deployed many
programs on various client site, this is my first time having this problem.
The following are the things I have done for problem diagnosis:
1) remove all Dcount() or Dsum() calling, all using recordset to retrieve
records
2) make sure the master and child tables have proper indexes defined

Now, I'm suspecting some problem on client's network? does anyone have
experience on this issue, any best practise I should do for performance
issue?

Any help will be very appreciated!

Rabbit

 

[Tony Toews]

Right now when they access a Form(Master/Child design, less than 10,000
records on both Master and Child tables), they experience extremely low
speed even for locating a master record and its child records into the form.
I have no idea why would this happened, because i have deployed many
programs on various client site, this is my first time having this problem.

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

 

[Rabbit]

Thanks Tom, the problem has fixed today, once I COMPACT server's MDB, the
performance issue resolved.

The user reminds me the performance problem happens since last time I added
a new field(non index one) into the master table, but no one did Compact
after db update. Probably thats cause indexes of that master table
outdated.

Can anyone share your actual experience about maximum number of concurrent
user on your developed application? and the maximum database size that the
application use? of course this maximum value won't impact application
performance!

Cheers,
Rabbit

 

[Guest]

Hi Rabbit,

I'm glad to hear that your problem has been resolved. I usually always
compact a back-end database during the same time that I am creating a back-up
of it, which, of course requires that all other users be out of the database.

I recommend deleting the linked table from the front-end database,
compacting it, and then re-establishing the link whenever you make any type
of design change to the table in the back-end database. I do this for any
type of design change--indexed field or not. (Actually, I don't know for sure
if the compact is required, but it's so easy to do in any case). You will
find this advice in my multiuser application paper. This would require
providing updated copies of the FE database to each user.

Regarding maximum number of users and max. database size: There are so many
variables involved, it is impossible to give cut and dry numbers that apply
in all cases. Variables include how well the database is designed--for
example are indexes applied appropriately, are users allowed to open bound
forms with huge recordsets, etc. Are users read only (you can have more
simultaneous read only users versus read/write users). What type of data
access methods have been employed (ie. bound forms or disconnected
recordsets)? Also, the reliability and speed of the LAN (Local Area Network)
plays a big role.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Thanks Tom, the problem has fixed today, once I COMPACT server's MDB, the
performance issue resolved.

The user reminds me the performance problem happens since last time I added
a new field(non index one) into the master table, but no one did Compact
after db update. Probably thats cause indexes of that master table
outdated.

Can anyone share your actual experience about maximum number of concurrent
user on your developed application? and the maximum database size that the
application use? of course this maximum value won't impact application
performance!

Cheers,
Rabbit