QueryFetchMode和JoinMode的组合

Article
08/22/2011

AX使用Query Framework做查询时候当涉及到多个QueryDataSource时候需要为child QueryDataSource指定2个参数: JoinMode和FetchMode. 具体的使用相关的文档对FetchMode语焉不详。

如下示例:

QueryDataSource dsParentMaster;
QueryDataSource dsChildDetails;
Query query;
query=new Query();
dsParentMaster=query.addDataSource(tablenum(XXX));
dsChildDetails= dsParentMaster.addDataSource(tableNum(YYYY));
dsChildDetails.addRelation(RRRR);
dsChildDetails.joinMode(JoinMode::InnerJoin);
dsChildDetails.fetchMode(QueryFetchMode::One2One);

1. 关联模式 joinMode = Enum JoinMode: (0=InnerJoin, 1=OuterJoin, 2= ExistsJoin, 3=NoExistsJoin)
   这个系统文档都有说明。我发现用主表和明细表易于理解和记忆:
   InnerJoin ---- 结果集包含master主表和detail明细表有关联的数据行都会被取回,取回的每一条master主表行都有关联的非空detail明细表数据行关联。
   OuterJoin ---- 结果集包含master主表所有行和关联的detail明细表行都会被取回 (没有关联的明细表行不会被取回),取回的每一条master主表行不保证都有detail明细表数据行关联 (没有关联的以null代替)
   ExistsJoin ---- 结果集只有master主表数据行会被取回，取回的每一条master主表行都有非空detail明细表数据行关联。相当于对主表master进行过滤，同时明细表不取回提高了性能。
   NoExistsJoin -- 结果集只有master主表数据行会被取回，取回的每一条master主表行都没有非空detail明细表数据行关联。相当于对主表master进行过滤，同时明细表不取回提高了性能。

2. fetchMode = Enum QueryFetchMode (0=One2One, 1= One2Many)

这个公开文档说明很少，主要是控制AX Kernel内部生成SQL server 的SQL 语句时候的映射算法的。对于复杂的Query比如有多个QueryDataSource构成一个复杂的Query Tree时候，One2One保证系统生成1个SQL Join语句，而One2Many AX Kernel则把DataSource分成多个blocks, 生成多个SQL语句。

fetchMode使用基本指导原则是:
使用AIF document service时候InnerJoin+One2One, OuterJoin+One2Many; 其它情况FetchMode尽量都使用One2One.
你可以在AX客户端打开SQL Trace来看到fetchMode的变化带来的映射SQL的区别: Tools -> Options -> SQL -> SQL Trace。

看个具体实例 (本实例数据结果来源于AX2012):

打开SQL Trace以后可以看到InnerJoin先被映射为1个SQL语句, 生成的结果集再和OuterJoin做映射匹配 (本试验中会有4条记录被匹配故系统生成了额外4条SQL语句)。

SQL statement: (GeneralJournalAccountEntry,GeneralJournalEntry) SELECT T1.TRANSACTIONCURRENCYAMOUNT,T1.ACCOUNTINGCURRENCYAMOUNT,T1.REPORTINGCURRENCYAMOUNT,T1.QUANTITY,T1.ALLOCATIONLEVEL,T1.ISCORRECTION,T1.ISCREDIT,T1.TRANSACTIONCURRENCYCODE,T1.PAYMENTREFERENCE,T1.POSTINGTYPE,T1.LEDGERDIMENSION,T1.GENERALJOURNALENTRY,T1.TEXT,T1.REASONREF,T1.PROJID_SA,T1.PROJTABLEDATAAREAID,T1.CREATEDTRANSACTIONID,T1.RECVERSION,T1.RECID,T2.ACCOUNTINGDATE,T2.LEDGERENTRYJOURNAL,T2.ACKNOWLEDGEMENTDATE,T2.LEDGERPOSTINGJOURNAL,T2.FISCALCALENDARPERIOD,T2.POSTINGLAYER,T2.LEDGER,T2.LEDGERPOSTINGJOURNALDATAAREAID,T2.JOURNALNUMBER,T2.TRANSFERID,T2.BUDGETSOURCELEDGERENTRYPOSTED,T2.FISCALCALENDARYEAR,T2.DOCUMENTDATE,T2.DOCUMENTNUMBER,T2.JOURNALCATEGORY,T2.CREATEDDATETIME,T2.CREATEDBY,T2.CREATEDTRANSACTIONID,T2.RECVERSION,T2.RECID FROM GENERALJOURNALACCOUNTENTRY T1 CROSS JOIN GENERALJOURNALENTRY T2 WHERE ((T2.JOURNALNUMBER=?) AND (T1.GENERALJOURNALENTRY=T2.RECID)) ORDER BY T1.GENERALJOURNALENTRY,T1.LEDGERDIMENSION [ID=245, Reused=Yes]

