* SLOW * BDE Connection to an Oracle Server

Steinberg

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

* SLOW * BDE Connection to an Oracle Server

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

Ed Brad

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:* SLOW * BDE Connection to an Oracle Server

Quote

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

Marco Kalt

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:* SLOW * BDE Connection to an Oracle Server

Quote

Ed Bradt <ebr...@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

Marc Scheun

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:* SLOW * BDE Connection to an Oracle Server

Quote

Steinberg <ami...@ibm.net> wrote:
>I'm sure there are ways to optimize this connection, or a more efficient

You could look at alternative ways to connect to Oracle, although some
of them might lock you into using just Oracle and would make porting
to other RDBMS more difficult:

1) ODBCExpress (http://www.odbcexpress.com) is a set of ODBC-based
components for Delphi that bypass the BDE entirely and get very good
reviews from the folks who use it (I don't - yet, maybe).

2) Direct Oracle Access : you can download an evaluation copy of this
set of Delphi components calling the Oracle API directly from the
Delphi Super Page (http://sunsite.icm.edu.pl/delphi).

HTH
Marc

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:* SLOW * BDE Connection to an Oracle Server

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Quote

> >> 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..)

We had great success using the BDE and Oracle. One of the biggest boosts
in performance was to turn the schema caching ON.

I can try to pull stuff out of my memory if this doesn't help much.

I just know that we had very sweet system running. We developed on a
monster
DEC Alpha system, but then stripped the database down to 1GB and threw
the
whole system onto a P-166 laptop running Personal Oracle for sales to
demo.

Overall I was rather impressed with the whole setup.

Now, before you bash me up one side and down the other, YES I KNOW that
using
other more "sophisticated" techniques that talk more directly to Oracle
will
probably get even greater speed, I want to just make the point that you
CAN
tune the BDE to get good performance. It took a bit for the brains in
our
group to figure it out, but in the end it was pretty simple and we got
good
results.

Oh, and make DARNED SURE that the Oracle queries are tuned right. :-)
(Yeah, I know, I know, *duh*, but there was a lot of wasted time over
that
issue. People were blaming the BDE when it was their stinking queries.)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBNB+BOzEYshOhjHv6EQLq/ACg2zvQufQNlFKll3VZw78LwXwjb/QAnAn1
ftlBuNCWumuvbc0ziNCagHrc
=nmmt
-----END PGP SIGNATURE-----

Steve{*word*106}inso

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:* SLOW * BDE Connection to an Oracle Server

Since you seem to be savvy to BDE, perhaps you can help with the
following problem:
I'm a Paradox developer from the 1.x days. My employer
recently had me convert many Paradox/Win apps over to an Oracle 7.1
backend, using the BDE SQL-link drivers from Borland, and I was quite
successful in retaining the Paradox frontends through this conversion.

With one app, tho', actually, one table in the app, I've been having a
problem that seems to be growing over time. After a few months of
successful use, first one then another user would report that they were
unable to edit certain records because "another user has changed that
record", when, in fact, no other user was logged in. In time, those few

users were unable to edit ANY record. For them, I worked out a
local-table workaround, flagging the edited records, then "Adding" them
back to the Oracle table when they were finished. This worked, for a
few
weeks. Suddenly, the "adds" were not taking -- no error messages, but
the
data wasn't changing.

Concurrently, more and more users were getting these same errors, until,

this past month, NO user could edit a record directly. I've had to
resort
to a brute force method of updating the database -- emptying the SQL
table, then adding the entire local table back in. This, of course,
opens
a can of worms regarding concurrency.

I've deleted and recreated the table a number of times. At first, this
helps -- everyone can edit, the "adds" stick, etc. But, with time, this

ability degrades, and I'm back to workarounds.

Let me reiterate, it is only ONE TABLE that exhibits this behavior.
There
are MANY MORE tables on the server and in the tablespace that do NOT
acts
this way.

Any clues? I'd be most grateful.

Thanks

--
------------------
Steve{*word*106}inson
sdick...@rcgit.com
Webmaster: www.rcgit.com
{*word*106}i...@pipeline.com

Quote

K&A wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1

> > >> 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..)

> We had great success using the BDE and Oracle. One of the biggest
> boosts
> in performance was to turn the schema caching ON.

> I can try to pull stuff out of my memory if this doesn't help much.

> I just know that we had very sweet system running. We developed on a
> monster
> DEC Alpha system, but then stripped the database down to 1GB and threw

> the
> whole system onto a P-166 laptop running Personal Oracle for sales to
> demo.

> Overall I was rather impressed with the whole setup.

> Now, before you bash me up one side and down the other, YES I KNOW
> that
> using
> other more "sophisticated" techniques that talk more directly to
> Oracle
> will
> probably get even greater speed, I want to just make the point that
> you
> CAN
> tune the BDE to get good performance. It took a bit for the brains in
> our
> group to figure it out, but in the end it was pretty simple and we got

> good
> results.

> Oh, and make DARNED SURE that the Oracle queries are tuned right. :-)
> (Yeah, I know, I know, *duh*, but there was a lot of wasted time over
> that
> issue. People were blaming the BDE when it was their stinking
> queries.)

> -----BEGIN PGP SIGNATURE-----
> Version: PGP for Personal Privacy 5.0
> Charset: noconv

> iQA/AwUBNB+BOzEYshOhjHv6EQLq/ACg2zvQufQNlFKll3VZw78LwXwjb/QAnAn1
> ftlBuNCWumuvbc0ziNCagHrc
> =nmmt
> -----END PGP SIGNATURE-----