>> Our application database contains over 70 tables, accessed through
>> a TIBODatabase object which feeds and equal amount of TIBOTables
>> grouped in a TDataModule. Usually each form of the application
>> creates and uses its own private instance of the above mentioned
>> TDataModule, opening only the tables it requires, and since each
>> form is re-created each time it is needed (and re-destroyed each
>> time it is closed), the database tables are continuously opened
>> and closed, possibly from multiple TDataModules at the same time
>> (it's an MDI application, so multiple forms, and multiple
>> TDataModule, can be active simultaneously).
>> Can this particular data access architecture be the cause of the
>> performance drop we recorded?

> Yes, exactly. The application architecture is probably OK but in
> Firebird you are not "opening tables" but running heavy queries
> that eventually will return all of the rows to the client.
> Client/server applications cannot perform with this data access
> architecture.

> Firebird isn't a desktop database nor a file-served database and
> applications don't read from or write to files.

> Table components do not belong in this environment. Everything must
> be done with queries, designed to return the minimum of amount of
> data. There's a whole new world of logic and language to
> understand here.

> You'll need to treat the completion of the migration from BDE to
> IBO and from Paradox to Firebird as an early first step. You'll
> certainly need to fix up the database structure: Firebird doesn't
> need nor thrive with Paradox-style hierarchical keys; and tables in
> Firebird have logical structure but no physical structure. Indexes
> in Firebird are not used to reorder tables, but to speed up
> searches.
> The term "search" means something quite different in SQL than it
> does in an ISAM database like Paradox, where "records" are found by
> physically locating them in a file. In SQL, you work with sets -
> selected groups of columns (you call them fields) in one or more
> rows.

First of all, thanks for your thorough answer!

Well, I know the difference between file-based desktop databases like
Paradox and RDBMSs like Firebird, and I'm aware that our current data
(access) architecture is far from ideal to use with the second: it is
unfortunately heavily table-centric, with a far from normalized and
streamlined structure, Paradox-style indexes, and so on.
My "What are we missing/doing wrong?" had a different meaning: I read
a few IBObjects reviews on the web which stated that, no matter how
the original code/data structure was, the 1-to-1 conversion from BDE
to IBO would always have assured similiar or better performance
(thanks to IBO high degree of optimization), which is not our case, so
my questions arised.
Now I suppose those articles were implicitly referring to
BDE/Interbase to IBO/Firebird conversions, where the original
code/data structure, no matter how it was, was already targeted at a
RDBMS, and I admit it was quite ingenuous from my part to think it
would have worked unconditionally, so my problem now is: given the
legacy data (access) architecture I said, which unfortunately I cannot
change that much (nor rewrite from scratch) since it's deeply linked
to tons of bad written legacy code which there's no time/resources to
revise as would be needed, how can I "tweak" the TIBO components to
obtain the maximum performance in the above outlined scenario? Which
elements of it have the heavier impact on performance? With limited
resources and time, which ones should I try to fix/optimize first?

I know this is not the ideal solution, but unfortunately it seems to
be the only feasible one at the moment :-( , and I've not the needed
insight on IBObjects to infer what they tolerate worst and what, on
the contrary, they can "auto-patch" to some extent thanks to their own
optimizations.


Thanks,
Michele.