Why does my RTC query take so long to run?

Authors: StephanieBagot
Build basis: CLM 4.x

This situation will help to determine why long running queries occur when using IBM Rational Team Concert (RTC).

When I run a query, what is actually happening?

All Rational Team Concert artifacts, such as work items, plans, and source control artifacts, are indexed upon creation. The following queries will run against the Lucene Indices:
1. "full text" condition in the query
2. The left bottom corner search box in rich client for quick search
3. The right top corner search box in web client for quick search

All other queries (like regular query condition) will go against the application database directly.
For more information on how full text queries are formed, review the article "Getting the most out of full text search".

Symptoms and impact

Below are some additional questions to ask when troubleshooting query performance:

• Do the queries run for a long time and eventually complete? Or do they run for a long time and then fail?
• Are you including multiple custom attributes in your query?
• Is the slow query response time related to only some queries, or all queries?

Initial assessment

1. Try to run the query from both the Web UI and Eclipse client to determine if the behavior is the same.
2. Try to run multiple queries to see if the behavior is the same across all queries.

Possible causes and solutions

Rebuilding the indices

In some cases, the indices used for queries become fragmented or grow too large for quick searching capabilities. In this case, you should run the following repotools commands to Rebuild the Text Indices, and Reindex the Triple Store and Lucene Indices.

The Jazz Team Server manages a directory outside the database that stores all data that is necessary to process full text queries. Below is the relevant command to rebuild them:

cd <location of Jazz Team Server Install>/server/
./repotools-<app>.sh -rebuildTextIndices teamserver.properties=<location of teamserver.properties file> 

The queries will also use triple store and Lucene text store indexes. Below is the relevant command to rebuild them:

cd <location of Jazz Team Server Install>/server/
./repotools-<app>.sh -reindex teamserver.properties=<location of teamserver.properties file> scope=all

Custom attributes

If multiple custom attributes are being queried, this may slow down the application if the attributes are not synchronized with existing work items. Synchronize the work items to ensure that this is not contributing to the performance degradation.

Database performance

Since queries (besides fulltext) run against the application database, it is important to ensure you have no performance issues with the database that can be contributing to the overall performance of your queries. Review the article: How to Determine if my Database has a problem for more information on troubleshooting.

Complex queries

Queries will often return multiple rows of data. For example, a user may want to see all of the work items that are in an open state for which they are the owner. This makes the generation of the appropriate SQL to retrieve data more complex, and can often return more data than a simple query for a single work item.

Malformed and non-optimal SQL queries

Malformed and non-optimal SQL queries can cause an increase in database response times which may impact performance.

The Jazz applications are driven by user actions, and they use a dynamic query building engine to build specific queries based on the user actions and requests. Certain user requests to the Jazz application can cause this dynamic query engine to produce queries that may not be optimal. The queries may not be well scoped, and may retrieve more data than is needed. An administrator should note the time when these spikes in database response times occur, and then check for a corresponding event or events within the database specific metrics.

In these cases, the simple indexing of particular database tables within the Jazz repository can address the performance issue. Use the database specific metrics and diagnostics to help determine when these conditions are being encountered, and when this can help address performance issues.

Known issues

• Queries on newly added data slower after upgrading to Rational Team Concert 4.0.1
  Although queries may keep responding when only dealing with pre migration data, queries on newly added data may either take a significantly longer time or even time out. See Upgrades to 4.0.1 create a significantly larger, incorrect index for more information.

Related topics:

• Still need help troubleshooting your performance issue? Refer to Performance Troubleshooting for additional topics.

External Links:

• None

Additional contributors: None

Questions and comments:

Warning: Can't find topic Deployment.WhyDoesMyQueryTakeSoLongComments