How to use a recurring Integration Endpoint for importing data
You are here
Select FirstOnly vs Select FirstOnly ForceSelectOrder ForceNestedLoop
Last modified:
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 forcenestedloopstopwatch.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 forcenestedloopstopwatch.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 forcenestedloopstopwatch.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.