Reducing Network Traffic

[stephen81]

We are shortly going to start using tablet PCs over a GPRS bearer circuit for some of our employees to update our core business system from within customers homes. To do this we have purchased a very streamlined version of our core system, designed to work at a reasonable speed even over a normal GSM connection.

The employees that will be using this also use an access database that I've created though. Whilst it isn't crucial that they can use this while they're out and about, it might be useful and it's something I am interested in looking into. In order to make it feasible though, I think I would probably need to dramatically reduce the amount of data transferred between the be and fe due to the high cost of data over GPRS (as well as the speed). So I have a few questions.

1) Am I right in thinking that if I am using ms access as a backend, all the processing is done at the front end? i.e. I run a query on the front end, the whole table is retrieved from the back end and then filtered at the front end?

2) Is there any way to get around this? Or would it require switching the back end to SQL Server or something?

3) Is there any way of measuring how much data (in bytes, kb etc) is being transferred between the front end and back end while running queries, updating records etc.?

I'll probably think of some more questions but this will do for starters.

 

[jrjr]

1) Yes that is correct

2) You could use a serverside code such as ASP. This is opened with an internet browser but all of the processing is essentially done at the server. This will work with either Access or SQL

3) I am sure there are some apps out there that will measure this but I havent had the need yet!

 

[RuralGuy]

I believe the answer to the first question is almost correct. It is a technicality but I believe you will find that the indexes are transfered and the processing takes in the FrontEnd but *only* the selected fields of the selected records are transferred from the BackEnd.

 

[stephen81]

Thanks for your pointers. I think I'm going to take a look at the ASP route when I get some time.

 

[neileg]

I strongly suggest you look at a thin client option, Terminal Services or Citrix. This will make it more realistic to run other applications over GPRS or GSM.

 

[stephen81]

I strongly suggest you look at a thin client option, Terminal Services or Citrix. This will make it more realistic to run other applications over GPRS or GSM.

We already use Netilla for accessing our main office systems from home, but my understanding of these systems is that they all the processing is done on the server and all that is sent is an image of what would be on the screen? If that is the case, then this would not be suitable for using over GSM or GPRS due to the amount of data involved in sending an image. We can't use Netilla satisfactorily on a dial-up connection, we ideally need broadband connections as dial-up is too slow to handle the amount of data being transferred at an acceptable speed. Therefore I imagine using Netilla over a mobile connection would be unnacceptable over GSM due to the speed, and not really feasible over GPRS due to the cost of sending data. If I use ASP, then all I need to transfer is small HMTL files?

 

[neileg]

I'm not familiar with Netilla, so I can't comment there. We have used Citrix over GSM and you have to design your screens carefully to minimise the traffic, but it does work. Unfortunately, I'm not a techie and don't know all the ins and outs of how it works. You can certainly use Citrix over a dialup connection, though.

 

[jrjr]

If I use ASP, then all I need to transfer is small HMTL files?

The processing is done at the server and just a web page is sent to the requestor as a result so the size of transfered data will be relatively small. Your server will have to be asp enabled. This is a simple matter for windows servers and can be included when the server OS is installed, or added later if needed. If you are using a 'Nix box it is a bit more complicated but still can be done.

PHP is another alternative language and should be considered if you are using a 'NIX box as PHP is a native language whereas ASP is a native language for Windows. They can be used either way though.

 

[The_Doc_Man]

I believe the answer to the first question is almost correct. It is a technicality but I believe you will find that the indexes are transfered and the processing takes in the FrontEnd but *only* the selected fields of the selected records are transferred from the BackEnd.

- Rural Guy[/]

I'm not sure this is entirely true either, no disrespect to Rural Guy, BUT he raises a very good point in any case. If he's right about only sending the indexes at first, it is an INCREDIBLY important point. When designing a DB, don't forget to have indexes on the tables you query most often, even if they don't require indexes for any other reason. Very often folks put indexes on their "LOOKUP" tables but forget to put them elsewhere. A few carefully-chosen indexes can spiffy up performance very quickly.

Rural Guy, the part I doubt is the "only selected fields" part. I don't think it works this way. I might believe the "only selected records" part with some reservations, but the "only selected fields" part cannot occur unless the BE does some processing. But as the BE can reside on a machine that doesn't even have a copy of Access installed (i.e. simple file server), the processing required to implement a field-selection query cannot occur there. No Access executables exist in a context to assemble the required resultant record set.

Now I do believe (or at least it is possible that) you could be calling back only selected FILE BLOCKS (a.k.a. BUFFERS; not DB records) based on the indexes being processed first. Indexes contain the relative file addresses of the records (i.e. starting byte number within file where first byte of file is byte 0), which a decent device driver could turn into a specific block request. As limited as it is, that would be great if it really happens. But field extraction has to occur on the machine holding the FE. Unless, of course, you have a non-Access BE setup.

 

[RuralGuy]

Doc Man,

I would have to agree. Thanks for the well thought out points.