RDBMS Performance Hints

Before you start defining your application it may wise to read the performance hints provided by the vendor of your target RDBMS. Also, keep the following in mind during the development project:

·	If you use joins, make sure that the table from which you expect to retrieve the smallest number of records comes LAST in the FROM clause.

·	If you use GROUP BY constructs, use WHERE clauses instead of HAVING clauses as much as possible.

·	In WHERE clauses, use columns that have been indexed.

·	Implementing subtypes/supertypes in ONE table will often be faster than implementing them in separate tables. You can evaluate the performance from situation to situation, and make your decision(s) accordingly.

Further, you can set the following resources in the USDIA or USDIW files to improve performance on Oracle databases:

holdCursor

This resource specifies whether cursor should be kept for later use. The default value is True.

*holdCursor: True | False

maxOpenCursor

This resource is a hint for the maximum number of open cursors at any one time. The default value is 75. This resouce has no effect on the number of open cursors that the database itself will allow. The resource acts on the USoft side of the connection, and the performance gain is based upon reduced parsing of statements. The real limit for the maximum number of open cursors is set in the global Oracle file. .

*maxOpenCursor: <integer>

optimizationArea

This resource is used to balance network traffic and memory usage. The value specifies the size of the memory blocks (in bytes) used to optimize record fetching from the database. Using the array fetching facilities, an array of records fetched is buffered in this memory block. The array size depends on the number of records that will fit in the optimization area. If you specify a very small number (e.g. 1), array fetching will not be used at all. The default value is 2048.

*optimizationArea: <integer>

NOTE:

Always remember that USoft Developer allows you to try and change rather easily, and at relatively low costs. Therefore, if you are not satisfied with the performance at some point, change your specifications in the repository and then regenerate your application and the corresponding database structure.