Good native SQL vs. bad native SQL
When the database processes your query, these two key aspects are relevant:
- Production database servers are usually better equipped with resources and processing power than the Cognos server.
- Processing at the database server means that proper indices are being applied when joining tables, which ensures faster query processing.
Good native SQL
A good native SQL query represents a generated native SQL query that appears as one query. When your Cognos query generates one native query, then the bulk of SQL processing is performed at the database server.
Bad native SQL
A bad native SQL query is a generated native SQL query that appears as multiple queries. Each individual query is executed against the database and then ‘stitched’ together and filtered in memory on the Cognos server.
For example, if a query such as
select * from work_detail
is run, then the query must be loaded into memory to process
all the joins and filtering. This query can return rows in the tens of
millions. Such a large number of rows causes slow processing.
Bad native SQL queries are caused by the presence of certain Cognos functions such as the CAST function. These functions require local processing or left outer joins in the metadata model.