Desktop vs. CS

By: Kevin Frevert

Abstract: The following paper will discuss the differences in desktop and client/server databases and why Delphi's desktop database components (TTables) are inefficient in a client/server environment.

Desktop db design versus client/server design

Trying to mix desktop database (Paradox, dbase, etc) and client/server database (MSSQL, Oracle, Interbase, etc) design often lead to deadly consequences.

Well, "deadly" may be too strong a word, but nothing will have your end-users hunting you down faster than that little SQL hour glass.
"Why does it take nearly an hour to open a million record TTable in Delphi?". That is a common question/problem on Borland's Delphi SQL server newsgroups. A developer tries to move a legacy desktop database application to a client/server database and running into the inherent problems with desktop database design in a client/server environment. The following paper will discuss the differences in desktop and client/server databases and why Delphi's desktop database components (TTables) are inefficient in a client/server environment.

TTables vs. TQuerys - Differences in desktop and client/server databases

The reason there is both a TTable and a TQuery component is due to the fact there 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.

TTable is specifically designed to work best with table-oriented systems - it is native to them. Using a TQuery against such databases is slower because they do not understand SQL and so the BDE must interpret the SQL and convert it to table calls for that database.
TQuery is specifically designed to work best with set-oriented databases that understand SQL directly and were designed to work this way. Using a TTable against such a system is slower because the BDE must convert the table functions into SQL instructions to be sent off to the database.

Some of the things that TTables do that eat time and resources over a network with an SQL system are:

  • On Opening (or Active = True), 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 (only Live TQuerys do this).
  • If you have large records with many fields, TTable will always select all fields even if you only want one or two. This is especially bad if the table contains blobs and you do not need them.
  • Using Locate or FindKey or RecordCount forces all records to be fetched because such searching / counting has to be done on the client side. This can be eased by using a good filter (in the Filter property, not the OnFilter event) to limit the records that need to be fetched (Filters are turned into SQL where clauses by the BDE).
  • If used in a grid, TTable must frequently execute multiple queries to fill the grid whenever you change record positions.
  • Tables 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 write TQuerys to select any relationships between and number of tables and get only exactly the data you need.

With TQuerys, 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 regard to SQL 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, if you do not then you are defeating the whole purpose of using them.
  • 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...").
    - A tip here is to 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 TQuery, this forces 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.

Credit where credit is due
The above information was taken from a borland.public.delphi.database.sqlservers newsgroup post written by:
Wayne Niddery (Logic Fundamentals Inc.)
Wayne is a member of TeamB

Server Response from: ETNASC03