Very slow Excel link spreadsheet report (F9) speed, caused by large XDB tables, fixed by partioning tables

Troubleshooting

Problem

User opens a spreadsheet report (XLSX file). User logs into Controller (Excel link), and presses F9 to run (refresh) the report.

This takes a very long time.

Symptom

In one real-life example, a typical report took as follows:

Before fix (unpartitioned database tables): 10 minutes
After fix (partitioned database tables): 1 or 2 minutes.

Cause

There are many known causes for slow Excel link reports.

TIP: See separate IBM Technote #375127 for more examples.

This Technote specifically relates to the scenario where the cause is that the customer's XDB (data) tables have grown so excessively large that the standard Microsoft SQL tables cannot give fast performance.

More Information:

Controller stores the bulk of its data inside tables whose names begin with XDB.

For example, for the year 2019 the corresponding table name is XDB19.

By default, Controller XDB database tables are not partitioned. This is OK for most customers.

In very rare circumstances, customer's XDB tables are so large that they benefit from being 'split' into separate partitions.
For more explanation on what partitioned tables are, see third-party (non-IBM) links below.

Environment

The information in this Technote is only applicable for the very rare scenario where both of the following are true:

the customer has huge XDB (data) tables
database hosted on Microsoft SQL.

Example:

In one real-life customer example, the XDB tables were filled with approximately 11-13 Gb of data:

vchTableName	biRowCount	vchTableSize	vchDatASpaceUsed
xdb17	80,000,000	30 GB	11 GB
xdb18	103,000,000	37 GB	13 GB
xdb19	80,000,000	30 GB	11 GB

The above sizes are very large. In comparison, a typical customer's XDB size is normally 1 to 2 Gb in size.

Diagnosing The Problem

For instructions on how to find the size of tables (in your Controller database) see separate IBM Technote #373067.

Resolving The Problem

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

** WARNING **

The information in this Technote is only designed for very rare scenarios, where the XDB table is excessively large.

Customers should take care when implementing the advice in this Technote.

In general terms, customers should not partition their tables without contacting IBM Support for advice first.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

1. As a precaution, perform the following on a test database first

2. Before continuing, create a backup of the database

3. Create a partition on the relevant XDB tables (for example XDB18, XDB19, XDB20) based on column [Perakt].

Example:

In one real-life example, the customer had at least 12 actualities (for each period), so a total of 147 partitions were created (for each XDB table).

After doing this, the speed of the report increased from approximately 10 minutes to approximately 1-2 minutes.

Related Information

375127 - Controller Excel Report Performance Tips

Third Party (Microsoft) - Partitioned Tables and Indexes

Third Party (Microsoft) - Create Partitioned Tables and Indexes

Third Party (non-IBM) - Table Partitioning in SQL Server – The Basics

373067 - How to find out the size of each table inside a SQL database

Document Location

Worldwide

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

Tips