Delphi ADO Data Filter

Question

In the application that I create at work, I have a large database with a table in which they say "People" with 100,000 + lines. In addition, the records in this table contain two data types: the Parent type and the Child type, where each record of the Child type has its parent's database identifier in the special column "Child_OF".

In memory, both types of db records are represented by the corresponding classes "TParent" and "TChild", where each parent class has a "children: TList" field.

What is the fastest way, using ADO, for: - creating a list of parents and correctly assigning their children to them ...

As I see it ... you can solve the problem 1) extract in the volume (one sql-query) all parents from the table and create a list of parents with empty lists of children. 2) to extract in bulk all the children and for each parent try to find their children from the corresponding data set.

Here is an example of what I mean for the program assignment phase ...

procedure assignParentsTheirChildren(parentList: TList<TParent>;
  ma_people: TADOTable);
var
  i: Integer;
  qry: TADOQuery;
  aChild: TChild;
  aParent: TParent;
begin

  // create the query
  qry := TADOQuery.Create(nil);
  qry.Connection := ma_people.Connection;
  // set the sql statement to fetch all children ...
  qry.SQL.Clear;
  qry.SQL.Add('Select * from ' + ma_people.TableName + ' WHERE ChildOF <> ' +
    QuotedStr(''));
  // supposedly do some optimization---
  qry.CursorLocation := clUseClient; // load whole recordset in memory
  qry.DisableControls;
  // disable controls ensures that no dataset bound control will be updated while iterating the recordset
  qry.CursorType := ctStatic; // set cursor to static
  // open dataset
  qry.Open;
  // ***EDIT*** for completeness I add the suggestion made by Agustin Seifert below
  qry.RecordSet.Fields['ChildOf'].Properties.Item['Optimize'].value := true;

  for i := 0 to parentList.count - 1 do
  begin
    // get daddy
    aParent := parentList[i];

    qry.Filter := 'ChildOF = ' + QuotedStr(IntToStr(aParent.parentID));
    qry.Filtered := true;

    while (not qry.EOF) do
    begin
      aChild := TChild.Create;
      getChildFromQuery(aChild, qry); // fills in the fields of TChild class...
      aParent.children.Add(aChild);
      qry.Next;
    end;

  end;
  qry.Free;
end;

I think the biggest bottleneck of the above code is that I filter the data for each new parent. Is there any faster processing with seek () or find / find ...? In principle, it can be assumed that my data set is static (at the time of creating the list of parents) and the latency of the network is infinite :) (that is, I first want to make the child in relation to the parent destination from memory). Many thanks!

btw I am using Microsoft SQL Server 2012.

Answer 1

/​​, , , ADO. :

var
  qry: TADOQuery;
  rs: _Recordset;
  ...
begin
  ...
  //after qry.Open;
  rs := qry.Recordset;
  rs.Fields['YourField'].Properties.Item['Optimize'].Value := True; //YourField = ChildOF in your case

. , .

msdn: - (ADO)