Changes in SQL startup parameter used in OpenEdge 10.1A
In Open Edge SQL 10.1A, there is a completely new, high performance temp data manager. The temp data manager is used to do sorting, as well as "dynamic indexing" and certain temporary data storage. As a result, 10.1A SQL is much more scalable, faster sorting, which incurs less IO and uses less disk space. To get this high performance; the new sort makes aggressive use of process memory automatically, without startup parameters or other user intervention.
In SQL, sorting is used for the SQL ORDER BY and GROUP BY clauses, and the DISTINCT operation, as well as certain data retrieval strategies. The query may not appear to require sorting but sorting may be part of an estimated, optimal data retrieval strategy. For example, when a query contains multiple OR'd predicates which select on index keys, and the estimated costs are in favor, SQL may use a data retrieval strategy of unioning the sets of rowid which satisfy those predicates. This strategy of unioning requires sorting.
The new 10.1A sort applies the startup parameter -SQLTempBuff in a different manner than OpenEdge 10.0B and earlier. -SQLTempBuff, if specified, now defines the amount of memory to use as a data buffer for each Sort operation. Formerly, -SQLTempBuff specified a temp data buffer shared among all the Sort operations for a query. Thus, a query with multiple Sort operation (either visible or internal) will use more memory in OpenEdge 10.1A for sorting.
In OpenEdge 10.0B and earlier, -SQLTempBuff and -SQLTempPgSize could be used to accommodate large sort volumes and large sort records. The new 10.1A sort has a much more compact, highly efficient record format which automatically solves these earlier problems. Therefore, these startup parameters are not needed for result sets with large data rows.
For these reasons, in Open Edge 10.1A SQL customers should re-consider, and probably change, their use of SQL startup parameters. If all your application's queries execute fine without the use of the -SQLTempBuff, then it is better not to use this database startup parameter to maintain high performance and better resource use. However, a larger –SQLTempBuff parameter value can minimize or eliminate the need for disk IO and in turn improve the performance of certain queries. Indeed, setting a larger –SQLTempBuff parameter value is necessary if the query fails when processing an unusually large amounts of data or if the process executing that query hangs or crashes.
Also in Open Edge 10.1A, the SQL startup parameters -SQLTempDisk and -SQLTempPgSize are not used by SQL, except in very unusual circumstances. Therefore, customers can discontinue use of the startup parameters.
Summarizing, these pre-existing SQL startup parameters have these meanigs in OpenEdge 10.1A:
-SQLTempBuff – sort buffer size, per sort. Default is 1M.
-SQLTempPgSize – not used (in almost all circumstances).
-SQLTempDisk – not used (in almost all circumstances).
Other new SQL startup parameters for sorting in OpenEdge 10.1A or later:
-Bt – number of IO buffers used to read temp data. Also partially determines merge factor used in sorting. Default value is 32.
-T – directory where temp data, for sorting and other uses, is written. Default value is directory specified by $WRKDIR.
-t – whether the temp data disk file is visible or hidden. By default it is hidden on UNIX platforms.
