Kbase P122494: Changes in SQL startup parameters used in OpenEdge 10.1A or later
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
4/8/2010 |
|
Status: Verified
GOAL:
Changes in SQL startup parameters used in OpenEdge 10.1A or later
GOAL:
New SQL startup parameters for sorting in OpenEdge 10.1A or later
GOAL:
Should -SQLTempBuff be used in 10.1A or later?
GOAL:
Should -SQLTempPgSize be used in 10.1A or later?
GOAL:
Should -SQLTempDisk be used in 10.1A or later?
FACT(s) (Environment):
All Supported Operating Systems
OpenEdge 10.1x
FIX:
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 accom