title: Documentum 6.7 DQL to SQL translation much slower compared to Documentum 6.5
body: <p>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.</p>
  <h3>Example of the DQL that now performance bad on 6.7:</h3>
  <pre><code>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
  </code></pre>
  <p>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:</p>
  <ul>
  <li>SQL generated by D6.5 DQL run on 6.5 SQLServer = 2.5 seconds</li>
  <li>SQL generated by D6.7 DQL run on 6.7 SQLServer = 368 seconds</li>
  <li>SQL generated by D6.5 DQL run on 6.7 SQLServer = 2.5 seconds or less</li>
  <li>SQL generated by D6.7 DQL run on 6.5 SQLServer = 368 seconds or more</li>
  </ul>
  <p>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</p>
  <p>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.</p>
  <p>Did anyone experience similar problems?
  And knows a fix or a magic configuration parameter that I overlooked?</p>
  <p>Thanks</p>
  <p>Jacques &quot;Plafond&quot;</p>