4

I have a .NET application (VB.NET) that runs against a MS Access database. Every data request connects to the access database, runs and returns the query and closes the connection back again.

I placed the database on a windows xp 32-bit machine.

I have two clients on which I installed the .NET application. Both clients are running windows 7 professional 32-bit.

Now I have a performance problem with this.

When I use the first client it runs fine. All data is shown very fast. When I than use the second client, it takes some 10 seconds to connect to the database, fetch the data and close the database connection. When i ask for other data on that second client, it all runs fine, until I request data from the first client than back again. Than it takes again 10 seconds on the first client before my data is fetched.

Can anybody please help me with that? I owe a Belgian beer to the solver of this issue ;-)

Thanks!

Roel Geusens
  • 147
  • 1
  • 10
  • @SLaks for a 2-client solution (and if the database is not mission-critical), it's probably not worth the effort to switch over to SQL Server. Keeping a persistent connection to the Access database will most likely fix the problem with much less work. – phoog Dec 27 '11 at 00:42

3 Answers3

5

Tom Wickerath wrote a great article on improving multiuser performance for MS Access applications. While his article assumes a MS Access front-end, many of the tips should apply to a .Net application. I recall two points that might help you:

  1. Keep a persistent connection to the back-end
  2. Use (short) UNC paths instead of mapped drives
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
Rachel Hettinger
  • 7,927
  • 2
  • 21
  • 31
  • 2
    Point 1 is really important in my experience. In some cases, setting all Subdatasheet properties to [None] also proved unexpectedly efficient. – iDevlop Dec 26 '11 at 22:57
  • You may be able to confirm that point 1 is causing the slowdown using Process Monitor from sysinternals. I had a similar problem a while back and you could see multiple 1sec delays for a SHARING_VIOLATION each time the app tried to open a connection. – chip Dec 29 '11 at 19:18
2

After a long search, i found it out... My virusscanner NOD32 was causing this, most probably by excessive scanning inbound and outbound network traffic.

Roel Geusens
  • 147
  • 1
  • 10
0

  1. I'm not sure stackoverflow is the right place for questions like this, but ...

  2. It sounds like the first process is locking the file, so the second process has to wait.

"Use SQL Server" isn't a completely flippant response - SQL Server is specifically designed to handle concurrency issues like this.

IMHO ...

PS: This is a pretty lame link, but it might help:

http://office.microsoft.com/en-us/access-help/about-sharing-an-access-database-on-a-network-mdb-HP005240860.aspx

PPS: Here's a somewhat better link, with some suggestions for things you can do to improve concurrency:

http://www.softcoded.com/web_design/upgrading_access.php

paulsm4
  • 114,292
  • 17
  • 138
  • 190
  • Hello guys, I found out what was going wrong. On the server there was the NOD32 virusscanner installed which was causing this strange behaviour. – Roel Geusens Dec 29 '11 at 20:37