Performance tuning the content server.

I'm currently experiencing performance issues on the content server. Currently it takes me over 10 minutes to run the following DQL in iDQL on the Content Server, and it only returns 150 objects before erring out:

SELECT * FROM custom_type WHERE FOLDER('/Cabinet/Folder/A', DESCEND)

Are there indices I can set in the database to make this query run faster? How does the FOLDER predicate work?

Also, when I browse the same folder in DAM, with the page set to display 100 objects at a time, it takes over 20 minutes to get the next 100. How does DAM browse folders? Does it use DQL to get these objects? If so, what is the DQL statement?

Find more posts tagged with

Comments

ldallas

2 things to check

1) make sure the update stats job is run successfully. If necessary have the DBA run it explicitly. This may solve the problem immediately. If not or if the problem returns after a short period of time -go to step 2.

2) the folder predicate is historically fickle (not a technical term I know) The query dies at 150 probably just because the dctm session it is timing out before the result is returned. Use an explicit query to get a count of objects in the location. Then run it with the all keyowrd to get a count of all versions. I have seen processes that created excessive numbers of versions of an object (log files and the like) The performance on a folder predicate will degrade if the number of versions is way out of whack with the actual number of current versions expcted in the results. the optimizer will get tricked and force a table scan because the ratios are out of the expected range. You should also look for this condition in other locations as well. (make sure log purge jobs are running) If the optimizer t has problems - it is the cardinallity of the dm_sysobejct tables regardless of folder location and the culprit could be anywhere - I'd look in /Temp

good luck

Francois Dauberlieu

This is an historical issue. The DESCEND predicate, even though it works, has always been as slow as a slow snail from slow city in slow county.

When I need to do some DQL statement like that, if I have the choice, I do my own recursive stuff. Obviously, that can be done only in an application, not in the DQL tester in DA

CláudioGil

Actually in a documentum 6.5SP1 I can execute a query like the following in a few seconds (~3s):

> select count(*) from dm_folder where folder('/SomeCabinet', descend)| dm_attr_0001 || 12628        |

But I think the predicate is limited to 1500 folders in some queries. I believe I got an error when using the same

folder('/SomeCabinet', descend)

in an query with the SEARCH DOCUMENT CONTAINS clause.

ldallas

did you update stats? any errors - any change in behavior???

SEARCH DOCUMENT CONTAINS - is a full text clause and doesn't apply to dm_folder

select count(*) from dm_document where folder('/SomeCabinet',descend)

then compare to results from

select count(*) from dm_document all where folder('/SomeCabinet',descend)

CláudioGil

I was using a query with SEARCH DOCUMENT CONTAINS and the folder predicate:

> select * from dm_document search document contains 'testing' where folder('/SomeCabinet', descend)Error occured during query execution:[DM_QUERY2_E_TOO_MANY_FOLDERS_FOR_FTDQL]error: 
"The folder predicate in the query exceeds the maximum number of supported folders (2,500)."

This query takes ~16s.

> select count(*) from dm_document where folder('/SomeCabinet',descend)| dm_attr_0001 || 502859 |

This query also takes ~16s.

> select count(*) from dm_document (all) where folder('/SomeCabinet',descend)| dm_attr_0001 || 502867 |

(Actually I though there were much more documents with multiple versions. Nothing like measuring it up :-D)

Derrick

I have tried the update statistics job but still no change.

I am now reviewing the DFC traces and trying to set indices on the object attributes in the WHERE clauses.

Francois Dauberlieu

Just read something on the Contology forums which I had forgotten.

The guy claims that putting the Folder statement in brackets speeds it up. Can't think about why it would but the guys says it does so basically instead of:

SELECT r_object_id FROM md_user WHERE user_name='john smith' AND FOLDER('/md/system/client',DESCEND);

if you do

SELECT r_object_id FROM md_user WHERE user_name='john smith' AND (FOLDER('/md/system/client',DESCEND));

It should be faster.

Don;t shoot the messenger if it isn't.

mszurap

How deep is your object model (type custom_type from which type does inherit)?

Do you get result when you query only count(r_object_id) instead of everything (*)?

By the way dm_updateStats job is not running very well on Oracle 10GR2 (and over) databases, you should run Oracle's built in gather_schema_stats procedure to collect the statistics. Often a database reorg is (ALTER TABLE MOVE..., ALTER INDEX MOVE...) is recommended on huge and fragmented tables and indices (this process needs a statistics collection afterwards).

