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?

Answer 1

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.