Query datasource FetchMode property

FetchMode property sometimes was a magic solution for report development in Axapta 3.0. Since then I wanted to run several tests and describe the feature.

Problem description
Analyse how a query is translated into SQL Server query based on FetchMode property.

Solution
Based on the description in msdn FetchMode property is available on an embedded datasource and determines a relation between parent and child datasources.
FetchMode values are 1:1 and 1:n, but there is no description of them.
The property name and values can lead to an assumption:
1:1 - data for parent and child datasources is fetched simultaneously
1:n - data for parent and child datasources is fetched separately.
Running ahead I must say that the assumption is not precisely correct, but let's analyse it. You can also jump to the conclusion section.


Case 1. FetchMode set to 1:1
All embedded datasources of CustCollectionLetterOverview query have FetchMode set to 1:1:

The query is translated into SQL Server query (hereafter some parts are omitted for simplicity):

SELECT ... FROM CUSTCOLLECTIONLETTERJOUR T1 CROSS JOIN CUSTCOLLECTIONLETTERTRANS T2 CROSS JOIN CUSTTABLE T3 CROSS JOIN CUSTTRANS T4 CROSS JOIN DIRPARTYTABLE T5 ...

Update. How is data fetched in Dynamics AX?
There is always one record in customer table (CustTable) and correspondingly one record in party table (DirPartyTable) for each customer collection letter journal (CustCollectionLetterJour). So it is logical to fetch journal, customer and party records together.
A journal can have several lines (CustCollectionLetterTrans) and respectively several customer transactions (CustTrans). There is such journal 000006 with 3 lines in standard demo data in USMF company. How will the query run in such case?
Let's run the following job and add breakpoint at the line containing total++:

static void FetchModeTest1(Args _args) { Query q; QueryRun qr; QueryBuildDataSource qbds; QueryBuildRange qbr; CustCollectionLetterJour custCollectionLetterJour; CustCollectionLetterTrans custCollectionLetterTrans; CustTable custTable; CustTrans custTrans; DirPartyTable dirPartyTable; int total; q = new Query(queryStr(CustCollectionLetterOverview)); qbds = q.dataSourceTable(tableNum(CustCollectionLetterJour)); qbr = qbds.findRange(fieldNum(CustCollectionLetterJour, CollectionLetterNum)); qbr.value(queryValue("000006")); qr = new QueryRun(q); while (qr.next()) { custCollectionLetterJour = qr.get(tableNum(CustCollectionLetterJour)); custCollectionLetterTrans = qr.get(tableNum(CustCollectionLetterTrans)); custTable = qr.get(tableNum(CustTable)); custTrans = qr.get(tableNum(CustTrans)); dirPartyTable = qr.get(tableNum(DirPartyTable)); total++; } }

After the first iteration (next method call) every table buffer gets corresponding record:

After the second iteration only CustCollectionLetterTrans and CustTrans table buffers are updated, other table buffers are not changed:

The same happens after the third iteration.
It may seem that in Dynamics AX transaction records (CustCollectionLetterTrans and CustTrans) are fetched separately, however according to SQL Server query all records are selected from database simultaneously.
Note: QueryRun.changed method is used to determine whether datasource has new value and is described on msdn.

Case 2. FetchMode set to 1:n
All embedded datasources of CustInterest query have FetchMode set to 1:n:

The query is translated into 3 SQL Server queries.
SQL Server query 1:

SELECT ... FROM CUSTTABLE T1 ...

SQL Server query  2:

SELECT ... FROM CUSTTRANSOPEN T1 WHERE ... ACCOUNTNUM=@P3 ...

SQL Server query  3:

SELECT ... FROM CUSTSETTLEMENT T1 WHERE ... ACCOUNTNUM=@P3 ...

So far the assumption is correct.
Note: There is logical error in the query - actual join mode on CustTransOpen and CustSettlement datasources is OuterJoin due to FetchMode specifics.

Update. How is data fetched in Dynamics AX?
Based on SQL Server queries, for each customer (CustTable) 2 separate queries are run to get open transactions (CustTransOpen) and settlements (CustSettlement). Customer DE-001 in standard demo data in USMF company has settlements and open transaction.
Let's run the following job and add breakpoint at the line containing total++:

static void FetchModeTest2(Args _args) { Query q; QueryRun qr; QueryBuildDataSource qbds; QueryBuildRange qbr; CustTable custTable; CustTransOpen custTransOpen; CustSettlement custSettlement; int total; q = new Query(queryStr(CustInterest)); qbds = q.dataSourceTable(tableNum(CustTable)); qbr = qbds.findRange(fieldNum(CustTable, AccountNum)); qbr.value(queryValue("DE-001")); qr = new QueryRun(q); while (qr.next()) { custTable = qr.get(tableNum(CustTable)); custTransOpen = qr.get(tableNum(CustTransOpen)); custSettlement = qr.get(tableNum(CustSettlement)); total++; } }

After the first iteration (next method call) only CustTable table buffer gets corresponding record:

After the second iteration CustTable table buffer remains the same, but CustTransOpen table buffer gets corresponding record:

After the third iteration CustTable table buffer remains the same, but CustTransOpen table buffer is empty (there is only 1 open transaction for the customer) and CustSettlement table buffer gets corresponding record:

