Subject Re: optimizing query
Author Dixon Epperson
I'll be happy to tell you what I know, which isn't much since I too
am relatively new to SQL.

A desktop database uses a folder/file approach for storage.
Typically your database is the path to the folder where the files are
stored, and files are actually your tables and their indexes.

When you use for example a TTable component and link it to a table,
it reads the whole table file into memory and then populates the
table component with the complete record as it was read in.

This includes every record and every field in every record. If your
database application is on the same machine these files are, then the
program is very quick, very responsive and very vulnerable to data

You can mitigate that to some degree if your using a product like
DBISAM and using only their query component.

However a sql database like firebird contains the records and indexes
of every table in its database within a huge file (or at least it can
become huge) as opposed to a windows file. When you query the
database, you do not open this file up. Instead you send your
request for information to the server (either running as an exe or as
a service) and that service gets the specific information you have
requested and returns them to you.

so what you get with a query is only those rows that meet the
criteria of your query and only those columns (fields) within each
row that you have requested.

here is an example
TTable with 1000 records and each record has 50 fields. You open the
tTable component, you get it all.

However, the same data in a Firebird database. You request (create a
query) 5 fields from the database that meet a certain value, if only
10 meet the criteria, you will get 10 records with 5 columns each.

TTable is quicker for a local database because everything is in
memory. However, you will never fight data corruption in Firebird
like you do in a desktop database.

Dixon Epperson

> Would you mind elaborating on the following paragraph below:
> > If you come from a desktop database background, one big change is
> > that client/server databases encourage selecting only a few
> > records rather than a large number of records. It may not sound
> > like a big difference, but until you start doing this Firebird
> > will feel like a big, slow mastodont of a database, whereas the
> > real problem is more the way you use it (like pigeons are suitable
> > for carrying small messages, not the 700 pages Firebird book that
> > Helen is writing).
> I'm new to Firebird and trying to get my head around it.