Cognos Sql Vs Native Sql and Performance

Hi

Native sql is the sql that is directly fired on the database. Cognos sql is the sql that is used by cognos once the output of the native sql have come.If your most of processing is done in the native sql.Your report will run faster.Simple Eg- If your filter for some reason such as data type casting are applied in the cognos sql.It means that all the data will be fetched first and then on it the filters will be applied.This will slow down the performance a lot.

You can notice that some time your report will have split queries in your native sql but the same queries are joined in the cognos sql .This means that cognos does know what is the path between those split tables .So the question is why does it split the queries and how to join them back.

Important :- If the queries are split then your performance is affected big time as the queries will be joined later by cognos.

The most simple reason for queries to split is the use of cognos function as simple as cast(abc,varchar(10)) ,since this is cognos cast and cannot be applied on the database it will be applied in congos sql , the query can split if you are applying a filter on this cast like this cast is used for date and you are saying date between report start date and report end date. Congos might consider the best way to do this is to split the queries apply the cast and filter based on cast on one query and then join both the queries .Now this is decided by congos logic and nothing you can do to control it .

Simple workaround : - Bring the column on which the cast is applied in the list or crosstab this might allow cognos to make a single native sql .

2) You can get rid of the cognos function by making use of similar database function,this will cause the processing to be transferred to database.

3) If you cannot find a way out and the project timeline is approaching you can make use of direct sql in queries .Take the two sql join together and fire on database .this is not the best way to do it due to maintainance concerns .But i have used this in one of my project.

Point to remember for good performance- Make sure your most of the filters come in the native sql and not in cognos sql. Try to use database function wherever possible so that most of cast , case ,count are handled by database this will speed up your performance.