Author: Adailton C38044
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.