Delphi: check if Record of DataSet is visible or filtered

Question

At work we have a component called a "ClientdatasetGrid", which allows the user to sort the records of the grid by clicking on one or multiple column-titles.

I have made a component for work also, a descendant from TEdit, which I call TDBFilterEdit.

once you assign a DataSet or DBGrid to it, it creates an OnFilterRecord event for the DataSet and after you stop changing the text that Event is executed.

the problem arises whenever the Dataset is already filtered and the user sorts the grid.

the grid-component adds IndexDefs to the Clientdataset by first deleteing the current IndexDef, Updating, Adding the new Index and updating again.

whenever an index is deleted or added my OnFilterRecord event is triggered. I mitigated this by disableing controls and NIL-ing the OnFilterRecord event from inside the grid until the new index is added.

cds.DisableControls();
try
  extProc:=nil;  
  if (TMethod(cds.OnFilterRecord).Code<>nil) and (TMethod(cds.OnFilterRecord).Data<>nil) then 
  begin
    TMethod(extProc):=TMethod(cds.OnFilterRecord);
    cds.OnFilterRecord:=nil;
  end; 
  ...
  ...  //<-- Delete Index & create new Index
  ...
finally
  cds.OnFilterRecord:=extProc;
  cds.EnableControls();  
end;

Once the Event is assigned again, it is immeadeately called and is iterating through all X records even though the user may only see 5.

Now I am searching for a way to see if a record is already filtered out so I can skip it inside my filter-method if the text hasn't changed.

---

Edit: Since a MVCE has been demanded I'll post a short version of my OnFilterRecord procedure.

• the following procedure is executed everytime the component hasn't recieved an input for 1 second
• fStringtypes and fTimeTypes are both a set of TFieldType
• fStringTypes:=[ftString,ftMemo,ftFMTMemo,ftFixedChar,ftWideString];
• fTimeTypes:=[ftDate,ftTime,ftDateTime,ftTimeStamp];

• after the procedure is completely finished the timer is disabled and controls are enabled again.

  procedure TDBEditFilter.FilterRecords(DataSet:TDataSet; var Accept:Boolean);
  var
    ...
  begin
    //initiliaztion//
    s:=FilterText;  //Filtertext=User Input into the TDBEditFilters Textfield
    TestFloat:=0;    
    Accept:=False;
    /////////////////
  
    for i:=0 to fDBGrid.Columns.Count-1 do  //for all DBGrid-Columns
    begin           
      if fDataSet.FieldByName(fDBGrid.Columns[i].FieldName).DataType in fStringTypes then
      begin                 
        Strvalue:=fDataSet.FieldByName(fDBGrid.Columns[i].FieldName).AsString;
  
        Accept:=AnsiContainsText(Strvalue,s); //<--to ignore Upper/lowercase
      end
      else if fDataSet.FieldByName(fDBGrid.Columns[i].FieldName).DataType in fTimeTypes then  
      begin
  
         StrValue:=DateTimeToStr(fDataSet.FieldByName(fDBGrid.Columns[i].FieldName).As   DateTime,Local_Form_Settings);
        Accept:=Pos(StrValue,s)<>0;
      end
      else if fDataSet.FieldByName(fDBGrid.Columns[i].FieldName).DataType=ftBlob then
      begin
        //ignore Blob
      end
      else //whatever fieldtype is left must be a numeric Field-type like integer or float
      begin 
        if TryStrToFloat(s,TestFloat)=True then
        begin
          Accept:=(TestFloat=fDataSet.FieldByName(fDBGrid.Columns[i].FieldName).AsFloat);
        end;
      end;
  
      if Accept=True then break;  //stop checking this record and check next record
    end; 
  end;
  

Answer 1

I thought I would post this as a separate answer because I've been experimenting with a "Filter TEdit" that works in a similar way as I'm guessing yours does, and it doesn't seem to exhibit any particular performance problems. My main assumption is that you are using one filter TEdit per datafield of interest, rather that a single one into which the user types a compound Sql-like expression including the field names, comparison operators, etc.

