Subject Re: [IBO] BDE-to-IBO conversion: performance issues
Author Helen Borrie
At 01:03 PM 13/04/2005 +0000, you wrote:

>Hi all,
>this is my first post here, so I hope I'm not asking an overworked or
>excessively generic question (I've just searched the group message
>base for it, but I didn't find relevant answers). Oh, by the way,
>excuse my English too: it's not my native language, I'll try to do my
>best. :-)
>Back to the mail topic, I work as a Delphi programmer in a small
>software house, and we are converting our main application database
>from BDE/Paradox to Firebird: we chose IB Objects as our new database
>access components, hoping to obtain, among other benefits, an overall
>performance boost in database operations (which are quite frequent,
>since our application is an hotel management program).
>For compatibility reasons we replaced the previous standard Delphi
>TDataset data access components with the corresponding TIBO
>equivalents, but doing so (basically a simple 1-to-1 substitution with
>minor adjustments) we immediately noticed a remarkable performance
>drop in all database intensive operations!
>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.

>It works quite well with Delphi standard
>data access components and BDE/Paradox, but perhaps it's not suited to
>TIBO components and Firebird...
>Or are we missing something?

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

>Are there any legacy BDE operation/feature/component properties
>configuration/whatsoever which is particularly inadvisable
>performance-wise for TIBO components and which we should avoid at all

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.

>The same operation is sometimes *several times* slower with
>TIBO/Firebird than it was with BDE/Paradox, using the same code and
>the same (converted) database: according to several sources I found on
>the web, IBObjects are tipically much more efficient than this, so
>what are we doing wrong? :-(

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.

>Any help to point us in the right direction will be _much_
>appreciated, and if you need more specific informations (code samples,
>tests, whatsoever) to get a better insight on the problem, just ask,
>I'm at your complete disposal!

Begin at

The first paper you'll need to read is "Moving to Client/Server" but, in
time, you'll need all of the others. The Datasets titles will be of strong
interest for your task initially.