This page provides assistance with Troubleshooting reference finder performance issues when the reference finder takes a very long time to complete the task of executing.

Reference Finder is configured in the dependency tab of CAST Management Studio (CMS).  More information on this functionality can be seen in the documentation here:

CAST 8.3.X: CMS - Dependencies - using Reference Patterns

Step by Step scenario

• Run Analysis
• Create reference pattern
• Run Reference pattern

Action Plan

There are six main reasons why the dependencies reference pattern could have performance issues :

1. The Regular Expression that has been configured in the Reference Pattern is gathering too much entries.
   1. Replace the regular expression by a specific pattern you are trying to search. If the hit is fast, it means that the performance issue is due to the regular expression.
   2. Replace the regular expression to get the more specific RegExp.
2. During the process of the Reference Pattern dependencies, the Engine is splitting the source code into multiple section : comments, source code, embeded SQL and string literal. To test that the performance issue is due to the splitting, in the Reference Pattern configuration, check all the search in option and run the run reference pattern dependencies, then, since the reference pattern will hit all the source code body, the engine will not split the source code into code, comments string literals and embedded sql.
   
3. If to many warnings / Error messages (thousands of messages) are logged in the log file it can lead to performance issue :
   
   1. The warning messages must be adressed.
4. In the Dependencies tab, the source objects. If the scope of the source is a technology, you should replace it by Analysis Units. This will reduce the number of objects that will be processed by the Reference Finder.
5. The Refine Target. In the dependencies, you must limit the refined target to the expected target. For instance, it you know that links must point to specific object type, configure the refine target by filtering only the object type, etc...
6. The Database Server : You must check that your Database Server has no performance issues.
7. The queries that are executed during the Reference Finder. Identify the queries that are longer by following the section Determine the longest query being done and send the list of longest queries to CAST Technical Support.

If none of the above points help you, contact CAST Technical Support and provide the Relevant Input in order to reproduce the issue.

Relevant input

CAST Support Tool (CST) - alias Sherlock (with the option of source code)

• Screenshot showing the issue and the reference pattern settings
• Source code  - required for further investigation of performance issues by support and R&D
• The sql logs showing the longest query being done 

Determine the longest query being done

• Activate SQL traces for Reference Pattern - this needs to be done at the database level
  • For CSS, see the page:  RDBMS Information - PostgreSQL - How to log Postgres SQL queries
• Run the Reference Pattern in CAST MS that is taking a long time (if it takes a very very long time, the process can be stopped after a few hours as the log should contain the query currently running)
• Examine the logs to determine the longest query
• Provide the logs and other information seen on the lengthy query to support. This is to provide the information to R&D for further investigation and remediation.
  

Ticket # 6019