General 

Before AnyDAC v5 the TADTable was a TADQuery with TableName to SELECT * FROM TableName translator. Although TADQuery is the right tool for client-server development, many AnyDAC customers still need old smart BDE TTable but for AnyDAC. Are you in the same boat ?

"So, what is good in the TTable ?", you may ask. It never fetches all records using single result set. It creates a live window into the table records. The window contains comparably small number of records (~200). The window is moving in the table data, when the program is navigating in the dataset. That gives you ability to work with millions of records without feching them all to the client and without the need to reopen query to refresh dataset.

"But where is a trick ?", you may ask again. As I sayd the TADQuery is the right tool for the client-server development, as it performs exactly the query you have specified with the good restricting WHERE, with limited SELECT list, etc. And TTable and now TADTable generate different queries behind the scena to implement the live window. That gives more work to the DBMS but makes you happy with how fast you can now go to the dataset bottom or just open the dataset.

Implementation

Because many our customers are in need for such TADTable for successful BDE to AnyDAC migration, we implemented it. The TADTable API remains mostly unchanged, but internals were reworked completely. Comparing to "old" TADTable we got:

• ability to scroll through extremely large tables without delays at jumping to last record or how else. Tested on 1M of records.
• Locate, GotoKey, ApplyRange, bookmarks and similar operations work with the live data. IOW, are server side.
• filtering works with live data. IOW, is server side.

The TADTable generates a SELECT statements basing on its properties and send it to a DBMS. The TADTable has two main operation modes:

• live data window mode. In this mode AnyDAC queries and keep in memory only 2 * FetchOptions.RowsetSize of records. The Filter property, range filtering, IndexFieldNames and IndexName properties, Locate and Lookup methods, key locating, setting RecNo are performed by additional SELECT commands or by setting additional phrases for the main SELECT command. That allows to open and work with the very large tables without serious delays and memory usage.
• standard mode. This mode is similar to TADQuery. TADTable generates a single SELECT command and uses the result set to walk through table records.

The live data window mode is used when the following conditions are met, otherwise the standard mode is used:

• CachedUpdates = False;
• FetchOptions.Unidirectional = False;
• FetchOptions.CursorKind in [ckAutomatic, ckDynamic] and table has primary or unique key.

The mode may be changed only when TADTable is inactive. Consequently, for example, the CachedUpdates may be changed only when table is inactive. In general, to make the live data window working effectively and correctly the TADTable requires:

• a primary or unique key to be defined for a table and accessible to TADTable. See Unique Identifying Fields for more details.
• a database sort order should match a client side sort order. The FormatOptions.SortLocale and SortOptions may be used to adjust the client side sort order.

Additionally, the performance may be improved by setting FetchOptions.LiveWindowMode to wmApproximate.

Behind the scene 

And few code sample showing how this works. Here we open the TADTable and jump to the last record:

 
ADTable1.TableName := 'Orders';
ADTable1.Open;
ADTable1.Last;
// -> SELECT * FROM "Orders" ORDER BY OrderID DESC {limit 100}

And perform Locate:

 
ADTable1.Locate('OrderID', 100, []);
// -> SELECT * FROM "Orders" WHERE OrderID = :OrderID ORDER BY OrderID ASC {limit 1}

Here {limit} is AnyDAC escape sequence. Note, to work properly TADTable requires primary key to be defined for the table. Also to make TADTable working effectively, the database programmer should create required indexes. To see queries which TADTable sends to a DB, enable tracing output for connection.

Also note, GetCustomOrderBy method was removed, because now TADTable always builds the ORDER BY phrase and it cannot be changed freely.

Afterwords

I want to thank MAD for helping us to implement new functionality and for his valuable remarks regarding TADTable !

The new functionality soon will enter into beta testing. If you have an active AnyDAC subscription and would like to participate in AnyDAC v5 beta testing, please contact This e-mail address is being protected from spam bots, you need JavaScript enabled to view it .