D&C GLug - Home Page

[ Date Index ] [ Thread Index ] [ <= Previous by date / thread ] [ Next by date / thread => ]

Re: [LUG] Network Switches

 

Richard Brown wrote:
> 
> It also has 10 xp boxes and 1 vista box on the network. Sage is
> connected to by anything up to 10 users and whilst Sage itself is low
> in data size the databases that users use through excel and access are
> huge. (Around 2gb in size). If a user tries a search on something like
> this the network slows to a halt and users lose connection with Sage.

Okay Excel and Access use file based approaches (usually - they can use
other data sources).

So the client machine (rather than the server) is reading and writing
the file (logically if not physically).

So if someone searches a 2GB Access database, it may well be that 2GB of
data is being transferred from server to client.

2GB x 8 (bits per byte) is 16Gb, at 100Mbps (you'll get a lot less from
Microsoft networking protocols - maybe 30Mbps), I'd expect it to take
nearly 3 minutes using all the bandwidth to transfer all the data, more
likely 10 minutes.

Presumably Access does some caching, but if you have Access databases
using the normal backend for Access then it probably well beyond the
time to migrate to a proper database architecture. Postgres / MS SQL /
Oracle which run a server process that accesses the files and only
serves that data which is needed to clients.

First thing to do is compact and repair the databases. Probably best
done on the server if you have the right software installed.

Backup, Defrag, Compact and Repair.

http://support.microsoft.com/kb/209769

2GB whilst small for real databases is a huge amount of data for Access,
indeed it was the upper limit for Access for a long time (may still be).

I've seen people using Access as a backend for a web site. When I
compacted their database after many months of operation the web pages
went from loading painfully slowly to almost instant. Yet another
Microsoft technology that creates a manual maintenance step, and doesn't
complain if the maintenance is never done, just goes slower and slower......



-- 
The Mailing List for the Devon & Cornwall LUG
http://mailman.dclug.org.uk/listinfo/list
FAQ: http://www.dcglug.org.uk/linux_adm/list-faq.html