CMS Snapshot Analysis - Run reference pattern - Create source set - Performance issue

This page provides the approach to troubleshoot the performance issues you may face during the Create source set step that occurs during Run reference pattern.

1. Close CAST-MS.
2. Run a CSSOptimize on your Local database. Refer to the page CAST Storage Service - Maintenance activities, section Optimizing CAST schemas.
3. Open CAST-MS and run the Reference pattern again.
4. The create source set step is a set of queries. If the performance issue still exists even after having optimized the database server, identify the SQL query that has poor performances.
5. Close CAST-MS.
6. Open CAST-MS with SQL Traces. Refer to the page How to launch CAST-MS in SHOW SQL mode in order to get the SQL traces
7. Run manually the Reference pattern dependency / search string.
8. Open CAST-MS log file that is in CAST-MS log file location
9. Identify the query that has performance issue :
   1. Check in the CAST-MS log file the biggest gap between the beginning and the end of a query execution as shown in the below screenshot :
      
      In the above example the beginning of the query starts at 15:55:28 and ends at 03:27:55 the day after. It took 692 minutes (10 hours and 32 minutes).

   2. Retrieve the query that takes time and run it on your local database. But, before running it, you must replace the "?" by their values. Refer to the below screenshot as an example :
      

      In the above example, the query will be:

      DELETE From TKB_SUPP_LOCAL.UsrProJob WHERE IdUsrPro <> 402 AND IdJob = 403

   3. Run the query to confirm that it is the one that has performance issue.
10. There is no temporary solution for this as the queries are run by CAST MS. Contact CAST Technical Support with the Relevant Input in order to reproduce the issue and to permanently fix the performance issue.