Gen READ/READ EACH for SQL Server & "WITH XLOCK" or "WITH NOLOCK"

Products

• Gen
• Gen - Workstation Toolset

Issue/Introduction

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

Resolution

ANALYSIS

Per Gen 8.6 doc page READ, READ EACH, SUMMARIZE, SUMMARIZE EACH Properties Dialog - MS/SQL, Gen only supports adding a Query Hint for SQL Server (MS/SQL) and Table hints are not allowed.

For SQL Server, it might have been thought possible to add a Query Hint to the MS/SQL tab "Query Optimizer" field for the READ/READ EACH statement. However, when something is added there the generated code wraps the OPTION clause around it, e.g. if you add this in the Query Optimizer field:
    TABLE HINT(<exposed table name>,NOLOCK)
(where <exposed table name> is the name of the table in the Gen generated code), the generated code adds:
        OPTION (TABLE HINT(<exposed table name>,NOLOCK))

Per Microsoft SQL Server reference Microsoft > Query Hints (Transact-SQL)  (see "K. Specify semantics-affecting table hints") that won't be valid SQL unless there is a corresponding WITH clause before that OPTION clause i.e. 'WITH (NOLOCK)'. So using the MS/SQL tab "Query Optimizer" won't be a feasible option.

SOLUTIONS

1. 1. The only possible solution within Gen model code itself is to use INLINE code to:
      
      1. Add 'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITED' just before the READ/READ EACH statement.
      2. Add 'SET TRANSACTION ISOLATION LEVEL READ COMMITED' just after the READ/READ EACH statement.
         
         However great care would need to be taken to avoid potential problems, e.g. if there are other nested READ/READ EACH statement contained within the READ/READ EACH statement (or USE Action Blocks that contain other READ/READ EACH statement), then all of those other statements would use READ UNCOMMITTED too. Also managing the adding of such nested READ/REACH EACH statement in the future would have to considered.
         
         
   2. Manually modify the generated code to have the required table hint or build a post processor to do it automatically.