Using SQL Server as the target database can a TABLE HINT like "WITH XLOCK" or "WITH NOLOCK" be added to Gen READ/READ EACH Action Diagram statements for improved performance?

Document ID : KB000056542
Last Modified Date : 14/02/2018
Show Technical Document Details

The current Gen version 8.5 does not explicitly support adding a Table Hint for any target database.

For SQL Server it is possible vis the workaround of adding a Query Hint to the MS/SQL tab "Query Optimizer" field for the READ/READ EACH statement. To do this the user needs to know and use the exact name that is in the Gen generated code for the <exposed object name> for the Table in each statement e.g. in the Sample model the <exposed object name> in this toolset generated code for the table DEPARTMENT is DEPARTMENT01 :

"SELECT FROM DEPARTMENT DEPARTMENT01 ..."

Then following the required syntax per this Microsoft SQL Server reference Query Hints (Transact-SQL) , the following Query Hint can be added to the MS/SQL Query Optimizer field:

WITH (XLOCK) TABLE HINT(DEPARTMENT01,XLOCK)

For Oracle a similar Query Hint could also be added.

 

NOTE: The <exposed object name> for the Table may be different in the coded generated from the CSE compared to the Toolset.