Skip to content

27.1 Performance Tuning

Datashop queries the data warehouse live, so report speed depends on the database and how the model uses it. The PERFORMANCE tab in Modeller has the main tools; Tuning guide opens Acinta's performance guide.

Start by finding the slow parts in Datashop: Job Monitor and View statistics – see 19.2 Job Monitor and Performance.

Indexes

Indexes on the columns Acinta joins and filters on are the most effective tuning.

  • Create indexes (PERFORMANCE tab, or right-click the Warehouse) suggests indexes for all tables. Tick the ones to create, choose Drop existing indexes first (on by default) and click Start. Each index shows the time it took.
  • To define an index yourself, select the table and click New... under Indexes in its properties. The Edit index dialog lists the Available columns with how often they're used (Weight); add them to Index columns (and Included columns), or click Suggest index. Custom DDL lets you write the CREATE INDEX yourself.

Creating indexes needs permission to change the database, and can take a long time on big tables – do it outside working hours.

Statistics and Summary Tables

When a fact is mapped to both a detail table and smaller summary tables, Datashop picks the smallest table that has the dimensions a workbook needs – see 24.1 Facts. Collect statistics counts the rows of the selected tables (or all) so Datashop can choose well. Run it after big data loads.

Data Source Options for Speed

DATA SOURCE → Options → Optimization and Database:

Option Effect
Caching time (seconds) Reuse query results for this long – 0 (default) turns caching off
Pre-aggregate fact tables Summarize fact tables before joining
Pre-select values from lookup tables Read lookup values first
Create indexes on temporary tables Index Acinta's temporary tables
Query timeout Seconds before a query is stopped (600)
Maximum number of allowed connections to this source Parallel queries per data source

Change one option at a time and measure – the effect depends on the database and data.

Other Factors

  • Dynamic views used as CTE run with every query – consider Materialized for heavy ones, see 27.2 Materialized Dynamic Views and Caching.
  • Large FlexTables (over 500 rows) are copied to the database on first use.
  • Row warning for grids/graphs (project options) protects users from accidentally huge reports.