Let's explore what keywords forceSelectOrder and ForceNestedLoop do.

Introduction

The database access layer in X++ largely conceals it magic from us as developers and we hope it gets the SQL right - and it does. SQL is largely 99.9% accurate and about 95%-ish optimized for the workload presented. You can read more about some of the techniques we'll be using here. We'll be using keywords in an X++ select to how they change the query and what that change means. All tests below were run on a "warm" VHD using Contoso data - specifically USMF.

Select FirstOnly SalesTable

First, we'll start with a simple select firstonly on SalesTable. This took 30 milliseconds and generated the following SQL (simplified):

SELECT TOP 1 [fields]

FROM   salestable T1

WHERE  ( ( partition = 5637144576 )

         AND ( dataareaid = N'usmf' ) ) 

Code is:

public static void salesTable()
    {
        System.Diagnostics.Stopwatch stopwatch = new System.Diagnostics.Stopwatch();

        SalesTable salesTable;
   
        ttsbegin;
        stopwatch.Start();
        //use forUpdate in transaction to force database hit
        select firstonly forupdate * from salesTable;
        Stopwatch.Stop();
        ttscommit;
                            
        //again with generateonly
        select generateonly firstonly * from salesTable;
        var sql = salesTable.getSQLStatement();

        info(strFmt("milliseconds: %1", stopwatch.ElapsedMilliseconds));
        info(strFmt("sql statement: %1", sql));
    }

Select FirstOnly ForceSelectOrder ForNestedLoop SalesTable

Next, let's add the ForceSelectOrder and ForceNestedLoop on our last query. This took 30 milliseconds and generated the following SQL (simplified):

SELECT TOP 1 [fields]

FROM   salestable T1

WHERE  ( ( partition = 5637144576 )

         AND ( dataareaid = N'usmf' ) )

OPTION(force ORDER) 

You can see it added a SQL query option. This option would give us some flexibility to change how SQL would handle this query. We are only selecting one table so the order won't matter but you can still see we changed the SQL. Also note since we're not in a loop, it ignored one of our keywords: ForceNestedLoop. Code is:

public static void salesTableForced()
    {
        System.Diagnostics.Stopwatch stopwatch = new System.Diagnostics.Stopwatch();

        SalesTable salesTable;

        stopwatch.Reset();

        ttsbegin;
        stopwatch.Start();
        //use forUpdate in transaction to force database hit
        select firstonly forupdate forceselectorder forcenestedloop * from salesTable;
        stopwatch.Stop();
        ttscommit;

        select generateonly firstonly forceselectorder forcenestedloop * from salesTable;
        var sql = salesTable.getSQLStatement();

        info(strFmt("forced milliseconds: %1", stopwatch.ElapsedMilliseconds));
        info(strFmt("forced sql statement: %1", sql));
    }

Select FirstOnly SalesTable Join SalesLine

Next, let's get the SQL for a simple non-looped join on SalesTable and SalesLine. This took 58 milliseconds and generated the following SQL (Simplified):

SELECT TOP 1 [fields]

FROM   salestable T1

       CROSS JOIN salesline T2

WHERE  ( ( T1.partition = 5637144576 )

         AND ( T1.dataareaid = N'usmf' ) )

       AND ( ( ( T2.partition = 5637144576 )

               AND ( T2.dataareaid = N'usmf' ) )

             AND ( T1.salesid = T2.salesid ) ) 

Nothing special here. As a call out, I didn't specify a join type in my code so we got a cross join. Code is:

    public static void salesTableSalesLine()
    {
        System.Diagnostics.Stopwatch stopwatch = new System.Diagnostics.Stopwatch();

        SalesTable salesTable;
        SalesLine salesLine;
   
        ttsbegin;
        stopwatch.Start();
        //use forUpdate in transaction to force database hit
        select firstonly forupdate * from salesTable
            join salesLine
            where salesTable.SalesId == salesLine.SalesId;
        Stopwatch.Stop();
        ttscommit;
                            
        //again with generateonly
        select generateonly firstonly * from salesTable
            join salesLine
            where salesTable.SalesId == salesLine.SalesId;
        var sql = salesTable.getSQLStatement();

        info(strFmt("milliseconds: %1", stopwatch.ElapsedMilliseconds));
        info(strFmt("sql statement: %1", sql));
    }

