Access 2003 database slow to open large tables across network

[baffled100]

Hi,

I have an Access 2003 database that has started misbehaving somewhat suddenly. The actual database works fine when on my local system, but across the network, it is very slow to open a particular form based on a table of about 24000 records. I have a combo box to look up people in the table that uses a filter on the table and that is slow also for the first person you are searching for. Subsequent searches are faster. It's as if once the table has been opened, the speed improves.

Other details......compact and repair takes at least 4 times longer than usual. The error message "You can't carry out this action at the present time" periodically will pop up, but not necessarily at the same places in the program. The 'Not Responding' program message appears more often.

I rebooted the server yesterday and it seemed to improve for a day, but it is back to being slow again. I checked to see if there has been any Office updates installed prior to this behavior and there has not been. I did install some Windows Server 2008 updates on Monday night and rebooted the server, but this problem started on Thursday....

This seems to be strictly related to opening a large table over the network for the first time. The rest of the database, ie menus, reports etc open fine.

And I have copies of the same database from prior to this behavior on the live database and they too behave the same way--slow. So I don't think it is a problem with the database itself per se.

This database is not split although I will be splitting it soon. But again, it was working fine without being split up until last week. I had not made any changes to anything in the database prior to it being slow.

The network does not seem to be slow for other uses.

Does anyone have any ideas? I'm fresh out....

Thanks!

 

[lameid]

Client and/or Server side virus scanning can often be a culprit but I'd expect Client side scanning more so than server to cause a problem. Although usually the worst bottleneck is the network itself...

I'd be curious to direct connect a PC to the server after hours and see if it behaves. If it does, it is not the client and server but the network or some sort of concurrency issue...

Another thought, defrag the Harddrive the database file is on. Ideally the RAID on the server and cache keeps this from being a real issue but I've actually created separate logical volumes for high use databases before (usually all access files on the same volume so file growth is not competing with excel and word documents for contiguous space).

But whatever is killing your performance is made much worse by using the application objects over the network.

 

[baffled100]

Thanks for your reply! I disabled Symantec Protection on the server and on a couple workstations and that made no difference.

The server is on a defrag schedule, so the drive is defragged weekly. Also, this slow behavior is not related to how many users are accessing the database. It happens to me when I am working on it at 10:00 pm!

Trying a direct connect is a good idea....thanks!

 

[lameid]

10:00pm? Any maintenance going on then, backup is the one that comes to mind?

 

[baffled100]

Thanks for your reply.....our backup runs at 11:30, but I didn't necessarily mean 10:00 pm every night, it was more to make the point that the database is as slow after hours as it is during the day. And it is just Access databases that seem to be having an issue over the network. Our financial software uses with large files across the network also and there has not been any slowdown for those users. I tried using the database with Access 2007 (instead of 2003) and it did not make any difference. And it happens on all workstations so it's not just a corrupted copy of Access. I'm stumped...

 

[lameid]

I ran across the below FAQ... A decompile command line switch followed by compact and repair jumped out at me as does turning off Auto Correct. I'm curious if splitting has made a big difference.

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

 

[baffled100]

Thank so much for your reply.....yes, I've been through that list a thousand times....

Auto Correct is off, and I haven't split the database yet--the issue was sudden, like overnight so I don't really think it is a split vs non-split issue.

I may try the decompile suggestion, but it's not that the database starts slowly, that is quite fast actually, it's only when accessing a large table through a form or query.

This is a true mystery......