I have a database table which contains all the organizational units, including a column for the parent ID.
Unfortunately I do not have a complete hierarchy in this table, so I've created a query subject with some recursive SQL to perform this task. This worked fine on Cognos 8.2, however I recently upgraded the framework to 8.4.
Now, if I create a report in Report Studio and join this query subject with another query subject (1..1 - 1..1 cardinality), query performance is terrible (38.4 seconds). I checked the native SQL and saw that Cognos is using a DISTINCT clause. If I fire the native SQL without the DISTINCT clause in Oracle SQL*Plus, the query only takes 0.8 seconds.
I turned off Auto Group and Summarize on the query and now Cognos removes the DISTINCT from the native SQL. However, if I run the tabular data from Report Studio the query is still just as slow (although the native query in SQL*Plus is fast).
What am I missing here?
Cognos adds a distinct clause when only identifiers and attributes are fetched. Obviously adding the distinct enforces sorting data to remove duplicates , hence the performance drop. You could add a dummy fact and test whether that makes a difference. I suspect that Cognos still will try to perform a sort after the fetch from the database. Can you enable a trace on the Oracle backend to check what is actually executed? We do this quite often on SQL server and it often points to differences compared to the suggested SQL from the report
You are so right!
The trace in Oracle showed me that Cognos ends the SQL with
order by "c3" asc nulls last
Anything I can do about that?