long running query in documentum application

Question

When retrieving objects in our Documentum application it takes a long time. We have activated long running query option in data source och, but have found that the below query is taking too much time:

select all 
  b.r_object_id, dm_repeating1_0.state_name, a.object_name 
from 
  dm_policy_sp  a, 
  dm_sysobject_sp  b, 
  dm_policy_rp dm_repeating1_0 
where 
    (
      (a.r_object_id=b.r_policy_id) 
      and (dm_repeating1_0.i_state_no=b.r_current_state) 
      and b.r_object_id in (N'a long, long list of IDs') 
      or a.r_object_id in (N'a long, long list of IDs')
    ) 
    and /* ...  */

As you can see, the table "a" is a policy table and it has only 7 records. In the SQL statement after both "or" operators, we are looking for an object_id between 100 objects in table "a"! We executed a query and searched for those objects in table "b" (systemObjects) and we found that those objects belong to table b!

The above query takes about 17 minutes. When we changed the name of table after "or" operator in table to b, it took only 10 seconds!

We suppose this query is wrong. We don't know if it is a bug in Documentum or we have configured Documentum wrong. We don't know where we can find the DQL which creates this SQL or related components? Any idea?

Are you sure the incorrectly nested `and` / `or` contstruction is intentional? — Tomalak, Sep 08 '11 at 15:12
No I'm not. I can not find the related dql code! But the list of objects are no longer than 100, if the number of objects are more than 100 there will be another or looking for objects in table "a"! — Govan, Sep 08 '11 at 15:19
First thing, try placing parentheses like this: `and (b.r_object_id in (...) or a.r_object_id in (...))` because the way it currently is is most certainly wrong. — Tomalak, Sep 08 '11 at 15:21
Yeah. We tried that. It work with parentheses. The problem is that it is Documentum which generates this sql. It not helps to modify the sql, we have to find the related DQLor configuration in Documentum. — Govan, Sep 08 '11 at 15:40
@Tomalak - I'm with you about the `and`/`or` thing - always specify parenthesis when mixing those operators (just for clarity and sanity). Also, thank you for qualifying your joined columns (and using aliases); however, you may wish to consider using explicit joins, to prevent future maintainers from _not_ doing so. (Lastly, can we get the remainder of your query - after the final `and` - because that may be significant...) — Clockwork-Muse, Sep 08 '11 at 15:44
@Govan: I see. I have no idea how Documentum works, so I can't really help you on this one. Good luck! — Tomalak, Sep 08 '11 at 17:07
You should activate the dcml trace for your application. There you should see the dql which produced this query. — David Pierre, Sep 13 '11 at 12:25
Thank you @David! Do you know how can I activate it or maybe you know a link about working with dcml trace? — Govan, Sep 17 '11 at 10:58
@Govan use IDfSession#traceDmcl(int level, String logFile), level is between 0 (no trace) to 10 (max), logFile is simply a path to desired logFile — David Pierre, Sep 19 '11 at 09:08
Another option is to file a support case with EMC using Powerlink, and have an engineer take a look. You could also try posting this to EMC Developer Network (https://community.emc.com/community/edn) to see if you get any hints there. — Brendan Hannemann, Oct 13 '11 at 16:55

score 1 · Answer 1 · answered Oct 31 '13 at 11:51

Looks like documentum does it inside LifecycleNameDataHandler and LifecycleDataHandlerHelper. I decompile these classes and found this DQL query

SELECT b.r_object_id, a.state_name, a.object_name FROM dm_policy(all) a, dm_sysobject(all) b WHERE b.r_object_id IN (...) AND a.r_object_id = b.r_policy_id AND a.i_state_no = b.r_current_state ENABLE(row_based)

Documentum Webtop execute this DQL query when user open any datagrid with LifeCycle state name column.

There are a few option:

Optimize query on database level and test it from DQL (dql Tester in DA and etc)
Decompile class LifecycleDataHandlerHelper and rewrite DQL query in other manner. Try to add hints like FORCE_ORDER or something else.
If you do not use Life Cycles at all, you can easy disable this class. in the file webcomponent\app.xml line com.documentum.webcomponent.library.applylifecycle.LifecycleNameDataHandler should be commented or disabled.
Delete Life Cycle State name (or State Name) from grids. Maybe users select this column in their customized grids. It is possible to advice users to delete this columns from the grids.

Thank you for your answer. I had this problem for two years ago and I am not working with documentum any more! — Govan, Nov 12 '13 at 08:37

score 0 · Answer 2 · answered Nov 06 '13 at 09:39

I don't know what exactly you want to retrieve by this query, but I think that your query might be reworked as follows:

select all 
  b.r_object_id, dm_repeating1_0.state_name, a.object_name 
from 
  dm_policy_sp  a, 
  dm_sysobject_sp  b, 
  dm_policy_rp dm_repeating1_0 
where 
    (
      (a.r_object_id=b.r_policy_id) 
       AND dm_repeating1_0.r_object_id=a.r_object_id
      and (dm_repeating1_0.i_state_no=b.r_current_state) 
      and (b.r_object_id in (...) 
      or a.r_object_id in (...))
    )

Sorry, I was innatentive. As far as I understood you need to find the place where such a DQL is created. Do you use Webtop? And if so in what component this query is executed? — Sergi, Nov 06 '13 at 10:15

long running query in documentum application

2 Answers2