Tuesday, August 30, 2011

ForceLiterals and ForcePlaceHolders in AX


When you use forceliterals keywords in Axapta, Axapta will issue SQL statements directly to the database as text string.
SELECT forceLiterals * FROM purchTable
    WHERE purchId == ‘EN00009’ ;
In SQL server, it will be:
SELECT A.VALUE, A.MODIFIEDTIME, A.CREATEDTIME, A.RECID FROM HINTTABLE A(NOLOCK) WHERE (PURCHID="EN00009") OPTION(FAST 47)
Conversely, using ForcePlaceHolders in Axapta, Axapta will issue SQL statements to the database, and a temporary stored procedure being created for this statement. This stored procedure then remains within the database for as long as the connection that was used when issuing the statement remains.
SELECT forcePlaceHolders * FROM purchTable
    WHERE purchId == ‘EN00009’ ;
In SQL server, it will be:
SELECT A.VALUE, A.MODIFIEDTIME, A.CREATEDTIME, A.RECID FROM HINTTABLE A(NOLOCK) WHERE (PURCHID=" @P1") OPTION(FAST 47)
Using forcePlaceHolders will help SQL Server to save the time to recompile the execution plan, that is, reuse the execution plan.
Excess use of the forcePlaceHolders can degrade performance. If a statement is executed only once, forceLiterals is preferred because it requires only one network round trip to the server. While using forcePlaceHolders for a statement executed only one time requires an extra network round-trip; one trip to prepare the statement and one trip to execute it.