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.

About these ads
This entry was posted in Axapta Development. Bookmark the permalink.

One Response to ForceLiterals & ForcePlaceholders

  1. superb…
    Nicely said.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s