dynamic SQL network performance

From: Gerard Weatherby <gerard.weatherby_at_snet.net>
Date: 1997/02/14
Message-ID: <33051269.6FAC_at_snet.net>#1/1

I'm working on a C++ client/server MS windows application using Oracle running on a UNIX (Sun Solaris) server as a central database. Some of the
client sites will be distant (halfway around the world), and we're expecting
significant networking delays (estimated as a quarter-second per round trip
message.)

We've been told that executing dynamic SQL causes a multiple step handshaking
process to go back and forth between the server and the oracle windows dll,
sitting atop SQL*NET. That is, a statement like:

	SELECT NAME 
	FROM SECRET_AGENTS
	WHERE ID=007

does not get transmitted as a single set of packets and receive a single set
in reply, but takes multiple steps.

We've also been told doing the equivalent operation via a stored procedure
takes a significantly less number of back and forths. I'm (yet) not sure of
Oracle procedure syntax--but notionally:

        EXEC PROCEDURE LOOKUP_NAME (007) Does anyone know how accurate this assessment is? If so, any idea why it takes multiple messages to go the job done?

Also, any insight on attempting to use Oracle over such a distant connection to
a server would be appreciated. Received on Fri Feb 14 1997 - 00:00:00 CET