Re: Database Connection Speed Issue. I'm stumped!

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance


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/




"tc" <tull@xxxxxxxxxxxxxxxx> wrote in message
news:%23dyFTPWjHHA.2120@xxxxxxxxxxxxxxxxxxxxxxx
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.




.

Relevant Pages

  • Re: VB slow Access database
    ... When the executable is on the server, ... > a Visual Basic 6 .EXE and an Access database. ... > client PC by loading the .EXE from the server. ...
    (microsoft.public.vb.database)
  • Using an Access Database in ADO.NET w/o MS Access
    ... If I am using ADO.NET in a VB.NET screen to do inquires, updates, deletes ... etc on an MS Access database, does the client or the server that the ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Help needed to connect to remote Access database using C++ (not MFC)
    ... Access database on a server, ... To make the local access database connection I used function ... you always end up doing all the processing on the client and pulling in much more data over the network than you would normally need. ...
    (microsoft.public.data.odbc)
  • minimizing data retrieval for subforms
    ... I'm working with an application composed of a "client" ... Access database and a "server" database containing all ...
    (microsoft.public.access.forms)
  • Re: What doesnt lend itself to OO?
    ... >> proxy and instructs the server to constuct the real object. ... rather than client code. ... If 'clock' is instantiated in the server, ... > for the server interface at the OOA level. ...
    (comp.object)