Delphi ADO dataset Filter

ado, delphi, performance

Solution

If you don't wanna change your code/logic, there's a way to optimize filter, find, sort operations in ADO. Access the recordset an optimize the involved fields:

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

This will create an index for the field. It takes a small amount of time vs the time it takes to filter lot of times without an index.

msdn: Optimize Property-Dynamic (ADO)

Problem

In an application I am building at work, I have a large database with a table say "People" with 100,000 + rows. Furthermore the entries in this table contain two types of data : Parent type and Child type, where each Child type entry has the database id of its parent in a special "Child_OF" column. In memory, both db entry types are represented by corresponding classes "TParent" and "TChild", where each parent class has the field "children : TList". Which is the fastest way, using ADO, to: - create a list of Parents and correctly assign to them their children... The way I see it... one can go about the problem by 1) retrieve in a bulk( by one sql query) all parents from the table and create the parents list with empty children lists. 2) retrieve in a bulk all children and for each parent try to find his/her children from the corresponding dataset. Here is an example of what I have in mind for the assignment stage of the program... ``` 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 guess the biggest bottleneck of the above code is that I am filtering the data for every new parent. Is there a faster rework using seek() or locate/find...? Basically one can assume that my dataset is static (during the time of creating the parents list) and network latency infinite:) (that is, I first want to do the child to parent assignment from memory). Many thanks! btw I am using Microsoft SQL Server 2012.

Original source