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

Ed Bradt <ebradt_at_lilly.com> wrote:

>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

I know it's a shameless plug, but:

If you want to get rid of BDE overhead, you might try the Direct Oracle Access components. It offers the fastest possible low-level access to Oracle without any overhead, and (if you're using Delphi 3) supports data-aware components. It works directly on top of SQL*Net and uses no other DLL's. You can distribute your application as a single executable.

Try it out, an evaluation version is available at:

http://www.allroundautomations.club.tip.nl/doa.html

Marco Kalter
Allround Automations Received on Sat Sep 06 1997 - 00:00:00 CDT