SysDa – a new X++ query API

One of the major problems we faced when we embarked on the extensibility journey, was that X++ select statements are not extensible. If someone wants to add a new range, a join, a field in the field list – it is not possible. So far our strategy has been to convert these statements to good-old Queries. This works fine in most cases, but there are short comings:

1. Queries are somewhat slower

   Even when the resulting TSQL is the same, queries need more compute to prepare the TSQL. The execution time in SQL server is of course the same (which often overshadows the overhead).
   This is the reason we avoid converting select statements to queries in performance critical paths.

    

2. Queries have a limited set of capabilities.
   For example; delete_from is not supported. Update_recordset and insert_recordset are partly supported – via some arcane static methods on SysQuery.

Welcome the new member of the family: SysDa

"Da" is short for Data access. It is a set of new APIs exposing the object graph that the compiler otherwise produces from select statements. As such it has full fidelity – everything you can do in select statements (including delete_from, insert_recordset and update_recordset) is possible.

…and it has the same performance characteristics as select statements.

…and it is extensible.

…and it is available in PU22.

…and here are some examples:

This enables a lot of desired abilities – where the only option previously was macros, such as reuse of field lists, where clauses, joins, etc. For example, we are now considering replacing the InventSum* macros with an extensible alternative.

THIS POST IS PROVIDED AS-IS AND CONFERS NO RIGHTS.