Tables vs. Query Components - Differences between Desktop and C/S databases
The reason there are both Table and Query components is due to the fact there
are table-oriented databases like Dbase, Paradox, or Access, and there are
set-oriented databases like Interbase, Oracle, and MSSQL. These different types
of database systems work and behave differently from one another and the same
methods of access cannot be equally applied.
Table components like TTable are specifically designed to work best with
table-oriented systems - they are native to them. Using Query components
against such databases is slower because they SQL statements must be
interpreted into table-oriented calls for that database.
Query components are specifically designed to work best with set-oriented
databases that understand SQL directly and were designed to work this way.
Using Table components against such a system is slower because the
table-oriented functions must be converted into SQL statements to be sent off
to the database.
Some of the things that Table components do that eat time and resources over a
network with an SQL system are:
On Opening, it sends many queries to the database to get all the metadata for
fields and indexes in the selected table in order to provide you with a
selection of these.
Most Table components will select all
fields even if you only want a few. If you have large records with many fields,
this can be disasterous for performance.
Using Locate or FindKey or RecordCount forces all records to be fetched from
the server to the client because it is the Table component that must do the
searching or counting (some table components may be smart enough to use SQL in
certain cases, but at least most do not).
If used in a grid, Table components must frequently execute multiple queries to
fill the grid whenever you change record positions.
Table components prevent you from using the power of SQL when working against a
real SQL server - they only see physical tables (or views in SQL systems),
whereas you can use Query components to select any relationships between any
number of tables and get exactly the data you need.
With Query components, you still need to use them right to get the most out of
them, but the point is that you can use them right with Clisnt/Server
databases.
With the exception of extremely small "lookup" type tables (e.g. State codes) always
use Where clauses to limit the number of records brought back. Unless you are
performing batch processing there is rarely a genuine need to fetch all records
for a large table, this is especially true in the case of visual presentations
- users do not
need to browse thousands of records (they often think they do because that is
how they have worked previously).
Unless you really need every field in a table, always specify the fields
you actually need (e.g. "select cust_id, cust_name from...", not "select *
from...").
Avoid editing records in a grid, use grids only for selection. This allows you
to only select the minimum fields needed for selection, and then use another
query to select all fields for that one
selected record for editing purposes.
Unless the result set is small, never use the Filter property or OnFilter
event, or call RecordCount with a Query component, these force the entire
record set to be fetched. If you really need the record count, use another
query to get it so the server will do the counting and send back the count
itself instead of all the records.
