ForceSelectOrder & ForceNestedLoop

ForceSelectOrder
Using ForceSelectOrder will enforce SQL server to access the tables in a join in the given order. It means that performs joins in the order in which the tables appear in the query. Otherwise, SQL Server chooses the order.
 
ForceNestedLoop
Using ForceNestedLoop will force the SQL server to use a nested-loop algorithm to process a given SQL statement containing a join. Otherwise, hash-join and merge-join algorithm will be possible.
These two keywords will often use together.
If there are two tables: table1 and table2,
Table1
Field1 Field2
   1      2      
   1      3
   1      4
 
Table2
Field1 Field2
   2       1
   2       2
   2       3
 
while select ForceSelectOrder ForceNestedLoop table1
       index index1
       where table1.field1 == ‘Field1Value’
      Join table2
      index index1
      where table2.field1 == ‘Field1Value’
{
}
 
this will always fetch table1’s records before trying to fetch any records from the second table.
First loop:
Table1
Field1 Field2
   1      2
Table2
Field1 Field2
   2      1
Second loop:
Table1
Field1 Field2
   1      2
Table2
Field1 Field2
   2      2
Third loop:
Table1
Field1 Field2
   1     2
Table2
Field1 Field2
   2     3
Advertisements
This entry was posted in Axapta Development. Bookmark the permalink.

One Response to ForceSelectOrder & ForceNestedLoop

  1. Glen says:

    Very good! However, assuming the case of an element.query() – over riding the fetch method, as such;

    element.query().dataSourceTable(tablenum(SalesLine)).findRange(fieldnum(SalesLine, ShippingDateRequested)).value(dateRange);

    How would the following ordering effect the query execution path?

    element.query().orderByField(tablenum(SalesLine));

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s