Dynamics AX Hints: Dynamic

While analysing performance issues I faced non-documented feature of Dynamic property on query datasource Fields. I made simplified and theoretical example to demonstrate problem and solution.

Problem description
More information than expected is returned form database when Fields node on query datasource has no field and Dynamic property is set to No.

Steps to reproduce
1. Create a new query in AOT like on screenshot below:

Query with no field in Fields and Dynamic property set to No

2. Create a test job to get query statement in AX and to invoke SQL statement

static void CustTableBankAccountCountry(Args _args)
{
    Query       q = new Query(queryStr(CustTableBankAccountCountry));
    QueryRun    qr;
    
    info (q.dataSourceNo(1).toString());
    
    qr = new QueryRun(q);
    qr.next();
}

3. Setup SQL Server Profiler to catch test statement
Note: for test it is enough to trace only SP:SmtCompleted event and filter results by CustTable (I plan to write a separate post on SQL Server Profiler hints for Dynamics AX tracing)

SQL Server Profiler setup for Dynamics AX

4. Run test job

5. Compare query statement in AX with actual statement in SQL
The following statement is built in AX:

SELECT AccountNum FROM CustTable(CustTable) 
OUTER JOIN  FROM CustBankAccount(CustBankAccount) 
ON CustTable.AccountNum = CustBankAccount.CustAccount 
OUTER JOIN CountryRegionId FROM LogisticsPostalAddress(LogisticsPostalAddress) 
ON CustBankAccount.Location = LogisticsPostalAddress.Location

The following statement is traced in SQL:

SELECT T1.ACCOUNTNUM,T1.RECID,
T2.ACCOUNTID,T2.CUSTDOMNUMBER_BE,T2.NAME,T2.ACCOUNTNUM,T2.BANKCIN,
T2.BANKACCOUNTTYPE,T2.LOCATION,T2.CURRENCYCODE,T2.REGISTRATIONNUM,
T2.PHONE,T2.TELEFAX,T2.CONTACTPERSON,T2.EMAIL,T2.URL,T2.TELEX,
T2.CELLULARPHONE,T2.PHONELOCAL,T2.CUSTACCOUNT,T2.BANKGROUPID,
T2.CUSTPAYMENTTEXTCODE,T2.SWIFTNO,T2.MSGTOBANK,T2.EXCHRATE,
T2.EXCHRATEREF,T2.BANKCONTRACTACCOUNT,T2.PAGER,T2.SMS,T2.BANKCODETYPE,
T2.BANKIBAN,T2.CORRESPONDENTBANKNAME_LT,T2.CORRESPONDENTBANKADDRESS_LT,
T2.CORRESPONDENTBANKSWIFT_LT,T2.INTERMEDIATEBANKNAME_LT,
T2.INTERMEDIATEBANKADDRESS_LT,T2.INTERMEDIATEBANKSWIFT_LT,
T2.CORRESPONDENTBANKACCOUNT_LT,T2.INTERMEDIATEBANKACCOUNT_LT,
T2.FOREIGNBANK_RU,T2.FOREIGNACCOUNT_RU,T2.FOREIGNSWIFT_RU,
T2.BANKSORTCODE_RU,T2.CORRACCOUNT_W,T2.LVDEFAULTBANK,T2.BANKCONSTANTSYMBOL,
T2.SPECIFICSYMBOL,T2.RECVERSION,T2.PARTITION,T2.RECID,
T3.COUNTRYREGIONID,T3.RECID 
FROM CUSTTABLE T1 
LEFT OUTER JOIN CUSTBANKACCOUNT T2 
ON (((T2.PARTITION=@P1) AND (T2.DATAAREAID=@P2)) AND (T1.ACCOUNTNUM=T2.CUSTACCOUNT)) 
LEFT OUTER JOIN LOGISTICSPOSTALADDRESS T3 
ON ((T3.PARTITION=@P3) AND ((T2.LOCATION=T3.LOCATION) 
AND ((T3.VALIDFROM<=@P4) AND (T3.VALIDTO>=@P5)))) 
WHERE ((T1.PARTITION=@P6) AND (T1.DATAAREAID=@P7)) 
ORDER BY T1.ACCOUNTNUM

No field is expected to be selected from CustBankAccount table based on query definition. Query statement in AX proves it, but actual SQL statement selects and returns all fields from CustBankAccount. As no field is actually required from CustBankAccount table, such useless select should be omitted.

Hints
Try to add any field into Fields node.

Dynamics AX Hints

16 March 2015

Non-documented feature of Dynamic property on query datasource Fields