What is the difference between the use of AOT query and X++ select statement

Question

In AX programming best practice, which is the best way:

• using a Query created from the AOT,
• using a select statement with X++ code,
• using a query created with X++ code the Query classe ...

And when to use each one of them?

Talca

Answer 1

The main techniques for selecting records in the database are as follows:

• The select statement
• A query

The techniques are essentially the same. They both deliver a set of records from the database in a table variable that can be accessed.

Use the select statement when:

• The selection criteria are complex.
• You are selecting a set of records from X++. The user is not going to change the selection criteria.

Use a query when:

• The user can choose which records are selected.
• The user can change the range of records to be selected.
• The selection criteria are not more complex than the query can accommodate.
• When you use queries, develop them in the Application Object Tree (AOT) or build them from scratch in your code. In both situations, the query can be modified in the code. A query built from scratch in code can be saved so that it occurs in the AOT. However, this should usually be avoided.

Build a query in the AOT when:

• A specific query definition is being used in many places. (The query in the AOT can be reused.)
• The query must contain code.
• The query definition is more complex. The AOT provides a visual representation of the query.

Doerr

Answer 2

First off, AX always uses queries internally, X++ selects are translated to query constructions calls, which are executed at run time. The query is translated to SQL at runtime on the the first queryRun.next() or datasource.executeQuery(). There is thus no performance difference using one or the other.

Forms also use queries, most often it is automatically constructed for you, because property AutoQuery has a Yes default value. You can use a X++ select in the executeQuery method, but I would consider that bad practice, as the user will have no filter or sorting options available. Always use queries in forms, prefer to use the auto queries. Add ranges or sorting in the init method using this.queryBuildDatasource() if needed. The exception being listpages which always use an AOT query.

In RunBase classes prefer to use queries, as the user will have the option to change the query. You may of cause use simple X++ select in the inner loop, but consider to include it in the prebuilt query, if possible.

Otherwise, your primary goal as a programmer (besides solving the problem) is to minimize the number of code lines.

Queries defined in the AOT start out with zero code lines, which count in their favor. Thus, if there are serveral statically defined ranges, links, or complex joins, use AOT queries. You cannot beat:

QueryRun qr = new QueryRun(queryStr(MyQuery))
qr.query().dataSourceTable(tableNum(MyTable)).findRange(fieldNum(MyTable,MyField)).value('myValue');

With:

Query q = new Query();
QueryRun qr = new QueryRun(q);
QueryBuildDataSource ds = q.addDataSource(tableNum(MyTable));
QueryBuildRange qbr = ds.addRange(fieldNum(MyTable,MyField));
qbr.value('myValue');
qbr.locked(true);

Thus in the static case prefer to use AOT queries, then change the query at runtime if needed. On the flip side, if your table is only known at runtime, you cannot use AOT queries, nor X++ selects, and you will need to build your query at runtime. The table browser is a good example of that.

What is left for X++?

• Simple selects with small where clauses and with simple or no joins.
• Cases where you cannot use queries (yet), delete_from, update_recordset and insert_recordset comes to mind.
• Avoiding external dependencies (like AOT queries) may sometimes be more important.
• Code readability of X++ queries is better than query construction.

Titmouse

Great answer. When you say X++ selects are translated to Query construction calls, how do you know this? – Hecker

By looking at the CIL generated XML. Actually it is not calls, but specific op-codes. Example BankCheckLayout.find method (line 15): <Var offset='184' index='3' defaultParamOffset='0'/> <Find offset='187' dataset='9'/> <FirstOnly offset='192'/> <Where offset='193'/> <Var offset='194' index='0' defaultParamOffset='0'/> <Expr offset='197'> <SymWhere offset='198' opcode='18'> ... – Titmouse

Queries should also be used when you need expressions in query ranges – Commemoration

Thanks Jan B.Kjelsen, that's a very generous anser. – Talca