Re: WAN SQL Net Bandwidth Problems

Mark Winn wrote in message <7ho3tv$8dp$1_at_mawar.singnet.com.sg>...
>
>1/ We seem to be sending a large amount of data, in fact we send more data
>across the network than we recieve which I find astonishing !

What protocol and network API are you using? IMHO stay away from pipes - be that NetBEUI pipes or TCP/IP pipes. I've run sniffs on VB SQL traffic using pipes vs sockets and pipes - pipes had something like a 300% (and more) overhead in the number of frames transmitted.

>2/ We have checked our query costs using Oracle tools and they are all
>optimized or low cost and almost all of them return single records with a
>few columns

What is the average size of a packet? With BDE you can also set the ROWSET_SIZE parameter for the Oracle connection. The BDE on-line help is a bit vague about this parameter, but with the Oracle driver it sets the approx. size of a TCP packet. Experiment with this - the ideal TCP packet size is as close to 1.4KB as possible. The more data there are in a packet, the less packets there are. And less traffic and less collissions. This can provide a noticable gain in network performance.

>3/ We send (in one scenario) around 10k of SQL text which appears to be
>generating around 70k of sent data and 50k of recieved data at the network
>level

Are you using data bound controls? If so, in general this is a Bad Thing (tm). Data bound controls work fine for file based databases (Paradox, dBASE, Access), but sucks at client-server databases. IMHO -never- use data bound controls with a "real database" unless you -KNOW- what that control is doing ito of the SQL it generates. Especially be careful when using a data control's seek/find functions.

IMHO this a problem in general with data bound controls - be that in Delphi or VB. Though in Delphi you can quite easily create new controls by simply subclassing the applicable data/SQL classes.

OK, what is the exact problem? Let's say you use a Table Data Set with bound data controls to edit the table. This means by default a 'SELECT * FROM table' - if that table is large, well then you have a problem. Next you want the user to find a record/row within that table, so you use the FIND method. This often generates very "interesting" SQL which is another problem...

The basic issue is that data controls do not really know your database - what indexes to use and not to use. What flavour of SQL to use. And in the case of Oracle, knows of course nothing at all about optimising SQL statements with hints.

What is the best way to find rows matching a specified criteria from the user? This type of intelligence is hard to built into data controls - not impossible though, just hard and very complex. And most times not worth the effort.

The "secret" is to make sure that every single SQL that leaves the client, is an "approved" one. I.e. one that was coded by a developer/SQL expert and no auto generated by the data bound control.

>a/ Are there any settings, either in Oracle or SQL Net that we
>can use to minimise the amount of network traffic we cause

ROWSET SIZE in BDE

>b/ Does anyone have any idea of what the formula is for working out the
>network traffic for Oracle / SQL Net queries sop we can get a better idea
of
>the speed of line we need

Unfortunately there are none IMHO - simply too many variables for a worthwhile thumb suck. You can however perform detail sniffer traces per transaction and get a very accurate idea of what the network traffic are.

>c/ Does anyone have any alternative solutions or any experience in this
kind
>of situation where bandwidth is the important factor

Bandwidth is always an important factor IMHO. One development shop for which I contracted -always- got in professionals to do network traces of the new applications. The results were an accurate picture of the amount of traffic per transaction and the amount of traffic per "average user". And as these applications run over a nationwide WAN it was invaluable ito of network infrastructure planning.

regards,
Billy Received on Mon May 17 1999 - 08:32:21 CDT