Kbase P95534: How to overcome performance problem due to an additional: ORDER BY progress_recid
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
16/10/2008 |
|
Status: Unverified
GOAL:
How to overcome performance problem due to an additional: ORDER BY progress_recid
GOAL:
What to do if progress_recid column is missing from an Oracle non unique index
FACT(s) (Environment):
Oracle DataServer
FIX:
An SQL statement within dataserv.lg shows an "ORDER BY progress_recid" and the usage of an index without the progress_recid column. This triggers a sorting responsible for bad performance.
Adding the progress_recid to the Oracle index is one solution, modifying the 4GL statement can be another solution.
Find below a sample showing a modified 4GL statement to overcome performance problem due to the additional: ORDER BY progress_recid.
The table "individual" has a non unique Oracle index "Index1" on the two columns "name", "state".
The 4GL statement
FOR FIRST individual WHERE name= "smith" AND state = "US":
generates the SQL Statement
SELECT /*+ INDEX_ASC(T0 INDIVIDUAL##Index1) */ PROGRESS_RECID unique_id_0,....
FROM INDIVIDUAL T0 WHERE (U##NAME = upper(:1) AND U##STATE = upper(:2))
ORDER BY U##NAME, U##STATE, unique_id_0
To overcome performance problem, have the following 4GL statement
FOR FIRST individual WHERE name= "smith" AND state = "US" BY name BY state:
It is to be noted that there are limits to this workaround. The progress_recid field provides uniqueness that allows a predictable ordering to records retrieved by FINDs and QUERYs. Without the progress_recid an attempt to randomly access a specific record in a non-unique result bracket is not possible. Also, the order in which results in a specific bracket are scrolled is unpredictable. If the application does not require random access for its non-unique results or a specific ordering to the results, then the progress_recid is not needed.