Speed up the data retrieval from Documentum via DQL

Question

I am doing a Java project connected to Documentum and I need to retrieve data from object table. The thing is when I retrieve from 1 table I can get the answeres in max 2 seconds for each of the following tables with the following DQLs:

SELECT * FROM cosec_general

and

SELECT * FROM dm_dbo.cosec_general_view

however once I want to join those two tables together in retrieve from the result it takes 5 min to do so. Is there any way that I can make it faster? Here is the DQL that I use to join them I get teh columns that I need:

SELECT dm_dbo.cosec_general_view.name, dm_dbo.cosec_general_view.comp_id, 
dm_dbo.cosec_general_view.bg_name, dm_dbo.cosec_general_view.incorporation_date, 
dm_dbo.cosec_general_view.status, dm_dbo.cosec_general_view.country_name, 
cosec_general.acl_domain, cosec_general.acl_name 
FROM dm_dbo.cosec_general_view, cosec_general

@Miki Why do u think so ? :) I can use it to retrieve data successfully though — Danial Kosarifa, Sep 13 '16 at 05:45
you are doing something wrong. try elaborating your question a bit more. show more code around your queries — Miki, Sep 13 '16 at 05:45
if your query is complete than what you have wrote isn't join, or more exactly, it's full outer join. how big are tables/object types in your repository? — Miki, Sep 13 '16 at 05:48
@Miki oh I meant that they join successfully but when I test it in DQL tester it takes me 5 min to retrieve all the data .( The only problem is the time that I want to reduce ) . It's quite big . The results should be around 190 000 lines :D — Danial Kosarifa, Sep 13 '16 at 05:52
I'm asking you: is there a lot of data in those table. Because if it is, then it's logical you need a lot of time to retrieve it. What DQL tester you use? Does your Documentum server or DB server have enough resource to deal with your queries? Your question is very broad, please narrow it describing it in more details — Miki, Sep 13 '16 at 05:54
well you said it all. since you are using term "DQL tester" I beleive you mean Repoint od DQMan. Both are old and unsupported java applications that need to load and display retrieved data. It's expected that you need that much time for it. — Miki, Sep 13 '16 at 05:56

score 1 · Accepted Answer · answered Sep 13 '16 at 14:09

1

There is no condition on which fields you are trying to join, Add WHERE clause containing condition for join, like WHERE dm_dbo.cosec_general_view.field_1=cosec_general.field_2

answered Sep 13 '16 at 14:09

Sergi

990
5
16

Thanks for your answer I had already figured that mistake out :D – Danial Kosarifa Sep 14 '16 at 02:37

score 0 · Answer 2 · answered Sep 13 '16 at 05:45

0

You are using wrong approach. In query

SELECT * FROM cosec_general

the asterisk * means return me everything. Once you loaded information to the memory object manipulation with it should be measured in milliseconds.

answered Sep 13 '16 at 05:45

Miki

2,493
2
27
39

Speed up the data retrieval from Documentum via DQL

2 Answers2