Kbase P170716: SELECT DISTINCT is creating a large temporary table file.
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
12/08/2010 |
|
Status: Unverified
SYMPTOM(s):
SELECT DISTINCT is creating a large temporary table file.
SELECT DISTINCT is taking 90 minutes to select some 4000 rows of two fields each from a 25 million record table without where conditions.
The same query without the DISTINCT takes 45 minutes to return 25 million rows
Both queries described above are doing a whole table scan.
FACT(s) (Environment):
IBM AIX
OpenEdge 10.2A
All Supported Operating Systems
OpenEdge 10.2A
CAUSE:
This is expected behavior. The whole table needs to be dumped into the temp table database file for sorting. Hence the large file of the temp-table database. Also, since there is no index combining the two fields involved, the times for the query resolution above are reasonable given the size of the table. The fact that the DISTINCT version takes twice the time to execute is due to the extra sorting activities required by the DISTINCT option
FIX:
Define an INDEX on the two fields being selected or use the GROUP BY and ORDER BY options to get the same result faster. Tests show using GROUP BY and ORDER BY is far more efficient than using the DISTINCT option. In the above tests, using GROUP BY and ORDER BY returned the same result set as that returned by the DISTINCT option in 8 minutes.