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:
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)
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.
