Master/detail is retrieving all rows from table

Data Abstract
1

Got an odd one here.

I have a set of TDAMemDataTables configured in a master/detail hierarchy. The main part of this are two tables, one for companies and another for contacts. When I want to load a given company and all its contacts, I configure a dynamic where clause on the company table and then open it, which cascades down the hierarchy, opening all the companies contact records and so on.

Whilst this works, I’ve been having performance issues with a large database, where it’s taking a long time to open a single company with only a few contacts.

I’ve just been checking exactly what SQL is being issued to the server (MSSQL) and have discovered something very odd. The entire contacts table appears to be being read!

The first SQL issued opens the company table and the where clause is there as configured via my dynamic where clause.

The next SQL issued opens the contacts table but there’s no where clause at all so it’s retrieving all rows, which takes a relative age. Despite this, the TDAMemDataTable does only contain the contacts for the company in question.

What’s going on here? Surely with a master/detail hierarchy such as this, DA should be applying the correct where clause to the SQL for the contacts table to only retrieve the required rows from the table. Is this not done automatically and I need to apply the necessary where clause to each table in the hierarchy myself or do I have something set wrong somewhere?

2

Can you please send as a small testcase that reproduces this problem, so we can review it? You can send it to support@remobjects.com

3

I’ll see if I can rig something simple up a bit later today.

4

Ok I’ve just created a simple database and new test application using the wizard and it’s behaving as expected. When I trace the SQL sent to the server I can see that it’s adding the correct where clause to the detail table to retrieve only the required rows.

So the question now is, why isn’t this working in my application? The structure of the tables is the same but in my app no where clause is added to the detail table SQL query. I’ll go through both my app and the test one to check all the various properties and so forth but can you think of any settings which would cause this problem?

5

Aha, it’s the moAllInOneFetch option. If I set this on the master table then the SQL query generated to retrieve the detail table rows contains no where clause. Without it set it does.

EDIT: Yep it’s definitely this option. I’ve just tried it with a three-level hierarchy. When I do this and open a single record at the top level then all relevant detail rows are retrieved. A the third sub-detail level, only the rows for the first detail row are retrieved. When I then move detail records, the sub-detail records for that detail record are fetched. I presume this is by design.

It all works perfectly with the correct WHERE clauses being created and issued to the SQL Server but only if I leave moAllInOneFetch disabled. As soon as I enable this option, the where clauses for the detail tables vanish and it’s retrieving all rows from the database, even though it then only populates the tables with the relevant ones.

Surely this is a bug?

6

Have done some more testing and now I’m thoroughly confused. This is with my three level master/detail/sub-detail hierarchy again.

Firstly, if I leave moAllInOneFetch disabled everywhere, i.e. the default, then I get two round trips to the server. Why not three? Surely with this option disabled, each table data retrieval should be a separate round trip? The detail table has a where clause applied to retrieve all records for the single master I’ve retrieved. The sub-detail table has a where clause applied to retrieve all records for the currently selected detail record. If I move to another detail record then another round trip is performed to retrieve data for that detail. I notice that these sub-detail records do not replace the ones already retrieved but simply add to them, so as I iterate through each detail record, the sub-detail gradually fills up with all relevant records and I can return to a previously selected detail record without retrieving its sub-detail again.

I guess the above makes sense but sometimes it’d be useful to retrieve all the sub-detail rows in one go, as if I know I’ll be iterating through all the detail rows anyway, it would negate the need for separate trips to the server each time. I suppose the difficulty here is that it’d have to generate a where clause on the sub-detail which included every parent detail ID.

Secondly, if I enable moAllInOneFetch on the master table only, I get a single round trip to the server but this only retrieves the master and detail tables, no sub-detail is retrieved at all. The SQL for the detail table lacks any where clause so all rows are being retrieved from the database, although only the relevant ones end up in the table.

Lastly, if I enable moAllInOneFetch on both the master and detail tables then I get a single round trip to the server which retrieves all three tables. Again there are no where clauses on the detail or sub-detail SQL queries so everything is retrieved. In this case, as all detail and sub-detail rows have been retrieved, iterating through the detail rows doesn’t cause any more trips to the server as it obviously has the data already.

