Understanding forceNestedLoop

In Axapta, there is a SQL keyword forceNestedLoop which forces the SQL server to use a nested-loop algorithm to process a given SQL statement containing a join.
The nested loops join, also called nested iteration, uses one join input as the outer input table and one as the inner input table.
while select forceNestedLoop myTableOne             //outer input table
       where myTableOne.fieldOne == valueOne
       join myTableTwo                                                //inner input table
       where myTableTwo.fieldOne == valueTwo
       //codes here
The outer loop consumes the outer input table row by row. The inner loop, executed for each outer row, searches for matching rows in the inner input table. In the simplest case, the search scans an entire table or index; this is called a naive nested loops join. If the search uses an index, it is called an index nested loops join. If the index is built as part of the query plan (and destroyed upon completion of the query), it is called a temporary index nested loops join.
All these variants are determined by the query optimizer. A nested loops join is particularly effective if the outer input is quite small and the inner input is pre-indexed and quite large. In many small transactions, such as those affecting only a small set of rows, index nested loops joins are far superior to both merge joins and hash joins. In large queries, however, nested loops joins are often not the optimal choice.
So before you use forceNestedLoop in Axapta, please take a careful consideration and conduct testings to make sure this keyword will improve performance.
This entry was posted in Axapta Development. Bookmark the permalink.

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