Database Connection Speed Issue. I'm stumped!

[tc]

Hi. I have a customer who is running our .net application, multiple clients
connecting to an Access database. This ONE customer reports a great speed
degredation when more than one client is connected.

First client logs on and the system runs correctly.
Second client logs on, both connected clients now run very slowly when
retrieving data.
Third client logs on, the speed on all three clients is no degraded even
more.
Etc.

I cannot replicate this, I have 3 PC's in development conencted to the same
database, same application, and everything is fine.

Although I don't know the exact scenario the network sitution as as follows:

Server at Site A, Client 1 at Site A, Client 2 at Site B, Client 3 at Site C

Site B and C are connected by WAN to site A, while Client 1 is connected to
the server on LAN.

I can understand Site B and C running slower than you would expect by LAN,
but I don't understand why the Client at Site A becomes slower just becuase
Client B logs on.

My previous post describes the same kind of thing but in a different
configuration. Several clients log on to an Access database on a server,
all LAN, the .net code runs perfectly. As soon as an old legacy VB6 app
connects to the database all the .net clients grind to a snails pace.
Disconnect the VB6 app and the .net code starts running at full pace again.
This I can replicate in the development environment.

Anyone have any ideas at all?
Cheers.

 

[sloan]

Two words:
Access database


An access db is simply a file sitting on a server. There is no RDBMS
(relational database management system) which regulates it.

Let's take an instance where you have a db.

Emp table
Dept table
EmpWorkWeek

Where Emp table has your employees of course. Let's say 10,000 emps.
Dept table has your company dept's. Lets say 200.
EmpWorkWeek is where an Emp enters his/her hours for a week. 1 row for
simplicity per emp per week. So each week gets 10,000 rows.

You want to get the following info

Select e.SSN, e.LastName , e.FirstName , d.DeptName , eww.Hours
from Emp e join Dept d on e.DeptID = d.DeptID join EmpWorkWeek eww on
e.EmpID = eww.EmpID
WHERE
e.SSN = '000-00-0000' and eww.StartDay = '1/1/1999'

A basic query. It should return 1 row.
You want to know how many hours '000-00-0000' worked on the week starting
1/1/2006.

1 row.

Because Access is NOT a true RDBMS, and its just a file, whatever computer
is using it has to do the filtering.
Back in the day , this was the Jet Engine.

So to get this row, you have to bring back

10000 X 200 X 52000
(that's if you only have 1 years of data ) (throw in a times Y, where Y is
the number of years data you have)


It ~~has to bring back all those rows, because there is NO RDBMS to do any
work for you.


So most times, its network traffic.

MS said one time that "Access can theoretically handle 255 users" (circa
1997)

Yeah right. They later said "Most likely 10-12 users at the same time".

Its not a RDBMS. Its a file. Like Excel. Like word.

Go to MSDE or Sql Express is my advice. You'll never win the Access battle.


12/16/2005
How to Install MSDE (Sql Server 2000 Desktop)
http://sholliday.spaces.live.com/blog/