Fix Performance Issues with CAD Integrator querying entire triSpace BO.

Case number

TS006473970

The team is facing performance issues when using CAD Integrator, specifically when they use the "Child Records" functionality within CI. When they run Child Records, it will typically take anywhere from 10 - 60 minutes to generate a list of spaces within the tool, and sometimes even time/error out altogether. With the number of CAD users we have, and the number of changes they make on a daily basis, this performance is not acceptable or sustainable. I would like to outline what we have tried so far, what we have learned, and hopefully get some insight from you or someone else at IBM that has deep knowledge of CAD Integrator.

*** What we tried ***
1. Platform CI Logging - We tried turning on CI Logging within the Platform Logging in the admin console, and then had a CAD user run Child Records, this did not help, as the log is only telling us that CI called the Child Records class, and another line when it finished.

2. CI Logging - We tried turning on debug logging within CI, this also was not helpful and more or less showed the same thing we saw in the initial CI Platform Logging mentioned above.

3. Platform SQL Logging - We next turned on SQL Logging in an isolated environment, and again had the CAD resource run Child Records, this was quite helpful. With this logging we were able to see what we feel is the root cause of the performance issues.

*** What we found ***
Within the log we were able to see that the query above was being run over 2000 times for one execution of Child Records, changing the list of SPEC_IDs for each execution. The SPEC_ID's you see in the SQL Where Clause are not even spaces within the floor the CAD Resource had open, which led us to realize that CI is trying to identify the Child Records of the floor by querying all spaces in TRIRIGA, and then filtering the result set for the floor in question. As you can imagine, with a portfolio as large as Google's, this ends up causing a massive performance hit, simply due to the volume of data.

*** In attempts to alleviate the issue, we have tried the following: ***
1. Added DB Indexing to help the CI - All Spaces query used by the triSpace CAD Mapping to retrieve spaces for CI to use. This helped the SQL query that gets the spaces run faster, but it did not affect Child Records performance from CI, as CI was still having to parse through the vast number of spaces to try to identify the child records for the floor.

2. Added filtering on the CI - All Spaces query. We filtered the CI - All Spaces query to return only a subset of the portfolio, around 25% of the entire normal record count. This sped up child records significantly, and we saw in SQL Platform Logging, the SQL mentioned in finding #3 was reduced to around 600 rows, instead of 2000+, which seems to support our assumption that CI is getting all Spaces before trying to filter the Spaces for the Floor in question.

3. Through the SQL logging we were able to see CI is trying to use the CAD_ATTACH_RECORD table, filtering with SPEC_IDs from Space records. We suspect perhaps this table is used to house all records in TRIRIGA that are attached to a Polyline in CAD, but after additional testng we found that to not always be true.

*** What we would like to get from IBM is any of the following: ***
1. Confirmation of our suspicion in terms of how Child Records is executed from CI. Information for any other TRIRIGA users that have faced similar performance issues with larger portfolios.
2. Recommendations on how to alleviate the performance issue. We do not currently have the capacity or desire to modify the CI source code, if that is even an option, perhaps IBM has someone on staff that can assist? Is there a planned enhancement to CI to address this issue systematically?
3. Confirmation of how CI is determining what spaces are attached. In lieu of fixing CI, we have also thought about trying to build something within the TRIRIGA Application to mimic the Child Spaces functionality, but as previously mentioned we feel we are still missing something between CI getting all spaces and comparing them to the CAD_ATTACH_RECORD table.

After meeting with the IBM/Wi-Pro team, they agreed this is an issue with how CAD Integrator is written, and suggested we file this RFE so that it may be remediated.