CODEWITHSUNDEEP
dqlwdkdocumentumwebtop
Govan
Govan

Reputation: 2129

long running query in documentum application

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?

Upvotes: 1

Views: 1511

Answers (2)

Sergi
Sergi

Reputation: 1015

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 (...))
    )

Upvotes: 0

Zlelik
Zlelik

Reputation: 570

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:

  1. Optimize query on database level and test it from DQL (dql Tester in DA and etc)
  2. Decompile class LifecycleDataHandlerHelper and rewrite DQL query in other manner. Try to add hints like FORCE_ORDER or something else.
  3. 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.
  4. 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.

Upvotes: 1

Related Questions