Select FirstOnly ForceSelectOrder ForNestedLoop SalesTable Join SalesLine

Next, let's step it up a little and get the SQL using our two keywords on a join. This took 59 milliseconds and generated the following SQL (Simplified):

SELECT TOP 1 [fields]

FROM   salestable T1

       CROSS JOIN salesline T2

WHERE  ( ( T1.partition = 5637144576 )

         AND ( T1.dataareaid = N'usmf' ) )

       AND ( ( ( T2.partition = 5637144576 )

               AND ( T2.dataareaid = N'usmf' ) )

             AND ( T1.salesid = T2.salesid ) )

OPTION(loop JOIN, force ORDER) 

You can see with now have some option values on our query which will effect how it executed in SQL. The code is: 

public static void salesTableSalesLineForced()
    {
        System.Diagnostics.Stopwatch stopwatch = new System.Diagnostics.Stopwatch();

        SalesTable salesTable;
        SalesLine salesLine;
        //using select firstonly forceselectorder forcenestedloop

        stopwatch.Reset();

        ttsbegin;
        stopwatch.Start();
        //use forUpdate in transaction to force database hit
        select firstonly forupdate forceselectorder forcenestedloop * from salesTable
            join salesLine
            where salesTable.SalesId == salesLine.SalesId;
        stopwatch.Stop();
        ttscommit;

        select generateonly firstonly forceselectorder forcenestedloop * from salesTable
            join salesLine
            where salesTable.SalesId == salesLine.SalesId;
        var sql = salesTable.getSQLStatement();

        info(strFmt("forced milliseconds: %1", stopwatch.ElapsedMilliseconds));
        info(strFmt("forced sql statement: %1", sql));
    }

Select Firstonly SalesLine Join SalesTable

Next, let's see if the order in which we use our buffers in a select have any effect on the generated SQL. This took 56 milliseconds to run and generated the following SQL (simplified):

SELECT TOP 1 [fields]

FROM   salesline T1

       CROSS JOIN salestable T2

WHERE  ( ( T1.partition = 5637144576 )

         AND ( T1.dataareaid = N'usmf' ) )

       AND ( ( ( T2.partition = 5637144576 )

               AND ( T2.dataareaid = N'usmf' ) )

             AND ( T1.salesid = T2.salesid ) ) 

As you can see, when we change our buffer selection order, SQL matches the order of X++. The code is:

public static void salesTableSalesLineReverse()
    {
        System.Diagnostics.Stopwatch stopwatch = new System.Diagnostics.Stopwatch();

        SalesTable salesTable;
        SalesLine salesLine;
   
        ttsbegin;
        stopwatch.Start();
        //use forUpdate in transaction to force database hit
        select firstonly forupdate * from SalesLine
            join SalesTable
            where SalesLine.SalesId == SalesTable.SalesId;
        Stopwatch.Stop();
        ttscommit;
                            
        //again with generateonly
        select generateonly firstonly * from SalesLine
            join SalesTable
            where SalesLine.SalesId == SalesTable.SalesId;
        var sql = SalesLine.getSQLStatement();

        info(strFmt("milliseconds: %1", stopwatch.ElapsedMilliseconds));
        info(strFmt("sql statement: %1", sql));
    }

Select FirstOnly ForceSelectOrder ForNestedLoop SalesLine Join SalesTable

next, let's see if the order in SQL change when we add our two keywords. This look slightly longer at 66 milliseconds to run and generate the following SQL (simplified):

SELECT TOP 1 [fields]

