Network Roundtrips when using Bind-Variables? How to avoid this?

[Anonymous]

I'm using the OCILIB-Library (which is build ontop of the OCI Library by
Oracle) to talk to an Oracle DB in the Network. I have a fairly big
SQL-Statement with Bind Variables like this:

SELECT Field_1, Field2, ....
FROM Table
WHERE Field1 = :Field1 and
      Field2 = :Field2 ...
Order by Field1, Field2, ...

I have bound 10 fields to their field-variables. These field-variables are
mentioned 50 times overall in the where clause. I execute this code:

OCI_Prepare ( stmt, sql ) ;
OCI_BindUnsignedInt ( stmt, ":Field1", field1 ) ;
...
OCI_Execute ( stmt ) ;
rs = OCI_GetResultset ( stmt ) ;

But when I log the timings, I get this delay

03:30:27.22 OCI_Execute
03:30:30.34 OCI_GetResultset

Whis is unacceptable in the environment I intent to deploy this.

If I dont use Bind-Variables like this (I have to build the sql-string,
which is ugly).

SELECT Field_1, Field2, ....
FROM Table
WHERE Field1 = 1 and
      Field2 = 'blub' ...
Order by Field1, Field2, ...

And execute it like this

OCI_ExecuteStmt ( stmt, sql ) ;
rs = OCI_GetResultset ( stmt ) ;

Then I get this delay

03:30:27.14 OCI_ExecuteStmt
03:30:27.22 OCI_GetResultset

Whis is much better.

What is happening here? Why is the delay with Bind-Variables so great and
is there a way to still use them without this delay (they are REALLY
convenient and produce much cleaner code) and without having to manually
build the sql string?

I'm using an Oracle 10 Client to connect to an Oracle 11 and have the
Network Oracle Db declared in my tnsname.ora like this

ORASERVER=
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = net.address.com)(PORT = 1521))
   )
  (CONNECT_DATA =
    (SERVICE_NAME = DBSERVICE)
  )
)

If this information helps. Thx in advance for answers.

Oh and changing the Bind direction from IN_OUT to IN doesn't speed anything
up.

[Vincent Rogier]

Hi,

What is your Oracle version ?

Have you tried to use OCI_SetFetchSize() and OCI_SetPrefecthSize() ?

Regards,

Vincent