Kbase P11697: Bad or poor performance on query with two INNER JOINs
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
20/10/2009 |
|
Status: Verified
SYMPTOM(s):
Bad or poor performance on query with two INNER JOINs
Index is not used when running SQL-92 query containing Inner joins even after running UPDATE STATISTICS
Example of a query that shows the issue:
SELECT pub.obasic."case-no", pub.obasic."ord-no",
pub.obasic."start-date", pub.obasic."start-time", pub.obasic."pf-dept",
pub.epiordidx.episode_id, pub.epiordidx.grp_id
FROM pub.episode
INNER JOIN pub.epiordidx
ON pub.epiordidx.episode_id = pub.episode.episode_id
INNER JOIN pub.obasic
ON pub.obasic."case-no" = pub.epiordidx.case_no AND
pub.obasic."ord-no" = pub.epiordidx.ord_no
WHERE pub.episode.episode_id = 2
Table scan of obasic is made, primary index case-no,ord-no is not used
FACT(s) (Environment):
UPDATE STATISTICS has been executed before running the Query
With version prior to 9.1D, after running UPDATE STATISTICS, the related Index is used
All Supported Operating Systems
Progress 9.1E
OpenEdge 10.x
CAUSE:
Missing the right database change to make use of related index
FIX:
Run the following commands:
UPDATE INDEX STATISTICS;
COMMIT;