Speed up TDataSet Lookups

[Guest]

The Delphi world contains a lot of database-solutions. One school of them are the TDataSet-based libraries, components and classes. If you are using TDataSet-based cached Lookups in a more "modern Delphi" you can easily speed up lookup handling.

 

I'm posting this because when i googled in order to fix a couple of thing in my current project, i did not find much, almost nothing on this.

 

This can save HUGE amounts of processor cycles if:

• You use "VCL Lookups" (they are not strictly VCL, but that is the term for example in DevExpress* forums) and have a lot of value-rows and a lot of lookup values.
• You use a component that cannot use lookup values and therefore need to "expand" the lookup values with their actual looked up "listfield" value.

 

* I use the DevExpress PivotGrid a lot. The "QuantumGrid" has a "SortByDisplayText" property for sorting on lookup values. But the PivotGrid does not. So for the data fed to a PivotGrid i see to it to use "VCLLookups" so that the PivotGrid handles looked-up values rather than their IDs... as if they where text. This is the only way to get columns/rows sorted the way the users want. In a scenario with 100K rows and 80K lookup values having a character length of 256, this decreased the time needed to load the PivotGrid with data (for each fetch the lookup values is "expanded").

 

In some scenarios (like the one i describe above) the time needed for the operation (and that is the time the user has to wait) decreased from 120 to 6,5 seconds. The reason being the "LookupCache" in Data.DB uses an O(1) algo on a Variant. Let's change that like so:

 

1. The treading "lightly" approach is to override you TDataSet-based class, mine is called TkbmMemTable:

 

...

interface

...

    // Faster lookups
  TDictionaryLookupList = class(TLookupList)
  private
    FDict: TDictionary<Variant, Variant>;
  public
    constructor Create; override;
    destructor Destroy; override;
    procedure Add(const AKey, AValue: Variant); override;
    procedure Clear; override;
    function ValueOfKey(const AKey: Variant): Variant; override;
  end;


  TkbmMemTableEx = class(TkbmMemTable)
  protected
    function GetLookupListClass(Field: TField): TLookupListClass; override;
  end;

...

implementation

...

function TkbmMemTable.GetLookupListClass(Field: TField): TLookupListClass;
begin
  Result := TDictionaryLookupList;
end;

...

{ TDictionaryLookupList }

procedure TDictionaryLookupList.Add(const AKey, AValue: Variant);
begin
  if not VarIsNull(AKey) then
    FDict.Add(AKey, AValue);
end;

procedure TDictionaryLookupList.Clear;
begin
  FDict.Clear;
end;

constructor TDictionaryLookupList.Create;
begin
  inherited;
  FDict := TDictionary<Variant, Variant>.Create;
end;

destructor TDictionaryLookupList.Destroy;
begin
  if FDict <> nil then Clear;
  FDict.Free;
end;

function TDictionaryLookupList.ValueOfKey(const AKey: Variant): Variant;
begin
  if (not VarIsNull(AKey)) and (not FDict.TryGetValue(AKey, Result)) then
    Result := Null;
end;

I am NOT "indexing" on Variant here. The TDictionary<Variant, ....> will convert variants to strings because the Dictionarys keys are hashed. I do not think that any database solution will yield a value when the key is null. But please comment if you are aware of such a scenario.

 

2. The "One Dict to rule them all" approach:

 

Instead of having each flavour of TDataSet kick in the new LookupList you can substitute the vanilla one for our dictionary based one for the entire application:

 

....

initialization

DefaultLookupListClass := TDictionaryLookupList;

This will see to it that any TDataSet that does not have it's own lookuplist will use the dictionary one.

 

This approach can be tested in almost any application in under an hour. It would be interesting to receive a comment or two if it affected the performance of your application.

 

Please note: This is only useful for lookup fields that have the "LookupCache" flag set.

 

HTH,

 

/Dany

 

Disclaimer one: I did not look at lookups on segmented indices.

Disclaimer two: All coding is done under the responsibility of the coder, not the author of this article.

Edited October 26, 2019 by Guest

[Lars Fosdal 1792]

I have a class type that I call a GridSet, and I use RTTI to match object prop names with DB field names (and attributes for overrides) for multi-row datasets,

I build a list of property setters that are ordered the way the fields exist in the DB, for all the fields that exist both in the data set and the object and then I simply loop that list row by row when loading the dataset. 

 

I also have an attribute driven variation of filling a GridSet that builds the field definitions dynamically from the DataSet - so that I basically can fill the GridSet from any query, and display it in my GridView class, which attaches to a TAdvStringGrid and sets up the grid purely in code with the various event handlers for sorting and filtering. Hence I can show the result of any query in a grid with one line of code - no visual design required.  The benefit of declaring the fields in a gridset, is that I can specify nicer titles, max widths, hints, etc.

[FredS 138]

I have a couple of instances where this is more useful without the LookupDataset, additions below:

interface

type
  TDictionaryLookupList = class(TLookupList)
  public
    /// <summary>
    ///   Binds a Dictionary Lookup List to a fkLookup Field
    /// </summary>
    class function Bind(const AField: TField): TLookupList;  
    /// <summary>
    ///   Binds a Dictionary Lookup List to a fkLookup Field without binding to a LookupDataSet
    /// </summary>
    /// <param name="AKeyFields">
    ///   Keyfields used to identify the Entry, (Default=EmptyStr) will use the Existing KeyFields value.
    /// </param>
    class function BindSimpleList(const AField: TField; const AKeyFields: string = ''): TLookupList;
  end;


implementation

type
  TFieldHelper = class helper for TField
    /// <summary> Sets Private Field </summary>
    procedure SetLookupList(ALookupList : TLookupList);
  end;

procedure TFieldHelper.SetLookupList(ALookupList : TLookupList);
begin
  if Assigned(LookupList) then LookupList.Free;
  with self do FLookupList := ALookupList;
end;

class function TDictionaryLookupList.Bind(const AField: TField): TLookupList;
begin
  Assert(AField.FieldKind = fkLookup);
  AField.LookupCache := True;  
  Result  := TDictionaryLookupList.Create();
  AField.SetLookupList(Result);
end;

class function TDictionaryLookupList.BindSimpleList(const AField: TField; const AKeyFields: string = ''): TLookupList;
begin
  AField.LookupDataSet := nil;
  if (AKeyFields <> EmptyStr) then AField.KeyFields := AKeyFields;
  Result := Bind(AField);
end;

 

[Jacek Laskowski 57]

@Dany Marmur

 

I try this trick, it working, but I can't get values in lookup list of dbgrid. It's is impossible? What I'm doing wrong?

 

[Guest]

@Jacek Laskowski, i'm having problems reading the text in the screenshot. I suspect the setup of the LookupFireld.

Start afresh with Lookups that works w/o any additions and apply the 2nd version. Or are you trying FredS's approach?

I'm sorry that i cannot be of more help.

[Jacek Laskowski 57]

@Dany Marmur When you click on the picture, a larger version opens.
But the main question in this post is whether DBGrid is able to show combo with data in the cached lookup field?

When the lookup field is constructed traditionally, with a dictionary dataset, then of course the combo list in DBGrid is not empty.

[Guest]

Ah, then let's hope someone with fresh experience of the TDBGrid chimes in. I have not used it in ... well ... 15 years or so.