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.



Comments (20)

  1. MAAKC says:

    Funny facts about bulk update:
    Brand new table has been created for testing:
    – Name: UpdateTestTable
    – Fields: Id:GUID, Status:Int, Memo1:Memo, Memo2:Memo, Memo3:Memo
    – Memo fields were added to show the value of using fields list (two row-by-row methods were tested)
    – Unique index on Id field, SurrogateKey as Primary index and Cluster index
    – Cross company table
    – No methods/handlers defined

    Initiated table table data with the following SQL script (64K of random records):

    We tested the following scenario: UPDATE UpdateTestTable SET STATUS = XX
    All executions were performed with the following parameters:
    skipDataMethods = true;
    skipEnvents = true;
    skipDatabaseLog = true;

    SQL execution time: 1989ms
    update_recordset: 3294ms
    row-by-row update: 120444ms (while select forUpdate UpdateTestTable)
    row-by-row fieldlist update: 29572ms (while select forUpdate Status from UpdateTestTable)
    Query::queryUpdateRecordset: 100763ms
    SysDaUpdateStatement: 2214ms
    For sure, elapsed time may vary, however overall ratio will look similar to the above results.

    Query::queryUpdateRecordset – falls to row-row update (tested on PU23 & PU24)
    update_recordset is 65% slower than direct SQL for simple updates
    SysDaUpdateStatement: extremely fast technique, almost the same time as direct SQL

    P.S. SysDaUpdateStatement does not support joins in PU23 (added in PU24)

  2. Thanks for the blog post.
    But how do i bind several components in the where clause if i have to? In select statements i could use paranthesis like “where (fieldA == valueA AND fieldB == valueB) OR (fieldC == valueC AND fieldB == valueD). I found the SysDaOrExpression and SysDaAndExpression, but no way to substitute the paranthesis.
    Best regards, Karsten!

    1. Hi Karsten,
      Look at this like the compiler would:
      A && (B || C) is really just: AndExpression(A, OrExpression(B, C))

      That way you can build any logical expression using SysDa.

      You example would be:
      qe.WhereClause(new SysDaOrExpression(new SysDaAndExpression(…), new SysDaAndExpression(…));

      Or with PU23, simply:
      qe.WhereClause(new SysDaAndExpression(…)).or(new SysDaAndExpression(…));

  3. To echo the others, I like where this is going, but it would be really great if it was more readable, less lines of code.

    Curious to know how this would have to be exposed for extensibility. I see these options:
    1) Create a protected member for every SysDaQueryObject. (So other developers can extend on it)
    2) Expose only the SysDaSearchObject. (Better than the previous case, but then I assume we have something like SysDaSeachObject.getSysQueryObject(tableNum(SalesPool)) or alike)
    3) A much cooler way that I am missing? 🙂

    Thanks for sharing Michael, many exciting things happening these days!

    1. A more complicated select statement is builded from join clauses also, which means you have to instantiate SysDaQueryObject for every joined table. So exposing every SysDaQueryObject instance creation is the proper way for extensibility. If you expose only the Find are Search object, the consumers can’t access the joined query objects, and they won’t be able to add custom where statements to it.

      1. Correcting my previous sentece, maybe they can access the joined query object, but not in the nicest way, have to test it. 🙂

      2. Yes, that is what I meant with my second option, that you pass a tableId and get the resultant SysDaQueryObject back. Ideally you would want to be able to go through the query, since the same table may be joined multiple times…

      3. You can’t get the required query object from Find or Search objects based on tableId. You can get the query with query(), and you can get its joined query with joinClause(), and also its joined one with joinClause() and so on, one by one. Like ‘decrypting’ the query. So that’s why it’s better to expose every SysDaQueryObject instance creation and allow consumers extending it properly. It also means them less dependecy from MS.

  4. trud says:

    I don’t get the first example. How do you then loop custTable – Does something like “next custTable;” still supported?

    1. The two examples show a select and while select statement – or in SysDa-lingo: Find and Search. When you use the approach in the first example, you can only get one record. If you need multiple records, you should use the SysDaSearchStatement class.

  5. Norm-C says:

    Hello guys, I just decided to start posting but unfortunately in this case is not for good. If this is the future of X++ we’re going on the wrong direction. Instead of making it easier and simpler to understand, SysDa it’s just a bad remake of the Query library. Not to offend but c# + Linq + Lambda are a beautiful solution, though I’d wish to have something like Dataweave too incorporated, they are doing a great job on data manipulation. Can’t be that writing three lines of code can turn into a nightmare. Solutions for X++ should improve not only the possibilities to do more but also to make it in a easier way. Actual disadvantages of x++ for computing a TSql are somehow bearable using views based on queries even on non-very-complex cases, for not talking about the generation of different execution plans (and when unfortunately sql picks always the slowest and heaviest indexes) to process data. More control on database operations are needed and others like handling different levels for catching exceptions when using transactions through a separate user connection. Like the good Martin said we’re in the good way but only about thinking on redesign and improve, his suggestion about start using Lambda on x++ is somehow a must in my opinion. I’m very sorry if this generates bad feelings, but I also think every day on these things on my job when I wonder how would I build something in x++ (everyday challenges) when with a good decision of Microsoft I could be using c#. My best wishes for everyone 🙂

    1. Yeah, lambdas and generics would be great. Just getting foreach would be amazing. Keep the feedback coming; I know our compiler team is listening.

  6. Matrix2008 says:

    Its very similar to Query and Querydatasource rangs etc ,sysDa dont offer syntax improvement .

    1. Thanks for the feedback. The differences may be subtle, but they are there. Notice how the local variable custTable is passed into SysDa – this means that it brings the state of the buffer forward. E.g. if you had called skipDataMethods, selectForUpdate, concurrencyModel or any of the other many methods – that would just work. In contract Query is a design time artifact – you can even model them in the AOT – you convert it to a QueryRun (the run-time artifact) which then can return records – based on the specifications in Query. As such SysDa is leaner and provides a different set of options.

      If you are interested in testability (another important aspect of software engineering), SysDa enables decoupling from the data base. Resulting in much faster and powerful test execution.

  7. Evaldas L says:

    Its definitely a good step towards select statement extensibility. Flexible way of building queries, especially if you have many similar ones. Macros, get rid of them as fast as possible. It should be put into a museum.
    However, I do expect something more sophisticated, maybe something hookable to the select statement itself or something templateable. Having these readable select statements in the language is such a joy. In the example a 3-liner turned into a 10-liner. Cannot imagine a good readability with more complex queries. What about reverse engineering and debugging? I guess that will become somewhat more complex.
    Regards 😉

    1. It should be possible to create a converted to/from SysDa – we are exploring options. For debugging then the experience is somewhat better than that of queries, as the ToString() method – which is what you see in the debug – returns the SQL for any SysDa object. It is quite convenient.

  8. Martin Dráb says:

    It’s a step in the right direction; I like it.
    I made some prototypes of a fluent interface for queries by myself; it’s great to see something like that out of the box.

    But I think it’s important to keep working on making the code simpler and more readable.

    What I hate the most are all the fieldStr() calls – it’s a lot of work to write and there is no validation whether the field belongs to the right table. I hoped for some compiler improvements to help with this problem. For example, this is what I could do with generics and lambda in C#:

    new SysDaFieldExpression(inventTrans, t => t.InventDimId);

    And I could simplify it by wrapping it in an extension method:

    inventTrans.toFieldExpression(t => t.InventDimId);

    Even something as simple as nameOf(inventTrans.InventDimId) would be an improvement.

    Hopefully we’ll get something like that one day, for now we must think about how to use the best what we have available. I’m now going to explore what we already have in SysDa… 🙂

    1. I fully agree, Martin. The version in PU22 is the first version of the API. In PU23 we added a few more fluent APIs – just to make it easier to consume. Any suggestions are welcome; especially if they stay within the current X++ syntax limitations. 🙂

      1. Anonymous says:
        (The content was deleted per user request)
      2. Martin Dráb says:

        So far I’ve at least created a few utility classes and an extension method to simplify the most common tasks.

        For instance, I can replace this:

        new SysDaEqualsExpression(
        new SysDaFieldExpression(priceDiscGroup, fieldStr(PriceDiscGroup, Module)),
        new SysDaValueExpression(ModuleInventCustVend::Cust)))
        .and(new SysDaEqualsExpression(
        new SysDaFieldExpression(priceDiscGroup, fieldStr(PriceDiscGroup, Type)),
        new SysDaValueExpression(PriceGroupType::LineDiscGroup)));

        With this:

        priceGroupQuery.whereClause(priceDiscGroup.sysDa().fieldValue(fieldStr(PriceDiscGroup, Module), ModuleInventCustVend::Cust))
        .and(priceDiscGroup.sysDa().fieldValue(fieldStr(PriceDiscGroup, Type), PriceGroupType::LineDiscGroup));

        (If it ignores line breaks again, I’ll have to find another platform to share feedback.)

Skip to main content