aggregate function queries and order by queries

[Former Member]

select max(p_retailprice), min(p_retailprice) from part

This query takes very long tiime to execute. PART table is of the TPCH benchmark with cardinality 200000.

moreover even the query

select p_retailprice from dbo.part ORDER BY p_retailprice ASC

is taking very long time to execute.

The execution time is of the order of 30-60 mins.

EnableMinMaxOptimization is set to YES.

Kindly suggest if any parameter can increase the efficiency of the query.

[markus_doehr2 · Accepted solution]

Try setting

READAHEAD_TABLE_THRESHOLD to e. g. 500

and retry the query.

Markus

[lbreddemann]

Hi there,

while the approach to enable prefetching (READAHEAD_TABLE_THRESHOLD >0) may solve this issue for MaxDBs with version >7.6.05 (but not for 7.7 !) the much more effective solution would be to create an appropriate index.

Just create an index on p_retailprice and both the min/max optimization will work as well as the order by will be much quicker.

See, the min/max optimization makes use of the B*TREE structure to find the left/right most outer value.

The same goes for the order by. Without an Index a temporary file needs to be created, all data read and sorted and finally the temp. file is read for the output.

best regards,

Lars