The number of guesses I've had to make is why I said it would have been helpful for you to include an MCVE.

I've written it to be self-contained, i.e. it generates its own data instead of needing an external database.

As you'll see if you try it, with a CDS containing, say, 3000 records, the time to update the filters is a few tens of milliseconds (under 20 on my laptop). If the CDS contains 30000 records, the filter update time increases roughly linearly to about 200 ms which seems perfectly acceptable from a gui-responsivenes pov.

(Traditionally, TCDSs have been regarded as hitting a brick wall performance-wise when the number of records gets into the tens of thousands)

Note that for simplicity

a) I haven't used a DateTime fiield for BirthDate or whatever, because of the complications of dealing with partial dates inputted by the user.

b) In the OnFilterRecord event, the LastName, FirstName and Age comparisons are done by comparing the field as a string with the corresponding filter expression.

c) The Filter expressions, if non-blank are left- and right-padded with asterisks and the value comparisons are done using the MatchesMask function from the Masks unit. See FilterExpr.

d) The IndexDef's FieldNames are composed of the names of the fields for which the filter edit's text is non-blank.

e) If the gui-updating is too slow if the user rapidly types several characters in succession into the TEdits, you can work around this by replacing the TEdits' OnChange event code by code in their KeyUp event which enables a TTimer which has an interval of, say, 150 ms. Then, in its OnTimer, call UpdateFilter.

Code:

  TForm1 = class(TForm)
    DBGrid1: TDBGrid;
    CDS1: TClientDataSet;
    DataSource1: TDataSource;
    Memo1: TMemo;
    CDS1ID: TIntegerField;
    CDS1Age: TIntegerField;
    CDS1LastName: TStringField;
    CDS1FirstName: TStringField;
    edLastNameFilter: TEdit;
    edFirstNameFilter: TEdit;
    edAgeFilter: TEdit;
    procedure CDS1FilterRecord(DataSet: TDataSet; var Accept: Boolean);
    procedure edLastNameFilterChange(Sender: TObject);  //  Set the OnChange events for the
    //  FirstName and Age TEdits to this, too
    procedure FormCreate(Sender: TObject);
  private
    procedure Log(const Title, Msg: String);
    function FilterExpr(const Input: String): String;
  protected
  public
    LastNameFilter,
    FirstNameFilter,
    AgeFilter : String;
    IndexFields : String;
    IndexDef : TIndexDef;
    procedure UpdateFilterExprsAndIndex;
    procedure UpdateFilter;
  end;

[...]
rocedure TForm1.FormCreate(Sender: TObject);
var
  i : Integer;
  Ch1,
  Ch2 : Char;
  LastName,
  FirstName : String;
  Age : Integer;
begin
  CDS1.CreateDataSet;
  CDS1.DisableControls;
  try
    for i := 1 to 30000 do begin
      Ch1 := Chr(Ord('a') + random(26));
      Ch2 := Chr(Ord('a') + random(26));
      LastName:= StringOfChar(Ch1, 1 + Random(10));
      FirstName := StringOfChar(Ch2, 1 + Random(10));
      Age := Trunc(Random(71));
      CDS1.InsertRecord([i, LastName, FirstName, Age]);
    end;
  finally
    CDS1.First;
    CDS1.EnableControls;
  end;
end;

procedure TForm1.Log(const Title, Msg : String);
begin
  Memo1.Lines.Add(Title + ' : ' + Msg);
end;

procedure TForm1.CDS1FilterRecord(DataSet: TDataSet; var Accept: Boolean);
begin
  Accept := True;
  if LastNameFilter <> '' then
    Accept := MatchesMask(CDS1LastName.AsString, LastNameFilter);
  if not Accept then exit;

  if FirstNameFilter <> '' then
    Accept := Accept and MatchesMask(CDS1FirstName.AsString, FirstNameFilter);
  if not Accept then exit;

  if AgeFilter <> '' then
    Accept := Accept and MatchesMask(CDS1Age.AsString, AgeFilter);
