Linked Tables causing slow response

[dpimental]

I have tried separating the tables from my database application. So, I have a frontend db with everything but tables, and a backend db with just the tables.

But the system runs very slow when doing data entry (although there are functions being called on the after_update event).

It seems 5 times faster when the tables are in the same database as the data entry forms.

Also, I work for a company that store all computer files on the network. You can't save anything to your hard drive.

Any ideas? Is this a record locking issue?

I have tried turning the front end into an mde, but that doesn't seem to help

David Pimental
(US, Oh)

 

[Ed2020]

I find linking the tables with a UNC file path (\\ServerName\ShareName\SubDirectory\MyDB.mdb) makes a big difference. You may want to give this a try.

Ed Metcalfe.

Please do not feed the trolls.....

 

[JerryKlmns]

Do you use lookup tables? Any chance you move them to FE and trigger an update on start up?

 

[dpimental]

I do not use lookup tables. But on update of a field, it runs several calculations, that update the data in the tables on the front end?

David Pimental
(US, Oh)

 

[JerryKlmns]

David sorry, didn't understand your question..

 

[dpimental]

Are there any tips about settings in access that would cause slow downs such as ...

enable DDE Refresh ... Mine is set to be enabled
OLE/DDE Timeout(sec) ... Mine is set to 30
Refresh Interval(sec) ... Mine is set to 60
Number of Update Retries .. Mine is set to 2
ODBC Refresh Interval(sec) Mine is set to 1500
Update Retry Interval(msec) Mine is set to 250

Also, I am opening db in shared mode

And the default record locking is for edited record

And I have checked the "open databases using record level locking"

Under tables/queries I have run permissions set to user's

Any ideas anyone?

David Pimental
(US, Oh)

 

[lameid]

So if the file is on the network and it is not split, and the same number of users are in it, it is faster? Seems odd that it would make a noticeable difference. Even then it should be slower as there is more objects clogging up data access. Please note that Access over a network is slower than Access on just your computer. Also note that as more users use the database performance declines (probably exponetially).

Please tell me that each user has a home directory and their own copy of the front end? FE's can do obnoxious things after ACC97 (or is it after 2000) if they are shared.
Also fight to get them local as Access FE's are more like applications that need frequent updates than files. You would keep a central master or published file on the network and then have some proceudre for updating it (i.e. copy it in the logon script OR use a solution in one of the Faq's to copy if it is upgraded).

When I upgraded to Windows Server 2003 I noticed a significant peformance hit to Jet databases. I think oppurtunistic locking being on had something to do with it.

Microsoft article id: 296264

I also isolated the Jet databases on a partition so they would not have to contend with the constant saving of other types of files to disk and therefore deal with more file fragmentation.

I believe both these solutions may help your situation as well as regular compact and repair of the database files in question.

 

[ZOR]

I might be entirely wrong, but when I first made a front/backend db, I could not believe how slow it was until I found this, and hope it helps:

http://support.microsoft.com/?kbid=200600

 

[lameid]

Zor,

That's worth a star... I had no idea.

The 2002/2003 article id is 290181.

And because MS is notorious for disappearing articles...

When a user does not have design permissions and autocorrect is turned on, it can slow down database performance.

Fix it by going to Tools, Options, and the General Tab. There uncheck Track name AutoCorrect Info box. This is a per database setting (implied in the article and tested by me in ACC2003).

Autocorrect was introduced in Access 2000 so clearly earlier versions are not effected. There was no mention of it for Access 2007 so either noone has complained yet or they fixed it. Office 2007 is such a resource dog, I did not keep it installed. I made the mistake of trying to run it with only a GB of RAM.

 

[JerryKlmns]

For sloooooow performance chek this out

http://www.fmsinc.com/tpapers/faster/