1

Recently I upgraded my Documentum system from Documentum 6.5 to Documentum 6.7 on a MS SQLServer database. For 6.5 we used 32bit SQLServer and move to 64bit SQLServer for 6.7. Since the upgrade I am facing very bad performance for DQL statements that almost run instantly on 6.5.

Example of the DQL that now performance bad on 6.7:

SELECT r_object_id, object_name, title, keywords 
  FROM dm_folder
 WHERE object_name LIKE 'MOC -10-%'
  AND NOT r_object_id IN
(SELECT i_folder_id FROM dm_document WHERE ANY i_folder_id = dm_folder.r_object_id AND     ANY keywords IS NOT NULLSTRING)
 ORDER BY 1 DESC

Doing some research and tracing, I learned that the SQL statements generated by Documentum 6.7 are much slower and can't be much improved using SQL Query Analyser. I have queries that run in less than 3 seconds on D6.5 but take 368 seconds on D6.7! When I grab the SQL generated on D6.7 DQL and past it into SQLServer running the D6.5 system the performance is also bad. It does learn me that the underlying datamodel is unchanged because I dont get errors on the D6.5 SQL database. Grabbing the D6.5 generated SQL and pasting it onto the D6.7 SQLServer gives the same (or even a little bit better) performance than the 6.5 query. To make sure it's not to confusing:

  • SQL generated by D6.5 DQL run on 6.5 SQLServer = 2.5 seconds
  • SQL generated by D6.7 DQL run on 6.7 SQLServer = 368 seconds
  • SQL generated by D6.5 DQL run on 6.7 SQLServer = 2.5 seconds or less
  • SQL generated by D6.7 DQL run on 6.5 SQLServer = 368 seconds or more

Documentum has a performance white paper for D7CS which recommends to the following parameter when using MS SQLServer: DM_LEFT_OUTER_JOIN_FOR_ACL=T

In the CS6.7 documentation I found this parameter too, setting it in the environment vars and restarting the server gives some improvements but the result is still too slow. Still the generated SQL's cant be improved much by the SQL tuning tools, in my opinion too many OR statements in the generated SQL.

Did anyone experience similar problems? And knows a fix or a magic configuration parameter that I overlooked?

Thanks

Jacques "Plafond"

Community
  • 1
  • 1
Jacques Plafond
  • 11
  • 2
  • 1
    Could you please share with us the generated SQL from D6.5 and D6.7? Have you generated the same indexes on both SQL servers? Also, be aware that the DQL you've written is extremely expensive, using two repeating attributes (ANY keyword), as well as using columns from the outer query in the inner one. Is there another practical approach, say, using two or three queries instead of one? – eivamu Mar 18 '14 at 22:34
  • Agree that we need to see the generated DQL because you have already done a lot of research yourself here. You may also want to post this on http://developer.emc.com. – Brendan Hannemann Mar 25 '14 at 15:48

2 Answers2

1

When you still have access to the older and newer Documentum systems, you can get the SQL translation of your query (via tools like Delilah for Documentum) and you can compare if the generated SQL has changed much.

Did you convert your Documentum database to Unicode? that would multiply your data by 2 and have impact on performance.

Rob de Leeuw
  • 21
  • 4
0

The subselect (SELECT i_folder_id FROM dm_document WHERE...) with the ANY is going to be a problem. Can you rewrite that so you're not forcing SQL Server to handle that?

ScottJShea
  • 7,041
  • 11
  • 44
  • 67
Content Source
  • 1