SQL statement: (DimensionAttributeLevelValueView) SELECT T1.VALUECOMBINATIONRECID,T1.DISPLAYVALUE,T1.DIMENSIONATTRIBUTE,T1.ATTRIBUTEVALUERECID,T1.ENTITYINSTANCE,T1.ATTRIBUTEVALUEHASHKEY,T1.MAXOFRECID,T1.RECID FROM DIMENSIONATTRIBUTELEVELVALUEVIEW T1 WHERE ((DIMENSIONATTRIBUTE=?) AND (VALUECOMBINATIONRECID=?)) ORDER BY T1.RECID [ID=36, Reused=No]

上图中如果我们把所有的fetchMode都改为1:1那么对应的SQL语句将变为仅有1条:

SQL statement: (GeneralJournalAccountEntry,GeneralJournalEntry,DimensionAttributeLevelValueView) SELECT T1.TRANSACTIONCURRENCYAMOUNT,T1.ACCOUNTINGCURRENCYAMOUNT,T1.REPORTINGCURRENCYAMOUNT,T1.QUANTITY,T1.ALLOCATIONLEVEL,T1.ISCORRECTION,T1.ISCREDIT,T1.TRANSACTIONCURRENCYCODE,T1.PAYMENTREFERENCE,T1.POSTINGTYPE,T1.LEDGERDIMENSION,T1.GENERALJOURNALENTRY,T1.TEXT,T1.REASONREF,T1.PROJID_SA,T1.PROJTABLEDATAAREAID,T1.CREATEDTRANSACTIONID,T1.RECVERSION,T1.RECID,T2.ACCOUNTINGDATE,T2.LEDGERENTRYJOURNAL,T2.ACKNOWLEDGEMENTDATE,T2.LEDGERPOSTINGJOURNAL,T2.FISCALCALENDARPERIOD,T2.POSTINGLAYER,T2.LEDGER,T2.LEDGERPOSTINGJOURNALDATAAREAID,T2.JOURNALNUMBER,T2.TRANSFERID,T2.BUDGETSOURCELEDGERENTRYPOSTED,T2.FISCALCALENDARYEAR,T2.DOCUMENTDATE,T2.DOCUMENTNUMBER,T2.JOURNALCATEGORY,T2.CREATEDDATETIME,T2.CREATEDBY,T2.CREATEDTRANSACTIONID,T2.RECVERSION,T2.RECID,T3.VALUECOMBINATIONRECID,T3.DISPLAYVALUE,T3.DIMENSIONATTRIBUTE,T3.ATTRIBUTEVALUERECID,T3.ENTITYINSTANCE,T3.ATTRIBUTEVALUEHASHKEY,T3.MAXOFRECID,T3.RECID FROM GENERALJOURNALACCOUNTENTRY T1 CROSS JOIN GENERALJOURNALENTRY T2 LEFT OUTER JOIN DIMENSIONATTRIBUTELEVELVALUEVIEW T3 ON (((T3.DIMENSIONATTRIBUTE=?) AND (T3.DISPLAYVALUE=?)) AND (T1.LEDGERDIMENSION=T3.VALUECOMBINATIONRECID)) WHERE ((T2.JOURNALNUMBER=?) AND (T1.GENERALJOURNALENTRY=T2.RECID)) ORDER BY T1.GENERALJOURNALENTRY,T1.LEDGERDIMENSION [ID=28963, Reused=No]

thanks
--Clifford Zhang

Share via

QueryFetchMode和JoinMode的组合

Additional resources