end;

procedure TForm1.edLastNameFilterChange(Sender: TObject);
begin
  UpdateFilter;
end;

procedure TForm1.UpdateFilter;
var
  T1 : Integer;
begin
  T1 := GetTickCount;
  UpdateFilterExprsAndIndex;
  CDS1.DisableControls;
  try
    CDS1.Filtered := False;
    if (edLastNameFilter.Text <> '') or (edFirstNameFilter.Text <> '') or (edAgeFilter.Text <> '') then begin
      CDS1.Filtered := True;
    end;
    if IndexFields <> '' then
      CDS1.IndexDefs[0].Fields := IndexFields;  //  Warning: This IndexDef needs to exist
    Log('Filter update time', IntToStr(GetTickCount - T1) + 'ms');
  finally
    CDS1.EnableControls;
  end;
end;

function TForm1.FilterExpr(const Input : String) : String;
begin
  Result := Input;
  if Result <> '' then
    Result := '*' + Result + '*';
end;

procedure TForm1.UpdateFilterExprsAndIndex;
begin
  LastNameFilter := FilterExpr(edLastNameFilter.Text);
  FirstNameFilter := FilterExpr(edFirstNameFilter.Text);
  AgeFilter := FilterExpr(edAgeFilter.Text);

  IndexFields := '';
  if LastNameFilter <> '' then
    IndexFields := 'LastName';
  if FirstNameFilter <> '' then begin
    if IndexFields <> '' then
      IndexFields := IndexFields + ';';
    IndexFields := IndexFields + 'FirstName';
  end;
  if AgeFilter <> '' then begin
    if IndexFields <> '' then
      IndexFields := IndexFields + ';';
    IndexFields := IndexFields + 'Age';
  end;
end;

I hope this at least gives you a basis for comparison with your own code so that you can identify any bottlenecks.

Update Rather to my surprise, I found that with the compound filter expression I used for testing, it is much faster to set the CDS's Filter to the expression and leave it do to the filtering using OnFilterRecord, With 30000 records, UpdateFilter2 takes under 20 ms, compared with 200 ms for a similar expression set using the `UpdateFilter'.

procedure TForm1.btnSetFilterExprClick(Sender: TObject);
begin
  edFilter.Text := 'LastName=''aaa'' and FirstName = ''zz'' and Age > 30 ';
  UpdateFilter2;
end;

procedure TForm1.UpdateFilter2;
var
  T1 : Integer;
begin
  CDS1.OnFilterRecord := Nil;
  T1 := GetTickCount;
  CDS1.DisableControls;
  try
    CDS1.Filtered := False;
    CDS1.Filter := edFilter.Text;
    if CDS1.Filter <> '' then begin
      CDS1.Filtered := True;
    end;
    Log('Filter update time', IntToStr(GetTickCount - T1) + 'ms');
  finally
    CDS1.EnableControls;
  end;
end;

Answer 2

I don't think you could do this using the standard TClientDataset's implementations of indexing and filtering.

Changes to the index or filter on a TCDS both invoke a traversal of its data records and you have no control over that because in both cases, the TCDS functionality depends on calls into the interfaces provided by Midas.Dll.

Setting up a new or changed index involves calling procedure TCustomClientDataSet.SortOnFields which in turn calls Cursor.SortOnFields, where Cursor is of type IDSCursor - see DSIntf.Pas

Equally, changing the CDS filter involves calling TCustomClientDataSet.AddExprFilter, which in turn calls FDSCursor.AddFilter, where FDSCursor is again of type IDSCursor.

So, you would need to re-implement both of these at the Midas level, on the other side of the IDSCursor interfave to avoid the default behaviour.