Advantages of TQuery over TTable for large tables?

This is the forum for miscellaneous technical/programming questions.

Moderator: 2ffat

Advantages of TQuery over TTable for large tables?

Postby jbj » Mon Nov 01, 2004 3:53 pm

Can anyone give me their experiences converting a database from using TTable objects to TQuery objects?
Does anyone know whether this would speed up our applications?
We have several apps which work fine in low user count/small DB situations, but when we get several users and a large DB, even with a huge server, the system slows considerably.
Worse, there are periods of several minutes where everyone's hourglassing.
Someone told me that TQuery objects with queries for the specific records needed would be better than TTable objects (which evidently bring the whole table to the client <arg!>
Can anyone give me facts or experiences on this?
Thanks so much
john j
jbj
 

TTable Vs. TQuery

Postby 2ffat » Wed Nov 03, 2004 12:05 pm

There may be several factors that can make a query faster than a table. These factors may include local vs. server based database, size of table (as you have found out), and the how the data is being selected.

I'm going from memory since I haven't had time to look up my facts so someone out there may correct me. When you use a TTable, all data is being pulled from the database into the table. If the server is on another machine you are going to experience a delay getting large amounts of data across the network. Even if the server is on the local machine, large amounts of data is still being copied.

A query, on the other hand, only gets the the data that matches a specific criteria. Thus, you only will get a portion of the data. Unless, of course, your query is SELECT * FROM MyTable. In this case, the query will be only marginally faster. I believe querys are run on the server instead of the local machine so that accounts for the difference.

If you need to use TTable, you can use a range function on the dataset to only pick up the data you need provided you use indexes. One thing to note here is to avoid using filters with large tables. The reason is that every record is collected, then filtered so you are not saving anything.

The following is a quote from my help file. You can also read more in the Developer's Guide that was supplied with BCB. See "Understanding datasets."
Both ranges and filters restrict visible records to a subset of all available records, but the way they do so differs. A range is a set of contiguously indexed records that fall between specified boundary values. For example, in an employee database indexed on last name, you might apply a range to display all employees whose last names are greater than “Jones” and less than “Smith”. Because ranges depend on indexes, you must set the current index to one that can be used to define the range. As with specifying an index to sort records, you can assign the index on which to define a range using either the IndexName or the IndexFieldNames property.

A filter, on the other hand, is any set of records that share specified data points, regardless of indexing. For example, you might filter an employee database to display all employees who live in California and who have worked for the company for five or more years. While filters can make use of indexes if they apply, filters are not dependent on them. Filters are applied record-by-record as an application scrolls through a dataset.
In general, filters are more flexible than ranges. Ranges, however, can be more efficient when datasets are large and the records of interest to an application are already blocked in contiguously indexed groups. For very large datasets, it may be still more efficient to use the WHERE clause of a query-type dataset to select data.
James P. Cottingham

Look at me still talking
when there is science to do.
User avatar
2ffat
Forum Mod
Forum Mod
 
Posts: 433
Joined: Wed Jun 23, 2004 7:07 am
Location: South Hill, VA


Return to Technical

Who is online

Users browsing this forum: No registered users and 8 guests

cron