After that only CustSettlement table buffer is updated with new values.
It is not obvious how records are actually fetched in Dynamics AX, but based on SQL Server queries data is selected from database separately: there is 1 loop on customer records and for each found customer 2 separate selects are run to get open transaction (CustTransOpen) and settlement (CustSettlement) records.

Case 3. FetchMode set to 1:n and 1:1
Embedded datasources of AssetAcquisition query have different FetchMode values:

The query is translated into SQL Server query:

SELECT ... FROM ASSETTABLE T1 CROSS JOIN ASSETBOOKMERGE T2 LEFT OUTER JOIN VENDTABLE T3 ... LEFT OUTER JOIN DIRPARTYTABLE T4 ...

Based on the assumption there should be 2 SQL Server queries - one for AssetTable and one for AssetBookMerge with child datasources.

Update. How is data fetched in Dynamics AX?
Let's run the following job and add breakpoint at the line containing total++:

static void FetchModeTest3(Args _args) { Query q; QueryRun qr; QueryBuildDataSource qbds; QueryBuildRange qbr; AssetTable assetTable; AssetBookMerge assetBookMerge; VendTable vendTable; DirPartyTable dirPartyTable; int total; q = new Query(queryStr(AssetAcquisition)); qbds = q.dataSourceTable(tableNum(AssetTable)); qbr = qbds.findRange(fieldNum(AssetTable, AssetId)); qbr.value("COMP-000004, COMP-000005"); qr = new QueryRun(q); while (qr.next()) { assetTable = qr.get(tableNum(assetTable)); assetBookMerge = qr.get(tableNum(assetBookMerge)); vendTable = qr.get(tableNum(vendTable)); dirPartyTable = qr.get(tableNum(dirPartyTable)); total++; } }

Fixed asset books of COMP-000004 asset have no related vendor,  so after the first iteration only AssetTable and AssetBookMerge table buffers get records:

After the second and further iterations for COMP-000004 asset only AssetBookMerge table buffer is updated:

Fixed asset books of COMP-000005 asset have related vendor, so after the first iteration all table buffers get records:

After further iterations only AssetBookMerge table buffer is updated:

Here it is important to use QueryRun.changed method carefully. Even though VendTable and DirPartyTable table buffers are not changed, the records are related to AssetBookMerge table buffer and it is changed.

Case 4. FetchMode set to 1:n
All embedded datasources of CustInterestCreate query also have FetchMode set to 1:n:

The query is translated into 3 SQL Server queries:
SQL Server query 1:

SELECT ... FROM CUSTTABLE T1 CROSS JOIN CUSTTRANS T2 ...

SQL Server query 2:

SELECT ... FROM CUSTSETTLEMENT T1 WHERE ... (((TRANSCOMPANY=@P3) AND (TRANSRECID=@P4)) AND (ACCOUNTNUM=@P5) ...

SQL Server query 3:

SELECT ... FROM CUSTTRANSOPEN T1 WHERE ... (ACCOUNTNUM=@P3) AND (REFRECID=@P4) ...

So the query is split into several SQL Server queries provided there are multiple embedded datasources with FetchMode set to 1:n on the same level.
Note: the same logical error - actual join mode on CustSettlement and CustTransOpen datasources is OuterJoin.

Update. How is data fetched in Dynamics AX?
Test job is almost the same as for case 2:

static void FetchModeTest4(Args _args) { Query q; QueryRun qr; QueryBuildDataSource qbds; QueryBuildRange qbr; CustTable custTable; CustTrans custTrans; CustTransOpen custTransOpen; CustSettlement custSettlement; int total; q = new Query(queryStr(CustInterest)); qbds = q.dataSourceTable(tableNum(CustTable)); qbr = qbds.findRange(fieldNum(CustTable, AccountNum)); qbr.value(queryValue("DE-001")); qr = new QueryRun(q); while (qr.next()) { custTable = qr.get(tableNum(CustTable)); custTrans = qr.get(tableNum(CustTrans)); custTransOpen = qr.get(tableNum(CustTransOpen)); custSettlement = qr.get(tableNum(CustSettlement)); total++; } }

After the first iteration CustTable and CustTrans table buffers get records:

After the second iteration CustSettlement table buffer gets record:

After the third iteration CustSettlement table buffer is empty, but CustTrans table buffer gets new value:

After the fourth iteration CustSettlement table buffer gets new value:

After several further iterations CustTrans table buffer gets value which is an open transaction:

After the last iteration CustTransOpen table buffer gets record:

There is 1 loop on customer transaction records and for each found customer transaction 2 separate selects are run to get settlement (CustSettlement) and open transaction (CustTransOpen) records.

Case 5. FetchMode set to 1:n and 1:1
I created CustGroupInfo query with 2 embedded datasources on the same level, but set different FetchMode:

The query is translated into 2 SQL Server queries:
SQL Server query 1:

SELECT ... FROM CUSTGROUP T1 LEFT OUTER JOIN PAYMTERM T2 ...

SQL Server query 2:

SELECT ... FROM CUSTTABLE T1 WHERE ... CUSTGROUP=@P3 ...

Conclusion
Dynamics AX query is split into multiple SQL Server queries provided there are multiple embedded datasources on the same level and at least one of them has FetchMode set to 1:n. Otherwise AX query is translated into one SQL Server query.
Combination of JoinMode set to InnerJoin and FetchMode set to 1:n can lead to logical error (see Case 2 and Case 4).