We recently found that Query Builder is creating SQL that performs very poorly (full table scans) specially if ProjectLink and/or SUMA was used and wanted to know if anyone else has seen this? We think we found out why and wonder it it might be affecting commonly used customization code as well.
We are heavy users of Query Builder and have some very large clients with large DBs, One recently reported very poor performance of a large QB query and asked us to investage. We discovered that the SQL generated was doing full table scans and was taking many minutes to execute. We looked at the SQL and found that there was a union on a table called WorkItem and ProjectWorkItem. We wrote some example code using java to mimic the creation of SQL. It turns out that the ORM model of Windchill means that if includeDependencies is on the API uses this union structure in the SQL that is causing the full table scan.
Q1. Can I stop QB using the setDescendantsIncluded when it builds queries
Q2. Has anyone else seen this?
More Details.....
So for example WorkItem has a dependencies of ProjectWorkItem, therefore a simple query on the top object will by default include the sub objects. Using the API we can turn this off, when we did this our query went from minutes to seconds! As this option is by default on it occurred to us that all code could be affected when a top object has a child, we note that even for WTPart this is true if a SUMA parts exist (note: the API checks if the table is empty before including the sub object).
We do alot of legacy code recovery projects and plan to review these to update the APIs to always turn off dependencies when we do not need that data. We inherit this code so it seems common practice to not actively change this and it appears to have very negative performance impact.
