Cognos 10.2.1 BI (stored procedure "ref cursor" VS View)

[_IBM_]

Hi all,

Could you please help, I have 2 reports:

1. Reprot was created via FM (stored procedure "ref cursor").
2. Report was created via Report Studio (select * from name_view).

Could you please explain to me

1. How does Cognos work with 2 variants?
2. What will be faster and why?
3. Does it depend count of rows?

Thank you.

[sunosoft]

I assume you want to know difference between package based report and a report designed by using SQL object in report studio.

What happens in second report(SQL object based) your select *from view query will be directly fired to database and data will be transfered from DB server to cognos server. And now if you have some filter condition in a report then that filter will be executed on cognos server. Now suppose if you have a large data set which is travelling all the way from DB server to cognos server then it will surely affect the performance. On the other hand if you use a hardcoded filter in a report this might be faster than previous scenario.

Now coming to package based report - here the query which is being fired on DB will be optimized and query hits dabase with filter hence this will be more faster in case of large data as comapred to SQL based report.

[bdbits]

Detail filters will execute on the cognos server for dimensional queries, and perhaps for DQM relational though I am not positive on that part. Traditional CQM relational queries will most definitely incorporate a where clause into the SQL sent to the database, you can easily see this with a database trace.

Whether a stored procedure will be faster depends somewhat on what the stored procedure is doing and the size of the respective result sets (row counts). Also, whether the package is dimensionally or relationally modeled is relevant. A stored procedure can be faster, but I would recommend against it for Cognos reports for all but the most critical performance problems. It generally defeats the purpose of having a metadata-based tool like Cognos in the first place. This is doubly so if the idea is to allow users to author their own reports.