Oracle 7, ODBC, SQL*Net, VB4 and WANs

From: David Witt <dwitt_at_ti.com>
Date: 1997/03/24
Message-ID: <5h6e6p$ab9_at_sf18.dseg.ti.com>#1/1

Greetings.

We are in the process of deploying a series of world-wide applications which will retrieve data from either a regional or central Oracle server. We have two predominate groups of programs - one group is Web-based, and the other is written using Visual Basic.

We are having problems as our VB applications move to a wide area network, whether it's domestic frame relay or international circuits. The problem is not performance on either side of the application, but rather the sheer number of seemingly useless packets which are transmitted between the client and server. Since each packet is dependent upon receiving the previous packet, we are hitting enourmous latency problems.

Server: Sun and HP systems running various versions of Oracle 7. Client: Windows 95 or Windows NT (4/3.51)

         SQL*Net 2.x (tried 2.1, 2.2, and 2.3 with same results)
         Oracle ODBC (unsure of version) as well as some Visigenic
         VB4 runtime libraries

As an example, a single select statement generates the following network traffic (note: All data sizes are TCP data sizes, not overall packet sizes):

Source Size Function

Client   18     unknown
Server   14     unknown
Client   18     unknown
Server   11     unknown
Client   varies SQL statement (in this case a SELECT)
Server   38     unknown
Client   42     unknown

Server varies Column headers
Client varies unknown
Server varies Data from request

Client   15     unknown
Server   11     unknown
Client   15     unknown
Server   11     unknown

I can probably deal with the middle six packets (although I'd like to drop the column headers), but I do not understand why the first and last four packets are necessary. It seems that the DB engine is starting up a seperate connection within the socket and closing it after the data has been retrieved.

Here are my questions:
1) What layer is adding the additional packets? We have changed versions of SQL*Net and ODBC to no avail.

2) Is there a particular vendor's ODBC driver that will work better?

3) Is this problem specific to VB, or will we see it also with VC++?

4) What is the function of the first and last four packets?

5) Has ANYONE tried to deploy a world-wide application in which the database server is located in a different area of the world (therefore going over high-latency WAN links)?

6) Are there any good ODBC drivers which will allow the developer to call stored procedures on the server? Any limitations to those?

7) etc

Thanks in advance. Please respond directly to dwitt_at_ti.com, and I will post the results as appropriate.

David
Network Assessment and Optimization Received on Mon Mar 24 1997 - 00:00:00 CET