Kbase P112879: "Open Query Get next .. REPOSITION..." performs slow against Oracle
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
25/01/2006 |
|
Status: Unverified
FACT(s) (Environment):
Progress 9.x
OpenEdge 10.x
Oracle DataServer
SYMPTOM(s):
"Open Query Get next .. REPOSITION..." performs slow against Oracle
"Open Query Get next... REPOSITION..." generates multiple sets of SQL statements
The extra set of SQL causes Oracle to do a full table scan
The extra set of SQL causes Oracle to do a full table scan even when statistics is up to date
CAUSE:
This is expected behavior. The "issue" here is actually caused by REPOSITION of the query. When doing REPOSITION, we have to rebuild the query result. We send a different sql to ORACLE so that we get the next records based off of the record that we repositioned to. We document the fact that REPOSITION will cause a new sql to be sent to ORACLE.
CAUSE:
Enhancement request 0000003313
FIX:
Code 4GL program differently to minimize the number of records it has to read. For example, reopen the query starting with the record wanted (instead of doing a reposition).
Or limit the result set size for his open query by setting the QUERY-TUNING(CACHE-SIZE) value. They could set the cache to the size of maybe 2-4 records and run some performance tests. It probably won't remove the table scan but it should give better performance.