Enhancements Using the Cost-Based Optimizer from the Axapta 3.0 CD'.
1.1 General Optimization Keywords
o forceLiterals
Instructs the kernel to reveal the actual values used in where clauses to the SQL server at the time of optimization. This is the default behavior in all join statements involving more than one table from the table groups:
Miscellaneous,
The advantage of using this keyword is that the server now gets all information to calculate the optimal access plan for a statement. The disadvantage is that the access plan cannot be reused with other search values and that the optimization may use more CPU on the SQL server. High frequency queries should NOT use literals.
The X++ statement below is an example of how to use this keyword:
SELECT forceLiterals * FROM myTable
WHERE myField > 10
ORDER BY myField2
It is not possible to determine if an index on myField2 or an index on myField1 should be used without considering the actual value 10. Therefore the keyword should be used as above.
ForcePlaceholders
Instructs the kernel not to reveal the actual values used in where clauses to the SQL server at the time of optimization. This is the default in all non-join statements. The advantage of using this keyword is that the kernel will be able to reuse the access plan for other similar statements with other search values. The disadvantage is that the access plan is computed without taking into consideration that data distribution might not be even or in other words the access plan is an on average access plan.
The X++ statement below is an example of when to use this keyword:
SELECT forcePlaceholders * FROM orderLines
JOIN orderTable
WHERE orderLines.orderNo == orderTable.orderNo
AND orderTable.orderNo == 10
In the above example, the SQL server automatically chooses to search the orderTable using an index on orderNo. The SQL server knows that the orderNo column is a unique field and thus do not need to know the actual search value to compute the optimal access plan.
ForceSelec ForcedSelectOrder
This keyword forces the SQL server to access the tables in a join in the given order. If two tables are joined the first table in the statement is always accessed first. This keyword is often combined with the ForceNestedLoop keyword.
Force opF ForcedNestedLoop
This keyword forces the SQL server to use a nested-loop algorithm to process a given SQL statement containing a join. This means that a record from the first table is fetched before trying to fetch any records from the second table. Normally other join algorithms like hash-joins, merge-joins and others would be considered. This keyword is often combined with the ForceSelectOrder keyword.
1.1
MBS Axapta <[EMAIL PROTECTED]> wrote:
Hi,
I have doubt on select statement which is written in the Axapat on a class known as "InventSumDate" .
If you look the code in the methods( "selectInventTransPostingFinancial"
or selectInventTransPostingPhysical) of
class "InventSumDate".
There is a while statement like below.
"while select forceplaceholders forceSelectOrder forceNestedLoop tableId from inventTransPostingPhysical
index hint ItemTypeDateIdx...".
Can anyone explain for me whats the meaning of the words as per below
1). forcePlaceholders
2). forceSelectOrder
3). forceNestedLoop
Hope i will get the reply soon.
Thanks in adv.
Regards![]()
Start your day with Yahoo! - make it your home page
Sharing the knowledge on Axapta.
| Business finance course | Business to business finance | Small business finance |
| Business finance consultant | Business finance magazine | Business finance schools |
YAHOO! GROUPS LINKS
- Visit your group "Axapta-Knowledge-Village" on the web.
- To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
- Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
