Minimizing SQL query response times
Remember that less is faster. If all other factors are the same, a simpler SQL statement is satisfied in less time than a more complex SQL statement. Likewise, requests for more data take longer than requests for less data, if all other factors are equal.
As reports are executed or dashboards opened, the Cognos® Analytics query service plans SQL statements that it requires to obtain data from one or more sources. The physical SQL statements that are generated are dependent upon the SQL semantics and data types supported by the underlying database. The complexity of the generated SQL statements can introduce performance costs both for the underlying data server and for the Cognos Analytics server when it needs to perform additional processing locally.
Cognos Analytics applications that are layered on operational databases frequently require complex joins and expressions to navigate through the data and present values in business terms. In contrast, applications that are layered on cleansed reporting structures, such as star schemas, can benefit from the data transformations applied by the publishing extract, transform, and load (ETL) processes. Reducing the complexity of the joins and expressions in queries can help the underlying data server plan queries more efficiently, and in turn, reduce processor and memory consumption.
Here are some preferred practices that many data server technology vendors suggest to improve run-time performance.
Avoid complex join and filter expressions
The complexity of expressions in the WHERE and JOIN ON clauses
of an SQL statement can impede planning for the data server, query rewrites to materialized views,
or other forms of query acceleration.
Reduce explicit or implicit data type conversions
Converting data types requires processing that can significantly increase query response times.
Converting data types happens explicitly when you use a function like CAST() in a
calculation or filter, or implicitly when certain operations occur on columns with different data
types.
Ideally, a calculation expression that serves as a key in a relationship between tables resolves to the same data type as the corresponding key on the opposite side of the join relationship. This prevents constraining the data server from considering certain join strategies, such as a hash join, because of incompatible data types.
Avoid using SQL expressions to transpose values
Users who are familiar with SQL can construct expressions that attempt to massage database values for display. In several cases, such expressions can be replaced by using the available data type formatting, layout, and other presentation options.
The following example demonstrates how you can initiate multiple data type conversions, substrings, and concatenations to display a date value in a particular way rather than using the data format rendering option available in various authoring interfaces.
Substring(Cast ( dateField, char(10)),6,2) || ‘-‘ || Substring(Cast ( dateField,
char(10)),9,2) || Substring(Cast ( dateField, char(10)),1,4)
Avoid unnecessary outer joins
Outer joins enable applications to return result sets when one or more tables in a statement lack associated data. Queries that use outer joins restrict various join optimization strategies and join ordering that a data server sometimes uses with inner joins. A model might be constructed to always use outer joins that may not, in fact, be required by the business questions posed by a report or dashboard.
Make use of constraints on tables in data servers
Tables in a data server can have constraints that can be considered by the data server query engine for strategies such as join eliminations, query rewrites, and expression optimizations.
Primary key, unique key, and foreign key constraints (but not null and table constraints) can be declared for this purpose. Depending on the data server technology, these constraints can be declared as either non-enforced or enforced. In a normalized table design that includes snowflake schemas, non-primary key columns are functionally dependent on the primary key.
To plan SQL statements for the data server to process, the Cognos Analytics query service uses enforced constraints defined in a metadata model, such as determinants in Framework Manger or column dependencies in data modules, and relationships between tables. These objects are often created during one of the initial steps of creating a model, but more common is that they are manually defined by the metadata modeler. For more information, see Determinants and Column dependencies.
Use indexes and table organization features
A common challenge for a database administrator is to anticipate the ways that applications attempt to navigate the database. This includes which tables the queries will combine and which tables predicates will be applied against.
Using a representative workload, the database administrator can review which tables are most frequently accessed, and in particular, which local set of columns is used to filter and group columns in tables. Using that knowledge, the database administrator can usually identify indexes or table organization strategies that enable the database to more efficiently select the required rows.
The candidate workloads must reflect any ad hoc analysis and exploration of data that can occur within an application. This is important when the database administrator is constrained in terms of what covering indexes or table organizations they can define, which might bias the solution toward the most frequent cases. For example, an application might predominantly categorize measures based on time, customer geography, and product perspectives for which the database administrator can optimize the table designs.
A metadata model can also be constructed on top of databases that expose application objects through SQL views. Such views must be reviewed by the database administrator with respect to the expressions within the view.