Posted by John, DelphiLand Team on March 18, 2009
In Reply to indexing tclientdataset: temporary or persistent? posted by Phil Birell p16812 on
March 16, 2009
: I'm porting a project with dBase files (DBF) to using MyBase XML, following directions
of John of the DelphiLand team. Works out nice!
: How about indexing the database, should I use "temporary" or "persistent" indexes?
Is the difference that persistent indexes are saved into the XML file, just like DBF indexes were saved
in a separate MDX file? What are the pros/cons of persistent versus temporary? Any other tips on indexes
and finding records?
Contrary to what you would think, "persistent" indexes are not saved to disk. When a Delphi ClientDataset is closed (either explicitly in code or because the application is stopped), persistent as well as temporary indexes are discarded.
The difference is, that as long as the ClientDataset is active, "persistent" indexes stay in memory, i.e. you can have several persistent indexes of which one is in use for ordering the records. A "temporary" index on the other hand, is discarded from the moment that you specify another index. So, if a lot of changing between different sorting orders is needed, this is a lot slower with a temporary index, because the index is rebuilt each time from scratch.
I advice to always use the so-called persistent indexes, since they have no drawbacks compared with temporary indexes. I don't see any good reason for using a temporary index, unless it's just a bit easier to specify it. But the few seconds that you gain at design-time aren't worth the sometimes massive delays at run-time.
Notes:
1. A temporary index is specified by putting the name(s) of the field(s) on which to index in the property ClientDataSet.IndexFieldNames. For example:
cdsPeople.IndexFieldNames := 'LastName';
Separate multiple fields with a semi-colon (;) for example:
cdsPeople.IndexFieldNames := 'LastName;FirstName';
2. For a persistent index, you firstly use the property ClientDataSet.IndexDefs to
set up one or more indexes. At design-time, this opens a dialog that lets you specify the field(s),
several options, and a name for each index. This can also be done at runtime, but that's a bit more
complicated.
For example, at design time you've set up 'ixLastFirst' as an index on last and first name.
Next, set property IndexName to the name of the desired index, for example:
cdsPeople.IndexName := 'ixLastFirst';
3. Finding records is the same for both types of indexes, with ClientDataSet.FindKey([values]) just as is done for the TTable component (that you used for DBF, Paradox,... tables). For example:
Found := cdsCountries.FindKey(['France']);