Cognos SQL vs. Native SQL

Native SQL:
The Native SQL is what is being passed to the database. 
Native SQL is the SQL the data source uses such as Oracle SQL. 
You cannot use Native SQL in a model query subject that references more than one data source in the project.
This is Sql based query which has typical 
database format and funcatons. All the caculation done at 
database only. 

Cognos SQL:
Where the Cognos SQL is the entire SQL used to perform the SQL. 
In certain scenarios, the native SQL will be a subset of the Cognos SQL which may indicate that the application will do some local processing. 
If the cause of the local processing can be identified, it may be possible to implement a report or model change to alleviate the local processing requirement.
This Sql is genreated by cognos on the behalf of your query. It is good for your own calculation and for the performance tunning also.

Pass-through SQL:
 lets you use Native SQL without any of the restrictions the data source imposes on sub queries. Pass-Through SQL query subjects are not processed as subqueries. Instead, the SQL for each query subject is sent directly to the data source where the query results are generated.

Analysing the Existing Native and Cognos SQL
 Extract the Native and Cognos SQL for the given query:

Native:
select "RETURNED_ITEM"."RETURN_REASON_CODE"
from "GOSL"."dbo"."RETURNED_ITEM" "RETURNED_ITEM"
order by 1 asc

select "RETAILER"."RETAILER_CODE"
 from "GORT"."dbo"."RETAILER" "RETAILER"
 order by 1 asc

Cognos SQL:
select
 1 as C_____CubeDetailsItem,
 XSUM(RETAILER.RETAILER_CODE ) as RETAILER_CODE,
XSUM(RETURNED_ITEM.RETURN_REASON_CODE ) as RETURN_REASON_CODE 
from GOSL.GOSL.dbo.RETURNED_ITEM RETURNED_ITEM 
left outer join
GORT.GORT.dbo.RETAILER RETAILER
on (RETAILER.RETAILER_CODE = RETURNED_ITEM.RETURN_REASON_CODE)
 group by 1

The native SQL consists of two separate select statements. A closer look reveals that the qualifications are different. This indicates that each of the two columns come from a table in a different schema.

The Cognos SQL shows that the query requires a left outer join relationship between the two tables. However, since this relationship is not included in the Native SQL, it is being processed locally. Depending on the size of the tables involved this may impact performance.

Due to the different schema qualifications, the tables were imported as two different data sources. These force the application to treat the query as federated.


Updating the existing mode
The model will need to be updated to include both tables under the same data source. This can be done either with an Oracle user which has access to both schemas or via a cross schema view. Once the model has been updated both the Native and Cognos SQL will contain the left outer join.

Native:

select distinct "RETAILER"."RETAILER_CODE" "RETAILER_CODE", 

"RETURNED_ITEM"."RETURN_CODE" "RETURN_CODE"  from "GORT"."RETAILER" "RETAILER" LEFT OUTER JOIN "GOSL"."RETURNED_ITEM" "RETURNED_ITEM" on "RETAILER"."RETAILER_CODE"="RETURNED_ITEM"."RETURN_REASON_CODE" 

Cognos:
 select distinct RETAILER.RETAILER_CODE as RETAILER_CODE, RETURNED_ITEM.RETURN_CODE as RETURN_CODE 
 from OracleSystem..GORT.RETAILER RETAILER left outer join 
OracleSystem..GOSL.RETURNED_ITEM RETURNED_ITEM 
 on (RETURNED_ITEM.RETURN_REASON_CODE = RETAILER.RETAILER_CODE) 

---------------------------------------------
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.