At 10:07 PM 9/12/2005 +0000, you wrote:

>Helen, I have a question that you may just not have the time or
>inclination to answer. And it's going to sound provocative but I
>certainly don't mean it that way. Every database application I've
>ever written was via the VCL and I know very little about database
>APIs and mechanisms beyond that. But you make the statement, "This
>model is therefore hopeless for a client/server DMBS If the table is
>bigger than about 200 rows." The application that I'm porting was
>originally written in Delphi using the BDE against a Paradox
>database. I decided some years ago that I wanted to move to IB so I
>stopped deriving directly from TTable and derived instead from a
>class I call TPortableTable which handles the little differences
>between Paradox and IB. TPortableTable derives from TTable but is
>sensitive to whether the back end is Paradox or IB. What I'm
>working on now is making TPortableTable derive from TIBOTable. My
>first cut of moving to IB was to migrate my data from Paradox to IB
>and then continue to use BDE through the Gemini ODBC driver. That
>implementation has been running in production for something like
>five years now and the tables in my database have way, way more than
>200 rows. The table against which I was doing the operation above
>has, as of this moment, 2,356,043 rows, and all the others in play
>have on the order of a million or so rows.
>
>Now, why is it that the rather hokey mechanism of TTable->BDE->ODBC-
> >IB can do these operations correctly and efficiently, but IBX and
>IBO seem to stumble? My BDE app doesn't chew up tons of memory. It
>doesn't chew up tons of cpu cycles or disk reads. And I had to do
>very little porting when I converted from Paradox to IB. Since then
>I've been slowly moving myself away from dependence upon BDE. I
>haven't used Paradox for this system for years, so we can't really
>blame any performance or resource issues I'm experiencing on Paradox-
>friendly things that I may be doing. One would think that TTable-
> >BDE->ODBC->IB would be full of performance problems and hiccups,
>but I've experienced far more hiccups trying to go to TIBOTable->IB
>and I completely gave up on TIBTable->IB. How is it that the BDE
>which was written years and years ago seems to be able to handle
>these things better than IBO? I mean, when I do the
>Insert/Post/Edit/Post against TTable->BDE->ODBC->IB against a table
>with hundreds of thousands of rows, it just works and it doesn't use
>a lot of cpu or ram or disk to do it. But when I run this same
>sequence against TIBOTable->IB, suddenly now I'm blowing up, I have
>to change this or that setting, and I won't be correctly positioned
>in the cursor after the Insert & Post. How does the BDE pull off
>this magic? The same is true of things like FindKey. I do lots of
>FindKeys in TTable->BDE->ODBC->IB and they all just work. But in
>TIBOTable->IB, he does huge amounts of caching and then linear
>searches through the cache.
>
>I have an app that has worked fine for years with big tables,
>TTable, IB, BDE, and ODBC and no Paradox. I think it was perfectly
>reasonable of me to assume that removing BDE and ODBC from the stack
>would only result in increased speed and simplicity, but this hasn't
>panned out very well. What have I missed? Why is it so hard for
>TIBOTable->IB to perform at least as well as TTable->BDE->ODBC->IB?

Let's just simplify the answer here. The difference is that the BDE
creates a Paradox table on the client's hard disk where it "parks" the
entire output of an intial SELECT * FROM TABLENAME set that it runs at
connect time. It's simply a dead cache: this table is a Paradox table
that has no transactional relationship with the underlying table in the
database. Borland solved this by restricting you to a single transaction
and inventing the VCL-style Autocommit mechanism that depends on the
database to be able to support CommitRetaining and keep the Paradox cache
and the application buffers in synch. It's what they invented InterBase's
CommitRetaining for.

In short, this means that all of your read operations on your multi-million
row TTable are done locally, using the Paradox query engine (which is what
the BDE is - it's not an SQL engine but a very efficient QBE engine for
local data access).

In short, if you want Paradox and desktop performance, use Paradox and
stick to the desktop. If you want IB/Fb and client/server, use IBO, IBX,
FibPlus, etc. and Firebird/IB, to implement multi-user concurrency and
transaction control, because they all (to some extent) make the full API
available to Delphi without the limitations of the desktop engine. There
is no local disk caching: the application buffers are directly
synchronised with database state, via SQL.

I doubt it was ever the objective of any direct-to-API component developer
to create a new, alternative desktop interface for IB...

(Note, IBO does support optional metadata caching.)

Furthermore, there is no BDE or driver version that fully supports dialect
3 databases. BDE 5.2 shipped with Delphi 6, was the last version ever and
the buggy driver has never been fixed, and won't ever be. Borland stopped
support for the BDE years ago and replaced it with DBX, but it has never
open-sourced the BDE code. I think probably it can't, since Corel owns the
rights to the Paradox engine.

Without opening up a big can of worms, there is no reason (technically) why
a direct-to-API interface should cause slower performance than the
BDE; quite the contrary. Where it happens, the causes are easy to
identify but (relatively) hard to fix, especially if the former desktop
developer is convinced that his/her experience with Paradox taught him all
he ever needs to know about relational database design. Paradox's
hierarchical keys and legacy Paradox indexes are serious performance
killers; and if SELECT * FROM TABLENAME is the only SQL you are prepared
to know, IBO, IBX and FibPlus will never be any use to you.

Anyway, that's all I have to say about it right now, since it's not an IBO
support issue and I'm under extreme time pressure at the moment. If you
have a genuine interest in delving, set aside a piece of your life to dig
around in Google. It's all there, if you have the patience to follow the
tracks.

Helen