Speed issues retrieving large hierarchy

Data Abstract
1

Hi,

I briefly touched on the issue of master/detail data retrieval way back in 2014 but I’m revisiting it again.

I have various master/detail hierarchies in my system. One in particular is quite large, being four levels deep with a total of 13 tables (1-8-3-1).

I’m not using the moAllInOneFetch option anywhere, so details are retrieved “on the fly” as I iterate through parent records. This works fine most of the time but, when working with a lot of records, performance suffers.

I have a situation where I need to load a large number of the above hierarchy. For each top-level master record, I need to iterate through the entire hierarchy. This is causing a big performance problem as literally thousands of queries are generated as I iterate through the hierarchy for each master.

Is there any way I can retrieve an entire hierarchy for a given master in one hit? I’ve messed about the moAllInOneFetch but it’s never worked correctly for me with a multi-level hierarchy.

I don’t even mind working out which detail (and sub-detail) records are required for a given master myself, if there’s some way I can manually fetch them all in one go and populate the detail tables directly?

EDIT: I just tried a quick test with a three-level hierarchy (moAllInOneFetch off everywhere) and tried pre-populating the third level with all records belonging to all second-level records for all top-level records. Unfortunately, when I then subsequently open the top-level table, it appears to throw away all these records and then re-retrieve them individually as I iterate the second level table.

Is there some way I can stop this? If I could pre-populate the lower levels with all the records I need so it doesn’t fetch them individually as I iterate their parents, that would solve the problem.

2

Hi,

if your tables have mmWhere relation, you can retrieve all required tables in one fetch like

RDA.Fill([table1,...,tableN]);

try

  • disable m/d relation
  • backup DynamicWhere expression
  • set new DynamicWhere expression
  • call table.RefreshFromServer
  • clear new DynamicWhere expression
  • restore saved DynamicWhere expression
  • restore m/d relation

the first and last items can be optional in some cases

3

But what records will be retrieved using the Fill command? Surely it won’t work out what records are required at the third and fourth levels for all parents above?

In case I’m not explaining myself clearly, take an example where you have a three level hierarchy of Company -> Site -> Contact. Each company can have multiple sites and each site can have multiple contacts. I want to retrieve the entire hierarchy for a particular company (or even multiple companies) in one hit.

As there doesn’t seem to be any way to do this automatically, I thought maybe I could work out what records are required at each level myself, then pre-populate the tables manually using my own DynamicWhere expressions, then use the master/detail hierarchy as normal but without the need to fetch further records as they’re already there.

I’ll have a play with your proposed solution later to see if I can make it work.

4

if you have table structures like

master PK:

  • master_ID

detail1 PK:

  • master_ID
  • detail1_ID

detail2 PK:

  • master_ID
  • detail1_ID
  • detail2_ID

you can use for

  • master: no DynamicWhere expression
  • detail1: DynamicWhere expression that retrieves records for master1&master2
  • detail2: the same as for detail1. if some additional filtering is required, it should include some filtering by detail1_ID

all these tables can be retrieved in one call via RDA.Fill([master, detail1, detail2]);

5

Unfortunately I’m not propagating the parent foreign keys down the hierarchy, so detail2 only has detail1_ID, not master_ID.

Maybe I should be but there are obviously downsides to doing so.

6

you could create manual sql for detail2 table like:

select 
   ...
from
  detail2
where 
   ((:param1 = 0) or 
  ((:param1 = 1) and
     // specify here conditions for fetching records from details1 
     // according to master_ID and applying these conditions
  ))

after this, you could set param1 to 1 for special mode processing or 0 for usual one

7

Finally got round to looking at this again.

I like this solution but I can’t make it work. Whilst it retrieves all the records, when I select a different detail record, the sub-details are retrieved again, even though they’re already there.

There must be something internally which flags whether the details for a given master have already been retrieved, as it only ever does it the first time a master is selected. If I could somehow modify or “trick” this mechanism, then the above system would work and I could “pre-fetch” all the sub-details.

8

can you create a simple testcase that has reproduces

I’ll look at this issue

you can attach it here or send directly to support@

9

Yeah I’ll have a go next week.

I’ve found the logic in question - it’s the IsNeedToFetch function in TDADataTable which checks fFetchedMasters. If I could somehow pre-populate this when I fetch all the sub-details, then the table would realise it doesn’t need to fetch them again.

10

as I see, we perform adding of keys into fFetchedMasters automatically if moAllInOneFetch is set

11

Yes but only at the first detail level, as it’s simple to work out which detail records are required for a given master.

What I need is to pre-populate the sub-detail level. I get that this isn’t easy to do automatically as you’d have to find all sub-details for all details for a given master. I don’t mind doing it myself and grabbing all the sub-detail records in one hit, I just need some way to tell it that the records are already there and it doesn’t need to fetch them again, which would require pre-populating fFetchedMasters with all the primary keys at the detail level.

12

try to set detailtable.RemoteFetchEnabled to false after you populated detail table

13

Yes! That appears to work!

Can I still apply updates back to the server with RemoteFetch disabled?

14

I’ve just tried it with a four level hierarchy, let’s call the tables Master, Detail1, Detail2 & Detail3. All the data table components are set up in master/detail relationships via the wizard. All in one fetch is disabled.

What I’m then doing is this:

  1. Set MasterSource on the Detail3 and Detail4 tables to NIL to disable the master/detail link
  2. Set DynamicWhere on Master to select the record I want and then open the table
  3. Detail1 opens automatically and retrieves all details for the selected master
  4. Set DynamicWhere on Detail3 to retrieve all records for all Detail2 parents.
  5. Open Detail3 and set RemoteFetchEnabled to False
  6. Set DynamicWhere on Detail4 to retrieve all records for all Detail3 parents (i.e. all possible Detail3 records for all possible Detail2 records)
  7. Open Detail4 and set RemoteFetchEnabled to False
  8. Set MasterSource on the Detail3 and Detail4 tables back to the correct values to re-enable the master/detail links

At this point everything is functioning normally but, critically, there have only been four round trips to the server, one to fetch all records at each level. I can then navigate through the entire hierarchy and no further fetches are made.

This appears to be as efficient as I can get it. I did think I could enable moAllInOneFetch to retrieve the Master and Detail1 records in a single call but, if I set this value, the SQL generated for Detail1 has no WHERE clause at all and is thus retrieving all records in the table (although only relevant ones would be displayed). I’ve hit this before and it still strikes me as a bug.

I think I’m going to work with this solution some more. I do have to work out the full set of Detail3 and Detail4 records to retrieve myself but, when I’m iterating through the complete hierarchy for a large number of master records, the bottleneck is the number of round trips to the server so I’m hoping minimising these will give me a good performance boost.

Thanks for the help.

15

you can retrieve all tables in one call via

RDA.Fill([table1, table2 ..., tableN ]);
16

You mean set the dynamic where on each one and then Fill them all in one call?

17

yes

18

I’ll give it a go

19

Yeah that works too. There are obviously still four separate SQL SELECTs issued to the database engine but I presume they’re all wrapped up into a single data stream for return to the client.

20

Interestingly, I’ve just tried applying this logic to my original hierarchy and the Fill method is throwing a “Streamer already in use” exception.

I can open the tables individually and it’s fine, but if I try to put them all in a single Fill call, I get the above exception.

Any ideas why this could be?