What I can deduce from all this is…

It seems that moAllInOneFetch needs to be the same on both the master and detail tables. If I set it on just the master then it breaks and no sub-detail is ever retrieved.

I can’t work out why, when I leave moAllInOneFetch disabled everywhere, I only get two round trips to the server instead of three, one for each table.

I seems that I have a choice between two extremes. Either I leave moAllInOneFetch disabled, in which case the where clauses are generated and only the necessary rows are returned, but then iterating through the detail rows creates a new round trip to the server each time to get the associated sub-detail. The other extreme is to enable moAllInOneFetch, which retrieves everything in a single round trip but retrieves every detail and sub-detail row from the database.

What I want is a middle-ground. I want to get everything in a single round trip to the server but only the necessary rows, i.e. all details for the master and all sub-details for all details for the master. Is this possible in any way? The only possible way I can see of doing this is to leave moAllInOneFetch enabled everywhere then generate my own where clauses on the detail and sub-detail. The former is easy but I can only do the latter if I propagate the master foreign key from the detail table down to the sub-detail so I can do something like “where sub-detail.masterid = X”.

If you could please shed some light on this for me I’d appreciate it as I’m rather confused.

Thanks

7

Hello!

Sorry for the delay, most of DA for Delphi team is on vacation. We’re still investigating the issue.

8

Ok no problem :slight_smile:

9

Hello, sorry for the delay.
Unfortunately, I couldn’t make it work exactly the way you want, so we’ll have to wait for more experienced guys to come back from the vacation.

10

Ok that’s fine, thanks for looking anyway :slight_smile:

11

All in one fetch mode is designed for retrieving static data for master/detail tables data in one fetch.

it is very similar to usual DataAdapter.Fill method:

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

however this feature can be also used for loading of dynamically changed data with table.RefreshFromServer:

  table.DynamicWhere.Expression := ...;
  table.RefreshFromServer;

or table.RefreshRow method:

  table.RefreshRow;

in this case, the newest records will be added into existing table or current record will be refreshed.

usually, you shouldn’t set dynamic where expressions during table opening with moAllinOneFetch feature, but some hardcore expressions are allowed if they are set in DASM or via custom SQL.

12

Ok, so in my master/detail/sub-detail scenario, I’m better off not using the all in one fetch system? This would result in multiple round trips to the server to retrieve the different levels and then subsequent round trips to fetch the sub-detail rows as I iterate through the detail rows, appending them to previously retrieved sub-details?

The only alternative is to use all in one fetch but, as this will retrieve all rows from the database, there’d be a massive performance hit if the tables were very large.

13

all in one fetch feature allows to retrieve all rows for all tables in 1st call so no new calls will be made to server at all.

without this feature, it will made server calls for each master record if correspondent details aren’t received yet. by other words, each detail table caches master keys for which records were received and doesn’t receive duplicates records. you can disable m/d feature for detail table and review which records were received.

I can attach an example of this feature for master/detail/subdetail case: it makes only one server call at opening and doesn’t make others at iterating master/detail tables.

of course, you can use all in one fetch feature for different levels, i.e.

  • master/detail and not subdetail
  • detail/subdetail and not master
  • etc
14

An example would be useful if you could please :smile:

Regards mixing the all in one fetch option for master and detail tables, this didn’t work at all for me, as I mentioned previously. Not sure if I was doing something wrong here.

All I’m trying to achieve is the fewest number of round-trips to the server in order to improve performance. With all in one fetch enabled throughout the hierarchy, all detail and sub-detail rows are being retrieved from the database, regardless of which master record is selected, which is obviously undesirable as the performance hit could be massive.

15

example

16

Thanks, I’ll have a look when I get a moment :slight_smile:

17

Only just got round to looking at this. Sorry but I can’t run the example as I’m using Delphi Pro which doesn’t have Firebird drivers.

18

You can replace Firebird with Interbase driver and database

19

Ah ok, not sure I’ve got the Interbase stuff installed but will have a look.