Performance Tuning
The following describes how to maximize the throughput using sorts and process cache.
To maximize the throughput for a number of the Basic I/O operations that operate on server tables, you must carefully plan the design of the table. The client process creates generic SQL Server tables. It makes no assumption about the data to be contained in the record other than what it determines from the Dictionary-IV Format. This is sufficient to describe the table to SQL Server, but under some conditions, sorting, for example this may not be enough.
SQL Server creates result sets that it generates from statements that specify ordering whether or not an index exists for the columns listed in the ORDER BY clause. Without an index this operation is very time consuming for large tables. Within the Basic environment this is multiplied since each access to the table may require an in-line sort. Basic saves the result set from a particular query and uses it if the subsequent query matches the one that generated the result. If it does not match, the result set must be regenerated the next time the query is executed.
The solution is to create an index for the columns to sort and define at least one column as NOT NULL (a mandatory field in Dictionary-IV). This must be done or the index is ignored for ORDER BY processing.
The create process translates mandatory fields and the first column of the primary key into columns with the NOT NULL constraint if PRM CREATWDBATTR is configured. If these columns are part of indices, you need no further changes.
Degradation due to indices being ignored becomes evident in:
Basic
using LKY( ), PKY( ), and I/O with sorts
Dictionary-IV File access with sorts (multi-record maintenance, view maintenance, sort order change) and backward reads in files (multi-record maintenance up arrow and page up)
Script-IV
READ PREVIOUS, LAST TO FIRST, sorts, etc.
Logon Cache
Section titled “Logon Cache”All access to the SQL Server database occurs through one login to the server using multiple cursors.
When a LOCK, EXTRACT, or WRITE is performed in Basic a new login is executed to preserve key pointers to the other tables opened on the original login (LOCKs, EXTRACTs, and WRITE will perform commits and will clear all record locks). Once the LOCK, EXTRACT, or WRITE is complete, this login is terminated. The logon cache count parameter in the IPLINPUT file is used to specify the number of login processes to retain.
For example, if the logon cache count is set to 5, a pool of up to 5 SQL login processes will remain open. The next LOCK, EXTRACT, or WRITE executed by Basic will attach itself to one of these 5 processes bypassing the overhead to login and establish a new process.
By configuring this value properly, a balanced maximum can be maintained between excessive login processing and the number of active logins. A maximum of 20 logins can be cached.
Registry Settings
Section titled “Registry Settings”The first Basic client connection will create registry settings, if they do not already exist. These settings can be used to optimize performance. To view the registry settings, run regedit from the Windows start menu. The Thoroughbred entries can be found in:
\HEY_LOCAL_MACHINE\SOFTWARE\Thoroughbred Software International\TS DataServer for SQL Server. The registry settings can be used as follows:
- CLIENT-SYSTEM-INFO
- Not used.
- COMMIT COUNT
- Database commits are done for all actions (add, drop, insert, update, etc.). For batch processing, you may want to adjust this value so commits are done less often. Set this value to the number of transactions between database commits. For example, if this value is 1000, a database commit will be done after every 1000 transactions.
- DATA-SOURCE
- Basic will use this Data Source for all SQL connections. This allows users to connect to a common database. The default system Data Source is LocalServer. See SERVER.MAP for additional capabilities.
- DEBUG-FLAGS
- This entry is used to enable or disable additional information that can be written into the debug log file. The flag values are:
| Flag Number | Description |
| 1 | Disable logging of the process ID number |
| 2 | Enable logging of a time stamp |
| 4 | Enable logging of parameter values. |
| 8 | Enable logging of fetched values |
- DEBUG-LEVEL
- If this value is non-zero, all SQL statements will be logged. The log file (tsmssql.log) can be found in the installed directory on the Server.
- LDA-CACHE
- This entry can be set to change the number of Logon Data Areas to cache. Depending activity (read, extract, sort access, etc.), as many as 6 LDAs may be associated with an open Basic channel. See the OPEN-TABLE-CACHE comments below regarding resource usage.
- LOGGING
- This sets the logging group to something other than the default-logging group.
- NO-OPENLOCK
- If this entry is non-zero, no Basic locks are done during OPEN. The Basic OPEN will verify the table exists. There is no concept of OPENing a table in SQL. The default lock helps Basic return an Error=0 when a user tries to ERASE a table opened by another user.
- OPEN-TABLE-CACHE
- A numeric value indicating how many open Basic channels to cache. This will enhance performance in the area of table opens. But,this setting should be used with caution. If this value is set too high, the tsmssql process will become a burden on system resources.
- Path
- This entry contains the install path and is used for updates.
- READUNCOMMITTED
- If this entry is non-zero, “WITH (NOLOCK)” will be added to generated SQL statements when READing. This control is enabled by default.
- UNIQUE-SORTS
- Any non-zero value will indicate all secondary sorts are unique. This will speed up the fetch process. Only set this value, if you you’re absolutely sure your sort definitions will generate unique keys. See PRM UNIQUE-KEYS for additional tuning.
From the TS DataServer for MS SQL Server, version 8.9.1. Open this page in the PDF