DQL: enable (return_top 10) performance impact

Question

I had this query that originally caused massive timeouts:

select d.r_object_id 
from isc_fiche  d, dmr_con c 
where any c.parent_id = d.r_object_id 
group by d.r_object_id 
having count(*) > 2 

yet when I add the enable (return_top 10) to the end then the performance issues seem a thing off the past. Apparently (according to colleagues) that statement could have a perfomance-improving effect.

Could someone clarify this for me?

Full query with 'way' better performance:

select d.r_object_id 
from isc_fiche  d, dmr_con c 
where any c.parent_id = d.r_object_id 
group by d.r_object_id 
having count(*) > 2 
enable(return_top 10)

Answer 1

enable (return_top 10) modifies the executed SQL statement, it adds a limiting clause to it, like ROWNUM <= 10 in Oracle. It depends on the underlying RDBMS, so I guess it's not ROWNUM <= 10 if you use EMC Documentum with Microsoft SQL Server.

You can run DQLs on the web interface of ECM Documentum (if I remember correctly it's called Webtop). There is a checkbox on the DQL run page which shows the generated SQL. You should check there what's the difference between the two DQL query.