Kbase P105478: Is there a negative impact on performance when SQL-WIDTH is set to a "very large" value?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
19/10/2010 |
|
Status: Verified
GOAL:
Is there a performance impact as a result of setting SQL-WIDTH to a very large value?
GOAL:
What effect would be caused as a result of making the SQL-WIDTH of a column very large?
GOAL:
What effect would be caused as a result of making the SQL-WIDTH of a column very large (e.g. larger than 8K) ?
FACT(s) (Environment):
Progress 9.x
OpenEdge 10.x
All Supported Operating Systems
FIX:
The value of SQL-WIDTH is used by the SQL engine to allocate a data buffer to hold the value of a column, when the column is selected by a SQL statement. Most column value buffers are allocated once only for the lifetime of a statement's execution, so large column sizes are fine.
There can be costs in sorting (for the ORDER BY, or GROUP BY, or DISTINCT phrases) in our current sort implementation when the SQL-WIDTH is very large (e.g. larger than 8K). Our current sort allocates sort record space based on the defined size (== SQL-WIDTH) of a column.
Very large column sizes consume much more sort space, and make sorting much more expensive.
Also, our default maximum sort page size is 8K. When large column sizes are sorted, you should use startup parameters to increase the sort page size using the parameter:
-SQLTempPgSize (specified in units of 1K bytes).
The SQL-WIDTH is also used to describe to the client application the size of a column. Many application tools, like Crystal Reports, do special handling for large column values. This could be a problem.