bandwidth (1 Viewer)

[diversoln]

I was told by our IT people that multi-user Access implementations are not allowed on our network because they require too much "bandwidth". What is bandwidth as it applies to network applications and is it common for Access to be excluded due to this ?

Thanks for your input.

 

[WayneRyan]

diversoln,

You have a person with a workstation connected to a server.
The user has a "small" software package that displays data
from a database on the server.

Two things can happen.

One, the software can specify that ALL of the data be available
for presentation at once. This will cause the server to send
tremendous amounts of data over the network, pretty much making
life miserable for all.

Two, the software can retrieve only the records that the user
is interested in. The server only has to send a small fraction
of the database over the network. Everyone seems to be smiling.

Access is just a software tool. Whichever of the above methods
the designers chose is one thing. You can't blame Access in
this situation anymore than you can blame Windows.

Wayne

 

[diversoln]

Thank you for your input Wayne,

If I understand you correctly, its not the software (Access) that is to blame for slowing down the network, but the methods employed by the Access programmer to use and display the stored data.

With this in mind, (on a high level) what do's and don'ts apply to publishing a multi-user db that isn't a network "hog" ?

Does splitting into front-end & back-end play a role ? If so, what else?



Thanks for your insights.

 

[cable]

I would prod the IT department for more reasoning behind this.

I seriously doubt an Access application would be stressing a normal local area network. It would be annoying the hell out of the users long before that!

If these access apps are transfering enough data to be causing network problems, then it sounds like they should be looked at again to see if their behaviour can be improved, and consider migrating them to be SQL server based.

Another, not recommend solution, is that the IT depart use a program to limit the bandwidth used by the Access app (and any others). This has to be more acceptable to all than a blanket ban.

 

[The_Doc_Man]

diversoln, here is the straight skivvy.

Access is a small-shop database. Powerful as all Hell, pretty good for what it does, but small-shop mentality in its design. And WORKSTATION-oriented in its operational paradigm. Shared databases are an add-on and extension of something that originally was intended to be standalone.

When you have a shared database, the host for that database is a file server. It is NOT an application server. The Access application runs on the client machines. The problem is that when the client runs the app, the client is the machine that inspects each record for whether it is qualified to be a member of a particular query's answer (recordset). The down side of this is that the client machine must see EVERY RECORD in the originating tables to make this decision. So a count query or a select query or a sum query all have the same issue. They have to see a LOT of records.

I've read WayneRyan's answer and it seems misleading. Perhaps I'm reading it wrong. But I can tell you this - the designer's choices in an All-Access shop don't affect whether the client has to see all the underlying records. Access, when it runs its own queries, MUST ALWAYS see ALL records in ALL tables that contribute to the query.

If your app has a lot of records (by lot I'm talking many tens of thousands minimum) then your network person is spot-on in complaining. If your app has fewer records, you will pull less data across the net. So the first question is, when you run a query against your tables, how many records have to be EXAMINED to form the answer set?

The bit that Cable gave you about changing to SQL server with an Access front end is because when you do that, the SQL server examines the records locally and ONLY sends the answer set, not the tables from which it was derived. That is because SQL Server runs on the SERVER, not the client. If you had ORACLE or some other PC-based package that accepted ODBC already, and if you had the appropriate licenses for it, you could use something besides SQL Server. Actually, you would need licenses for THAT, too.

Another question that comes to mind is, how fast is your corporate backbone? Are we talking 10 Mbit Ethernet? 100 Mbit? 600 Mbit? That would also make a difference. How much competing traffic runs over your net? If you are running Ethernet with about 30% load, your collision rate (messages trying to step on each other) will start to rise. If you are above 40%, I know why your network person is having his/her apoplexy over yet another network-intensive app.

You can perhaps reduce the load a little by splitting your DB into a front end and back end, then distribute the front end. But sadly, that will only go a little ways towards load reduction.

Another possibility depends on how much data changes and whether you can distribute the database to multiple machines, then run a reconciliation. But if your system wasn't built for that up front, it would be hard as Hell to retrofit.

 

[diversoln]

Thanks Doc,

That goes a long way in explaining the bandwidth issue. My little db has been running in our lab for a year and a half and only has 800 records. We're talking about increasing usage to about 10 times that by having other departments use the system too. So by this time next year, we're looking at maybe 8000 records total which could be subdivided among as many as 4-6 tables depending upon the department - and there's no reason why old data couldn't be archived on a semi-regular basis.

But, with that said, the IT department has their rule so I think I may be stuck....

 

[The_Doc_Man]

But, with that said, the IT department has their rule so I think I may be stuck....

Oh, ye of little faith. I've been around too long to let a little thing like THAT stop me. To whom is this database important? How important is it to the company? Who is the highest ranking company officer who wants this data?

You get THAT person to "gently" explain to your network persons that their attitude is standing in the way of progress.

Now, if YOU are the highest ranking person, you are probably right. Stuck and no way off the hook. But in most cases, you build these databases because someone ASKED for something. So if you have a problem, you report back to that person that you cannot do what they asked because of that mean old network administrator with no sense of adventure.

Trust me. I've been there and done that so often, I think I own the rights to the T-shirt.