Derrick

The custom types are all 4 levels deep:

dm_sysobject - dm_folder - vf_folder - vf_title

dm_sysobject - dm_document - vd_document - vd_version

dm_sysobject - dm_document - vd_document - vd_episode_version

I opened IDQL on the content server box and ran the following query:

SELECT COUNT(r_object_id) FROM vf_title WHERE FOLDER('/Longform Video Content S4M/By Title/A', DESCEND)

It returned the count being 5215 objects, and the query took 43 seconds to execute. Since SQL Server would take less than 1 second to execute a query that would return the same number of records, am I wrong in assuming this to be too long?

As for the update statistics job, we are using Microsoft SQL Server here, not Oracle.

Francois Dauberlieu

Did you test my suggestion from my previous post ? Do you see any difference ?

Derrick

Tried it and still no luck. I'm guessing it's the SQL that's generated from the FOLDER predicate. I encapsulated the FOLDER predicate in () and still the query took 43.422s to run.

I then tried the following as an alternative to using the FOLDER predicate:

select r_object_id from vf_title where i_cabinet_id = '0c0dea2780086368'

This query took 22.812s to execute.

I then tried to put an index on i_cabinet_id but was returned an error.

I then tried the following query:

select count(r_object_id) from vf_title where i_cabinet_id='0c0dea2780086368'

This query took 5.860s.

What is the maximum performance I can expect for simple DQL SELECT queries?

BTW, given the following trace:

136.344 12.438 N/A [main] com.documentum.dmcl.impl.DmclApiNativeAdapter@652110.eval("readquery,c,select max(v_universal_id) as universal_id from vf_title where i_cabinet_id = '0c0dea2780086368'","") ==> "q0"

I'm using the 2nd column as the time in seconds. Am I reading this trace correctly?

ldallas

question - what is the response time from

SELECT COUNT(r_object_id) FROM dm_sysobject WHERE FOLDER('/Longform Video Content S4M/By Title/A', DESCEND) and r_object_type = 'vf_title'

please clarify from your orignal post - is the response time different between webtop and dam when browsing the same folder?

Derrick

I am not using webtop. I test from DAM and then using the API command line on the content server.

I ran the query and this is what the trace shows:

70.282 21.172 N/A [main] com.documentum.dmcl.impl.DmclApiNativeAdapter@879860.eval("readquery,c,select count(r_object_id) from dm_sysobject where folder('/Longform Video Content S4M1/By Title/A', descend) and r_object_type = 'vf_title'","") ==> "q0"

I assume this means the query took 21.172s, right?

robineast

What is the maximum performance I can expect for simple DQL SELECT queries?

DQL select queries (at least ones without FTDQL clauses) are simply translated into SQL, executed against the database and the results returned to the user. In just about every case I have come across, the cause is that the SQL is not executed efficiently by the database (I am currently dealing with an example where this is not the case but that really is an exception). So you probably need to work out what the sql being executed is - using the 'show sql' option in DA is the easiest way - and then look at database tuning options for the query.

I'm using the 2nd column as the time in seconds.  Am I reading this trace correctly?

Yes, although that is not necessarily the complete time for retrieving all the rows. In DFC terms a query consists of a query statement (readquery, execquery, query, etc), followed by 1 or more next statements. The complete 'cost' of the query is the time it takes to execute the query and next statements.

Derrick

What we did was type the DQL syntax into the DQL tester in DA, and used the show SQL checkbox to see the SQL converted. We ran that SQL statement against MS SQL Server and it was fast (under 1s).

So I think I am also in that rare case as you are working on...

What about the dfc.dqlhints.xml file? Can I add that to my content server box to speed things up?

Derrick

Recently, I took the time to examine my object model hierarchy, as I read in a knowledge sharing article that the depth of the hierarchy affects DQL performance. ie performance will degrade considerably if the inheritance level of an object being queried is 4 levels deep: dm_sysobject -> dm_document -> vd_document -> vd_episode_version.

Based on this, I decided to use Composer to change my object hierarchy to:

dm_sysobject -> dm_document -> vd_episode_version

However, the same DQL now takes longer than when the inheritance was 4 levels deep.

Is this expected behaviour? I thought that with less inheritance, the SQL generated from the DQL would have less tables to join against and therefore reduce the query execution time...