using Access 2003 (11.8204.8221) SP3
We have a split database front-end, back-end, (45mb format 2000)
the back-end in on a NAS on a 10/100 network
when a single user use the database the performance are good, but when a second user connect to the database the performance of queries are really bad. Even if the second user does nothing and stays in the main menu.
The problem is intermittent, sometimes both user have good performance, sometimes only one or neither. Sometimes compact the front-end fix the problem, sometimes compact the back-end fix the problem, and sometimes overwriting the front-end with the original backup copy.
The same query:
(select photo from item where id = <id>) id is the primary key, 110,000rows
run from debug mode in vba
when 2+ user: 4-5sec
when only one: instantaneous.
so when many queries are executed after a single user action, it may take 30sec to execute.
When I monitor the network traffic using the task manager, I can see why it's slow, the simple query transfer about 30Mb, even the field I am querying is a text of about 50chars. Using a sniffer (wireshark) I can see a major part of the "item" table being transfered over the network.
But in single-user, it's fast and only ~20k is transfered on network.
why the behavior in single-user is different in multi-user?
after trying most of thing I found on groups and forums
I'm thinking to switch to a sql server back-end
Did someone else got this problem?