Re: *** SLOW *** BDE Connection to an Oracle Server

Steinberg wrote:
>
> Hi
>
> I'm working with Delphi 3's SQL Links against an Oracle 7.x Database
> using TTable and TQuery, BDE 4.0 and ORA72.DLL. The performance of this
> connection is very poor, although everything else is promising: a strong
> server, fast LAN,
> highly optimized database, etc..
>
> Issuing SELECT, INSERT, UPDATE (etc...) from a delphi program is too
> slow. In fact, running 1000s of queries takes considerably too much
> time, in relation to the server performance.
>
> I'm sure there are ways to optimize this connection, or a more efficient
>
> way to use it. I need your advice with that. Great BDE overhead is the
> MAIN suspect. There are probably several ways to deal with that overhead
> (parameters, etc..)
>
> Herbert

Yes GREAT BDE overhead IS the main culprit. I've run the Oracle trace facility on sessions for Pardax to Oracle connection via sql links and BDE. The first time a query is executed in a session BDE goes to absurd lengths querying most of the Oracle system dictionary tables to find out everything it can about the tables, columns, indexes, grants, constraints for the user and all tables in the query. Only after a LONG list of these queries taking several minutes does bde actually submit the query you issued. You'll notice the second time you execute the same query (in that same session) it runs MUCH faster as at least BDE is smart enough to recogniz that it doesn't need the information again.

Unfortunately we were not able to find a way to limnit BDE to interrogating the Oracle data dictionary to a more reasonable extent. It would be nice if there was a way to tell bde to just issue the query 'as is' and let Oracle figure out if it's valid or not. If you find the switch for this let me know.

Ed Bradt Received on Fri Sep 05 1997 - 00:00:00 CDT