IBM Support

Suddenly slow (for example 3 minutes, compared with 10 seconds) to open a Data Entry form (even if only one user active) caused by out of date statistics

Troubleshooting


Problem

User clicks 'Company - Data Entry - Reported Values'. User chooses parameters, and clicks 'open'. There is a long delay (for example 3 minutes). Eventually the data entry form appears (in Excel).

Symptom

Originally (for example at the beginning of the month, before anyone has entered any data) the data entry form opens quickly (for example in 10 seconds). However, after a while (after users have entered data in the form, during the month end process) the problem occurs.

  • For example, it suddenly takes 3 minutes (not 10 seconds) to open the same form.

The problem affects all users, and is reproducible even when only 1 user is on the system.

Example:

In one real-life example, the problem only affects the current period (for example 1601). If the user tries to open a future period (for example 1602) then the form opens quickly (for example 10 seconds).

Cause

There are many possible causes for slow performance when opening Data Entry forms.

  • TIP: For more examples, see separate IBM Technote #1621134.

This Technote specifically relates to the scenario where the cause is that the database statistics are out-of-date.

Real-life Example:
Symptom:
    In one real-life customer case, the 'bad' Data Entry form uses a column definition that uses:
    • current period
    • and last period for AC (= period -1)

    The defect causes Controller to create a very complex database (SQL) query when data is retrieved from multiple years.
    • In other words, running this form gives OK performance when only data for the same year is involved.

    In the real-life customer case (where the form includes data from the previous period):
    • If the user opens the form choosing '1512', then 1512AC, 1512BU and 1511AC is used
    => Good performance (because it only looks at data for one year)
    • If the user opens the form choosing '1601', then 1601AC, 1601BU and 1512AC is used
    => Bad performance (because it looks at data over two separate years)
    • If the user opens the form choosing '1602', then 1602AC, 1602BU and 1601AC is used
    => Good performance (because it only looks at data for one year)

    In other words, for this specific customer case (where the data entry form only includes data from this current period plus the previous period) the problem will only occur during the first month of the year (for example 1601, 1701, 1801...).

Cause:
    In that real-life customer case, the cause of the problem was that the indexes in this database were incredibly fragmented (more than had ever been seen before for any customer!).
    • This fragmentation causes the SQL Server query optimizer to execute the wrong execution plan (for this specific query relating to the two years etc).

Environment

The problem is only triggered by certain types of Data Entry form design.

  • Specifically it has been seen where the DE form contains references to data from more than one period.

Diagnosing The Problem

To find out when your database statistics were last updated, run the following SQL script:

    SELECT *,
    STATS_DATE(object_id, stats_id) AS LastStatsUpdate
    FROM sys.stats
    WHERE left(name,4) = 'NC_X'
    order by laststatsupdate

Inside the 'Results' section, look at the final column ("LastStatsUpdate").
  • Scroll down past many entries (saying 'NULL') until you get the first entry that has a date in it.

For example, in one real-life database it was on row 263:

[Here the last time that the database was optimised was 1st November 2014].

Resolving The Problem

Fix:
Update the database statistics on your database.

Steps:
There are several different methods to update the statistics on your database.

  • Method #1: Run a database optimise from *inside* the Controller GUI
This action can be performed by a Controller super-user. However, it will require some downtime (no other users on the system).
    1. Obtain downtime (no other users on the system)
    2. Ask the Controller superuser to click 'Maintain - User - Single Mode'
    3. Click 'Maintain - Database - Optimize'
    4. Tick the box 'Rebuild Indexes':

    5. Click 'Yes'
    6. Click 'Run'.

  • Method #2: Run a database re-index from *outside* the Controller GUI
This action can be performed by the customer's I.T. department (database administrator). However, it does not require any downtime.
    1. Ask your I.T. department's SQL database administrator to create/schedule a reoccurring (regular) SQL Maintenance Task to re-build indexes and update statistics regularly
    • For example, update statistics every night, and rebuild indexes once per week
    • TIP: For full details, see separate IBM Technote #1396973.
Workaround:
Depending on your form design, there may/may-not be a workaround.
    Example:
    In one real-life customer case, the workaround was to:
    • Open the DE form for the future period (1602)
    • Save the data inside the column which relates to 1601 (which is a different column from normal!).

[{"Product":{"code":"SS9S6B","label":"IBM Cognos Controller"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"Controller","Platform":[{"code":"PF033","label":"Windows"}],"Version":"10.2.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
15 June 2018

UID

swg21976521

Page Feedback