Speeding up Master/detail browsing
Posted: 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 :
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
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;
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