Re: Network trace of Oracle stored procedure result

Al Brown wrote:
> I did a network trace yesterday for the results coming back from an Oracle
> 9.2 server running a stored procedure in a package. This was called from
> ADO from a windows 2003 server.
>
> the expected resultant record set is a 15 byte customer id. The network
> trace shows that about 100 packets with a total size of 90K was sent to
> satisfy this one request. The preamble before you recieve any data was a
> list padded out with spaces of al lthe stored procedures and their names
> that were stored in tha package.
>
> can anybody explain why this is and what can be done to stop it. The reason
> for doing this was because of a slow down in execution times when executing
> this over a 34MB Wan segment.

Al, it sounds like a client driver problem. ADO/ODBC is, excuse for being very blunt, utter and total crap.

I've done (many years ago) the same type of network sniffs. Like you we were running across a WAN with VB clients talking to a SQL-Server database. The VB clients were using ODBC/Jet Engine to connect to SQL-Server. I wrote two sample programs - one in VB and the other in Delphi. Using Delphi I could "tune" the ODBC layer connection (in essence setting the max packet size). Both sample programs did the exact same sequence of commands. Open a connection, run a select count, close connection.

The VB sample app send 19 frames and the Delphi sample app send 4 frames. The reason was the VB layer into ODBC (the Jet Engine that should have been rather called a Jet-less engine).

When we moved from SQL-Server to Oracle som years later we again did similar traces. It was horrible.. The ODBC/Jet Engine layer did all kind of hidden SQLs to "discover" the database type, enumarate tables and indexes and stored procs and all kinds of other crap. A simple connection command coded by a developer resulted in a whole bunch of these hidden SQLs.

Granted, I have not touched ODBC/ADO for some years now.. but it always had a reputation (deservedly so) of being clunky, slow and interfering. One way to work around it was to use ODBC pass-thru SQL - but even that did not match native db client driver performance.

And you're description of what is returned clearly shows that ADO is still doing hidden SQLs.

Drop that crap. Like we did almost 7 years ago.

FWIW, Oracle now provides a very small footprint client. Using that via an Oracle created ODBC driver will likely provide your WAN with immediate relief.

--
Billy