Query size

Query sizes tend to be a concern when authoring reports. Queries generated by Cognos from the metadata model tend to be larger because they must be filtered, instead of being directly selected from the tables.

If the physical layer of the metadata model was select <COLUMN_NAME> from <TABLE_NAME>, then the resulting query generated by Cognos would select directly from the table as expected. But, when the query is generated against the application database, queries against tables are often filtered by LOCALE_ID for data localization views. This causes Cognos to nest the query, for example:

Select wb_table.column_name
From
(Select column_name
From vl_table_name
Where locale_id = 1)
wb_table

This is why a query representing the Employee query subject would be so large. The Employee query subject could return upwards of 30 columns, with a combination of a data localization view (VL_EMPLOYEE) and security (SEC_EMPLOYEE) that must filtered for the logged in user and locale.

Query size effects on performance

The opinion that large SQL queries perform slower is only partly true. RDBMS optimizers take longer to parse larger queries to determine what needs to be executed. But, once the optimizer knows what needs to be done, the query execution time and cost is almost identical to an equivalent simple query.

The disadvantage of large queries is that by containing more SQL queries, they use slower SQL operations, such as UNION's, GROUP BY's, ORDER BY's, sub queries, and data formatting. Short queries can use these operations as well (and can even hide them behind VIEWs).

To improve performance for large queries, the Duplicates property for a Union should be set to Preserve to use a UNION ALL statement. The Auto Group and Summarize property should be set to Yes when rolling up data to limit the use of the GROUP BY, ORDER BY, and DISTINCT statements.