1

Identify the inefficient SQL statement

Use the Analyzing-AR-System-Log-Analyzer-output topic or database tools to identify the inefficient SQLs that are impacting performance.

Identify the expected times and the actual response time.

Note that some SQLs, such as running complex reports, are expected to take time to complete. Focus your analysis on SQL statements that are expected to return quickly but cause a delay.

2

Check if there is an obvious problem with the query

Look for obvious problems with the query, such as:

1. No Where clause or a Where clause that only includes non-field values, such as where 1=1 or where ' '=' '.
2. The Where clause includes only statements that would not use an index, such as <>, != , IS NULL, IS NOT NULL.
3. The Where clause includes only qualifications that are Not Selective. In other words, would not match more than 10% of the rows in the table. For example, Where C7>=0, or Where C536870924 LIKE '%A'.
4. The Where clause includes only qualifications with leading wildcards, such as Where C536870924 LIKE '%A'.
5. The Where clause only includes any of the qualifications listed in points 1 - 4.
   

Note that all of these examples are fine as long as there are other components in the Where clause that would use an index.

If any of these obvious problems exist with the SQL in question, you may have one of the following problems:

• A user performed an inefficient query and should be educated on creating more efficient queries.
• Custom workflow or qualifications (such as table fields) caused the poor qualification. The customization should be modified to generate more efficient SQL.
  For example, add a more specific qualification to the query such as Where C7=2 OR C7=3
• Out-of-the-box workflow or qualifications are causing the problem. Continue to the next step to create a support case.

3

Determine if an index could be used but does not exist

Check to see if the SQL statement includes qualifications that are likely to use an index, such as:

• C7=1
• C7>9
• C536870925="Bob"
• C536870925 LIKE "Bob%"
• In some cases, IS NULL will use an index if a small number of records match the qualification, such as where C18095 IS NULL

If there a qualification that you suspect should use an index but appears not to be, check the form properties to ensure that an index exists.

In most cases, to use the index, it should be present in the first column/field of a composite index.

If you believe that an index does not exist but should exist, create a support case.

4

Determine the origination of the SQL

5

Determine if User Behavior can improve the situation

If the SQL resulted from a customer query, identify if the problem can be resolved with user behavior.

For example, a user looking for 'All Open' cases may not be able to get a fast result because an index is not used. Narrow the scope of the query such as searching for 'All My Open' cases.

6

Identify workflow that caused the inefficient SQL

If you can identify the workflow, table field, or other reason for the query, create a support case so that BMC can provide a short and a long-term solution.

7

Find a solution

a. Use the following table to troubleshoot specific problems with slow SQL statements.

b. If the cause has not been found or no solution is available, proceed to the next step to gather logs and create a support case.

8

Collect relevant logs

9

Create a Support Ticket