Select statement on field

A field select is a special select statement in X++. The description is available on msdn, but what is actually executed on SQL Server? Can the performance be improved? Let's analyse it.

Hints
Run field select statement on several tables and track actual T-SQL statements in SQL Server Profiler. You can also jump to the conclusion section.

Query datasource FirstOnly property

How does query datasource FirstOnly property work and influence SQL Server query? It seems obvious, but in fact it is not.

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

Solution
Based on the description in msdn FirstOnly property is a hint for database that only one record is required. Let's run several tests to analyse how it actually works. You can also jump to the conclusion section.

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.

How unique index influences T-SQL statement

As it was mentioned in the previous post, T-SQL statement can contain additional fields which are not selected in X++ statement. Let's analyse on InventBatch table what exactly is added and why.

Problem description
Analyse how an unique index influences T-SQL statement.

Steps to reproduce
Run a test job:

static void Test_UniqueIndex_SQL(Args _args) { InventBatch inventBatch; select generateonly description from inventBatch; info (inventBatch.getSQLStatement()); }

Hints
Change index properties on the table and re-run test job.

How joins in X++ select statement are translated into T-SQL

It is not always obvious what request is actually executed on SQL Server. The most confusing is probably exists join in X++. Let's analyze how joins in select statement in X++ are translated into T-SQL statement sent to SQL Server.

Non-documented feature of Dynamic property on query datasource Fields

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.