Optimizing Performance when using an RDBMS

This page contains some tips to optmize performance when you store your data in an RDBMS.

APPX performance with data stored in APPXIO is always better than APPX performance with data stored in SQL Server or Oracle. However, there are many things you can do to improve performance. If all of the following suggestions are followed, good performance can result. In a few special cases, performance may actually be better than APPXIO.

1. If at all possible, install the RDBMS on the same server as APPX. This can eliminate the network delays and can result in a significant improvement in APPX performance.
2. If the RDBMS must be installed on another server, make sure that the network connection is fast - at least 1 gigabit or more. Also, if the RDBMS must be installed on another server, tune your network to minimize the number of TCP/IP packets that will be transmitted between the servers. This can be done by increasing the packet size.
3. In output processes, remove unnecessary DLUs that may have been included when an item was added to the output image.
4. Read with Hold is very slow compared to reading without a hold. Do not read a record with hold unless you know you will REWRITE or DELETE the record.
   • BEG READ/END READ
     • Don't read with hold unless you know you will be updating most of the records read
     • Use READ with hold within the BEG READ/END READ loop after you determine that you need to update the record.
   • Update and Output processes always read the PCF records with hold if a REWRITE or DELETE statement appears anywhere within the process. This is true even if the REWRITE or DELETE statement is not for the PCF.
5. Load Read-Only files into cache for a big performance boost
   • Use SCRATCH with CACHE? Y followed by CREATE with CACHE LOAD to initialize and populate an in-memory copy of relatively small files which are only read by a process. After executing the CREATE statement, the file will have been populated and will be Open for access instead of the actual disk file.
   • You can easily switch between the disk file and the cache file by using the OPEN statement with CACHE Y or N. You do not need to CLOSE the file when switching back and forth between the disk and cache files.
   • The scope of a cache file is Detached. The scope of the record buffer is Subprocess as expected. The scope of the "open" is Subprocess for Permanent files and in the case of temporary files matches the scope of the temporary file.
   • See The Use of Cached Files for more details on using Cache files.
6. Use "Partial Record IO" for a big boost in performance when accessing files that have lots of fields
   • A READ statement with a file name issues a SELECT * to the database and return a value for every field in the record. A READ statement which uses a field name instead of a file name, only returns a value for the specified field. If the named field is a Group, then only the fields in the group will have values returned by the READ
   • Use "Partial Record IO" for REWRITE statements. Even if you READ the entire record, you can still use Partial Record IO when you execute a REWRITE statement. If this case, only the specified field or group of fields will be updated in the database.
   • Partial Record IO is good to use when reading a record with hold to delete it if you don't need to examine the field values before executing a DELETE.
   • PARTIAL RECORD IO can be specified for the PCF file in the Additional Attributes of the process.
   • Partial Record IO is ignored when used on an APPXIO file. The full record is always processed.
7. If you are using SQL Server, consider using hold type 3 on READ, READNEXT, and BEG READ statements and on Output and Update processes to avoid locking records in the database and for a performance boost.
   • This hold type reads the record without holding it but APPX knows that you intend to possibly update it.
   • If you subsequently REWRITE the record, the REWRITE statement will not succeed and the REWRITE statement will set the F flag if the record has been changed by another process since it was originally read from the database.
   • Hold type 3 can be specified for the PCF record in the Additional Attributes of the process.
   • Hold type 3 is treated like hold type 1 for APPXIO files
   • See SQL Server Interface Performance for more details
8. If you are using SQL Server, consider using hold type 4 on READ, READNEXT, and BEG READ statements and on Update and Output processes and on the associated Query process for a big performance boost, especially when processing large files.
   • This causes the entire set of selected records to be read from the database (very fast) and written to a temporary file on the APPX server's disk. The records are then processed from this local disk copy.
   • If you subsequently REWRITE a record, the REWRITE statement will not succeed and the REWRITE statement will set the F flag if the record has been changed by another process since it was originally read from the database.
   • Hold type 4 can be specified for the PCF record in the Additional Attributes of the process.
   • Hold type 4 is treated like hold type 1 for APPXIO files
   • See SQL Server Interface Performance for more details
9. Optimize Query processes so that they only process the needed subset of records
   • Consider using a child constraint on the QUERY step of the job. This will allow APPX to read a subset of the records from the database.
   • Consider coding a BEG READ/END READ in the Establish PCF Range event point of a Query to select only the desired records
   • Consider setting the BEG? END? flags on Fields which can be used to constrain the PCF.
10. Consider using child constraints on Update and Output processes which have no QUERY if a subset of records is to be processed.
11. Consider eliminating automatic children in Update and Output processes, especially if the PCF of the child process is stored in a database.
12. Consider using Subroutine processes instead of Update processes.
    • In most cases, processing a range of records in a subroutine process using a BEG READ/END READ loop is more efficient that using an Update process when processing a file that is in a database.
13. Commits to the database have a significant impact on performance
    • Consider changing the commit mode of Update and Output processes. The default commit mode for APPX processes is "COMPATIBLE". This tells APPX to try to be as compatible as possible to APPXIO when accessing data in a database. Since APPXIO updates are immediately written to disk, APPX will schedule a commit after every REWRITE, WRITE, or DELETE statement. This can have a huge impact on performance. Consider changing to PCF CYCLE, END, or MANUAL commit mode.
    • Minimize commits to the database
    • Be aware that a COMMIT statement commits all pending updates and releases all locked records. A COMMIT statement applies to all tables.
14. Be aware that databases allow a process to lock more than one record in the same table.
    • Don't read a record with hold unless you intend to update it. Records in a database remain locked until a commit takes place. In COMPATIBLE mode, a commit only takes place after a record is updated. For example, if you read 100 records with hold before you update one, all 100 records will be locked until the update is performed and the subsequent commit takes place.
    • Consider adding a COMMIT statement to the End of Process event point of Update and Output processes. This will ensure that records are not unexpectedly locked in the database.

Comments

-- Jean Neron - 2016-09-27