Created On:  13 April 2012

Problem:

Reading a large table with Acu4GL in a SQL Server Data Base accessing the records by alternate key, the performance decreases dramatically compared with a VISION file.

Resolution:

It is a fact that accessing a data base table instead a VISION file, the performance decreases due to VISION files are the native file system handled by Extend runtime with no translations at all. This performance hit increases if the program is accessing the tables using alternate keys; data bases don’t have and don’t know the alternate key’s concept.

In Cobol, the two most expensive statements, in terms of performance, are OPEN and START; accessing a VISON file this  impact is not noticeable but it is when we are working with a data base.

Using the above COBOL statements extensively in the same program or in the same runtime unit will decrease the performance of the runtime.

Said that, there are some guidelines described in Chapter 9 Performance and Troubleshooting; 9.1 Performance Issues; 9.1.1 Guidelines  in our Acu4GL User's Guide. 

There is an emphasis in The Where Constraint which allows to improve performance in some situations.

In addition to the WHERE constraint, there are some Acu4GL configuration variables that can improve performance.  They are:

     A_MSSQL_FAST_ACCESS 

     A_MSSQL_ROWCOUNT

     A_MSSQL_USE_DROPDOWN_QUERIES 

     A_MSSQL_LIMIT_DROPDOWN

     A_MSSQL_SELECT_KEY_ONLY

     A_MSSQL_PACKETSIZE

     A_MSSQL_NO_CACHED_READ

Users need to read the documentation on each of these to determine whether they apply to their application.  Some testing can help determine the best settings.  These can all have a dramatic effect on performance depending on what the program is doing.

In addition to that there are two configuration variables to disable locking which users may want to implement if the program is only reading data: 

     A_MSSQL_NO_TABLE_LOCKS 

     A_MSSQL_NO_RECORD_LOCKS

 They both have the obvious consequences.

Incident #2567144