ForceLiterals & ForcePlaceholders AX

ForceLiterals & ForcePlaceholders

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.