Comments (6)
  • Add a Comment
  • Edit
  • More Actions v
  • Quarantine this Entry

1 lancemorris commented Permalink

The relationship where clause for doclinks provided out of the box for supported objects can be optimized, especially for work order. The time to retrieve and display a work order was taking almost 2 minutes post upgrade to 7. We traced the performance down to the doclinks query. We changed this relationship query from numerous OR statements with values in numerous lists to a union statement and the time to retrieve and display a work order dropped from almost 2 minutes less than 2 seconds.

2 Allen.Dean commented Permalink

Thanks @lancemorris. It looks like your solution was similar to what is posted in the original blog entry.

3 lancemorris commented Permalink

Yes. The original post simplified the query by removing any objects that were not needed. In our case, we did not remove any but replaced the "or in" clauses with "union all" clauses which makes the query run much more efficiently.

4 Bruno Portaluri commented Permalink

I have greatly improved performances of docklinks queries refactoring the SQL query using UNION clauses instead of OR clauses as Lance has already described. <br /> Check out <a href="">this post</a> for details.

5 PamDenny commented Permalink

Bruno - Excellent! Thank you for supplying this additional information!

6 JulioHernandez commented Permalink

For DocLinks for WOs and SRs, the performance can be further improved by adding an index on the RelatedRecord table for columns RelatedRecKey and RelatedRecClass.