Kbase P69721: Performance of SQL92 query having pro_element() is slow in 9.1D comparing to 9.1B
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
10/08/2004 |
|
Status: Unverified
FACT(s) (Environment):
Progress 9.1X
SYMPTOM(s):
SQL Queries taking unacceptable length of time to complete
Performance of SQL92 is slow in 9.1D comparing to 9.1B
Performance of SQL92 query having pro_element() is slow in 9.1D comparing to 9.1B
Query contains lots of statement as follows
{fn convert(pro_element("dr-address",1,1), SQL_CHAR)},
CAUSE:
The 9.1B behaviour is incorrect.
Column defined in this case is sql datatype of varchar(288)
The function "pro_element()" does not know how large each array element instance will be, so it must allow for up to max size of the entire array. The ODBC convert function does not have an associated length defined, and so it tool must provide for a max length value.
Therefore, using 288 as the size of the output of the above function is correct. Since 288 is correct, the 9.1B output size of 30 is incorrect.
Now, this query may work better in 9.1B because the actual data values in the database happen to fall within a limit of 30 chars max.
For these reasons, the current behavior is correct. The correct behavior is more costly, which sometimes happens. The solution to the cost problem should to revise the query slight so that sql knows what the expected max output size should be for this function.
FIX:
Amend sql query following way
original line
{fn convert(pro_element("dr-address",1,1), SQL_CHAR)}
new line
cast( (pro_element("dr-address",1,1)) as char(30))