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 Dickinson
sdickins_at_rcgit.com
Webmaster:  www.rcgit.com
dickinsn_at_pipeline.com





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




--
-----------
Steve Dickinson
dickinsn_at_pipeline.com
Webmaster: www.rcgit.com