Speeding up Master/detail browsing

Discussion of open issues, suggestions and bugs regarding PgDAC (PostgreSQL Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
GoSane
Posts: 4
Joined: Fri 15 Aug 2014 10:46

Speeding up Master/detail browsing

Post by GoSane » Mon 19 Oct 2020 15:49

Hello,

I have a simple master/detail TPgQuery setup. Master has ~100000 records and detail has ~340000 (distributed quite evenly). I browse master by code with something like :

Code: Select all

while not qmaster.eof do begin
  qmaster.next;
end;
The problem is that it is extremely slow : ~15 seconds to navigate through 100 master records. I have set up a test program that does nothing (apart from measuring the time). That's about 500 records (100 master + 400 detail) in 15 seconds, that's 33 records/second :-(

qDetail's Mastersource is connected to the qMaster's Datasource, Masterfields and detailfields are filled with the (serial/integer) primary key of qMaster, qDetail's IndexFieldNames contains that same primary key. It is configured in FetchAll=True (slower to start but no network lag during traversal), Options.LocalMasterDetail is True.

The even stranger thing is that if I remove the IndexFieldNames, time to process 100 master records "falls" to 11 seconds. Wasn't this option supposed to speed up Master/detail?

I don't see what I could do to speed things up.

Thanks for your help.

Using Delphi 10.3 and PgDAC 6.2.4

oleg0k
Devart Team
Posts: 190
Joined: Wed 11 Mar 2020 08:28

Re: Speeding up Master/detail browsing

Post by oleg0k » Fri 30 Oct 2020 20:42

Hello,

Try the code:

Code: Select all

qmaster.DisableControls;
while not qmaster.eof do begin
  qmaster.next;
end;
qmaster.EnableControls;
wbr, Oleg
Devart Team

dschuch
Posts: 75
Joined: Thu 05 Feb 2009 15:29
Location: Dresden

Re: Speeding up Master/detail browsing

Post by dschuch » Fri 05 Mar 2021 11:53

Hi,

are u sure that disablecontrolls diables master/detail?

I have to check this everytime again if i run through that. It disables the visual controls, but master details too?

in every case possible:
  • remove the master source and set again after
    close the detail dataset and reopen
dont forgett try finally.


Look for "NotifyDetails" as well as "ControlsDisabled "

Code: Select all

procedure TDataSet.DataEvent(Event: TDataEvent; Info: Longint);

  procedure NotifyDetails;
  var
    I: Integer;
  begin
    if Assigned(FNestedDataSets) then
    begin
      if State <> dsInsert then UpdateCursorPos;
      for I := 0 to FNestedDataSets.Count - 1 do
        with TDataSet(FNestedDataSets[I]) do
          if Active then DataEvent(deParentScroll, 0);
    end;
    if (State = dsBlockRead) then
      for I := 0 to FDataSources.Count - 1 do
        TDataSource(FDataSources[I]).NotifyLinkTypes(Event, Info, False);
  end;

var
  I: Integer;
  NotifyDataSources: Boolean;
begin
  NotifyDataSources := not (ControlsDisabled or (State = dsBlockRead));
  case Event of
    deFieldChange:
      begin
        if TField(Info).FieldKind in [fkData, fkInternalCalc] then
          SetModified(True);
        UpdateCalcFields;
      end;
    deFieldListChange:
      FieldList.Updated := False;
    dePropertyChange:
      FieldDefs.Updated := False;
    deCheckBrowseMode:
      CheckNestedBrowseMode;
    deDataSetChange, deDataSetScroll:
      NotifyDetails;
    deLayoutChange:
      begin
        FieldList.Updated := False;
        if ControlsDisabled then
          FEnableEvent := deLayoutChange;
      end;
    deUpdateState:
      if ControlsDisabled then
      begin
        Event := deDisabledStateChange;
        Info := Integer(State <> dsInactive);
        NotifyDataSources := True;
        FEnableEvent := deLayoutChange;
      end;
  end;

  if NotifyDataSources then
  begin
    for I := 0 to FDataSources.Count - 1 do
      TDataSource(FDataSources[I]).DataEvent(Event, Info);
    if FDesigner <> nil then FDesigner.DataEvent(Event, Info);
  end;
end;

oleg0k
Devart Team
Posts: 190
Joined: Wed 11 Mar 2020 08:28

Re: Speeding up Master/detail browsing

Post by oleg0k » Thu 11 Mar 2021 15:25

Hello,
For information about the DisableControls property, see http://docwiki.embarcadero.com/Librarie ... leControls
If DisableControls\EnableControls doesn't help, please send us a sample project with code comments through https://devart.com/company/contactform.html

wbr, Oleg
Devart Team

Post Reply