Master-detail performance

[piopio]

Hello,

I have a performance issue when I link two DB tables in master-detail relation.
I have a PgDataset called PgDataSourceMasterData linked to a PgQuery called PgQuery1. PgQuery1 sql statement is SQL.text:='Select tbl_customer_master_data.*,.......’
If I run the query and then scroll among the several records, the scrolling action is fluid and fast.

then I link a PgTable to the PgQuery1 by assigning the following properties:
• TableName= tbl_invoices
• MasterSource=PgDataSourceMasterData
• MasterFields=id
• DetailFields=customer_id

If I run again the query and open tbl_invoices the scrolling action is very slow (it takes several seconds to go from one customer to the other).

Is there a better method to link two (or more) tables and to keep the scrolling action fluid and fast ?

Many thanks
Pio Pio

[ROD]

Hello Pio Pio

The problem may be due to many records are selected when scrolling in detailing table by PgQuery1.
If so, set the FetchAll property of PgTable to False (True by default). This allows to select only FetchRows rows (25 by default), not all at once.

[piopio]

Hello Pio Pio

....If so, set the FetchAll property of PgTable to False (True by default). .....

Thank you very much Rod !!! This did the trick !!!

What else should I look at in order to improve the performance further ?


Many thanks


Pio Pio

[ROD]

Everything depends on the field types in the returned queries, the number of requested records, and connection features.

For example, when widely using blobs, the DeferredBlobRead option can help very much.

UniDirectional=true, when there is a great amount of records, lets save a lot of storage place on a client (the number of records specified in FetchRows is cached). But you should be careful with it: when UniDirectional=true, if you use, for example, DBGrid, a user will not be able to scroll upward in the grid.

ExtendedFieldsInfo=false lets reduce time for data fetching by not retrieving info about necessity of fields in a table, default values, etc., but it should be used only when reading data, not editing.

Try set various values in FetchRows (if FetchAll=False) depending on network capacity. If it is high, set the value, for example, to 25, 100, 500. As a result, the number of calls to the server will be reduced, that can speed up fetching.

Always welcome)

[piopio]

Hello again,

Following the suggestions above I linked the main query to other two queries as master details and now in order to go from one record to another it takes a few seconds each time.

What else can you suggest in order to improve the performance ?


Many thanks


Pio Pio

[ROD]

Hello Pio Pio

If to apply the above for all datasets that are included to master-detail, this will be enough. i.e. you shouldn't forget about optimization of all detailing datasets.
If this is done, but the great delay still occurs when scrolling records, please send us the scripts for creating and filling your tables and relations between them. And also specify how many records there are in these tables.

[piopio]

Hello,

I set up a test application using Delphi standard components and Devart PostgreSQL DAC. Here, the master-detail performance is fine. The issue must be elsewhere.

Thanks for your help

Pio Pio