FROM   salesline T1

       CROSS JOIN salestable T2

WHERE  ( ( T1.partition = 5637144576 )

         AND ( T1.dataareaid = N'usmf' ) )

       AND ( ( ( T2.partition = 5637144576 )

               AND ( T2.dataareaid = N'usmf' ) )

             AND ( T1.salesid = T2.salesid ) )

OPTION(loop JOIN, force ORDER) 

Again, we see our keywords as options on our query. The code for this is:

    public static void salesTableSalesLineForcedReversed()
    {
        System.Diagnostics.Stopwatch stopwatch = new System.Diagnostics.Stopwatch();

        SalesTable salesTable;
        SalesLine salesLine;
        //using select firstonly forceselectorder forcenestedloop

        stopwatch.Reset();

        ttsbegin;
        stopwatch.Start();
        //use forUpdate in transaction to force database hit
        select firstonly forupdate forceselectorder forcenestedloop * from SalesLine
            join SalesTable
            where SalesLine.SalesId == SalesTable.SalesId;
        stopwatch.Stop();
        ttscommit;

        select generateonly firstonly forceselectorder forcenestedloop * from SalesLine
            join SalesTable
            where SalesLine.SalesId == SalesTable.SalesId;
        var sql = salesLine.getSQLStatement();

        info(strFmt("forced milliseconds: %1", stopwatch.ElapsedMilliseconds));
        info(strFmt("forced sql statement: %1", sql));
    }

While Select Firstonly SalesTable Join SalesTable

Next, let's add a while around this and see how our execution times change. GenerateOnly isn't supported against a query in a while loop so I don't specifically have the SQL to display. We can assume it's the same as the same query above with no While. This took 98 milliseconds to run. The code for this is:

    public static void salesTableSalesLineLoop()
    {
        System.Diagnostics.Stopwatch stopwatch = new System.Diagnostics.Stopwatch();

        SalesTable salesTable;
        SalesLine salesLine;
   
        ttsbegin;
        stopwatch.Start();
        //use forUpdate in transaction to force database hit
        while select firstonly forupdate * from salesTable
            join salesLine
            where salesTable.SalesId == salesLine.SalesId
        {
            //have to do something here or optimizer cuts out the loop
            info(salesLine.SalesId);
        }
        Stopwatch.Stop();
        ttscommit;                           

        info(strFmt("milliseconds: %1", stopwatch.ElapsedMilliseconds));
    }

While Select ForceSelectOrder ForNestedLoop Firstonly SalesTable Join SalesTable

Next, we're going to add a while loop with our two new keywords. Again, GenerateOnly isn't supported so we'll assume it uses the same SQL without the file. This look 94 milliseconds to run. The code for this is:

    public static void salesTableSalesLineLoopForced()
    {
        System.Diagnostics.Stopwatch stopwatch = new System.Diagnostics.Stopwatch();

        SalesTable salesTable;
        SalesLine salesLine;
        //using select firstonly forceselectorder forcenestedloop

        stopwatch.Reset();

        ttsbegin;
        stopwatch.Start();
        //use forUpdate in transaction to force database hit
        while select firstonly forupdate forceselectorder forcenestedloop * from salesTable
            join salesLine
            where salesTable.SalesId == salesLine.SalesId
        {
            //have to do something here or optimizer cuts out the loop
            info(salesLine.SalesId);
        }
        stopwatch.Stop();
        ttscommit;

        info(strFmt("forced milliseconds: %1", stopwatch.ElapsedMilliseconds));

    }

Take Aways

Over the years from AX 2009 to 2012 to F&O, the database access layer has gotten better and better - smarter and smarter. While these keywords can have an effect, they are likely uneeded 98% of the time unless the data abstraction layer falls flat on its face for SQL and SQL can't optomize the problems out of the poorly generate query. Just keep keywords ForceSelectOrder, ForceNestedLoop, GenerateOnly in your tool belt for the 1 time a year you may need them.