Model based report vs native SQL based report

[sunosoft]

Hi All,

Just wanted more information on perfroamnce of model based report and native sql based report.

Which report should work fast ?

As I heard or read or made understading from google that native sql based report should be faster than model based. Let me know if my understanding is right or not ?

Reason why I want to know:
--------------------------------------------
We have one project consisting of more than 30 reports which has not been developed by using standard approch of model designing through FM. Rpeorts are developed just draging SQL objects and writing select *query on the view. Each report is having its related view in DB. These reports are taking very much time to execute(6-7hrs), even if they are returning data around 500 rows. If we run the same query against database then that query is running within 2 mins.

For one of reports when I developed a simple pkg writing the same select * from view query in FM do fetch the query items in query subject. I developed report based on this pkg and it executed very fast. Its taking the approximately the same time as time taken by query in DB.

I am not able to understand the diff between these reports. Can anybody sugest anything

[MFGF]

Hi,

If you bring a SQL object into a report and code "Select * from <table/view>" your report will retrieve all columns from the table or view regardless of whether you use them in a report object or not. This is absolutely not best practice.

If you have a data source query subject in FM with an embedded query of "Select * from <table/view>" then you publish this into a package and build reports off this, the query engine will minimise the query as much as possible. If your report only uses two columns from the table, a query retrieving only those two columns will be issued. This is because the default behaviour for SQL generation in FM is "minimized". You can override it by setting the behaviour to "as view" if there is a need, but generally it's not something that's done unless there's a good reason.

Cheers!

MF.

[sunosoft]

Hi MF,

Thanks for your response.

As you know I never dealt with development. I worked in admin part only. Recently this new applications has been developed and now users has started to use it. And they are reporting performance issue to me. Seems like just before my joining this application came to production, I dont know how they brought without testing. Developer has left the organization. I dont know why he followed this approach.
Still I want to make my users happy somehow :) which i have been doing

Regarding report columns, yes they are using all columns from that view.

Reports are very simple list reports. Nothing is done in the report, it seems like everything is handled at DB level. In report just drag and drop has been done. And just one or two prompts thats it.
Beside this SQL object, should I look into any other thing which is impacting the performance. Or should i suggest them we will need to rebuild the application again ?

[MFGF]

...And just one or two prompts thats it...

So consider this. If your report has hard-coded SQL it cannot be changed by things you do within the report spec - only by editing the SQL manually. If you add prompts they will therefore be handled locally on the Cognos server. This means the entire data set from the table will be retrieved from the database, shipped across the network to the Cognos server, and these results will then be filtered locally, effectively casting most of them away. That's *hugely* inefficient.

If you have a report based on query subjects in a package, when you add filters and prompts (mostly) the query engine writes a query passing the filter condition to the database as a predicate ("where" clause). This means only the desired rows are retreived and passed to the Cognos server.

If you have the opportunity, I would strongly recommend you create a metadata package in Framework Manager and point the reports at this, dumping the hard-coded SQL. It sounds to me like there is no valid reason to have manually-coded SQL queries in your reports - I imagine the developer simply didn't have the knowledge or skills to do anything other than what he did?

Cheers!

MF.

[CognosPaul]

It's also worth mentioning that Cognos can analyze and optimize queries from the model. With the new querying engine, it can split up a query and use in-memory caching to make the query faster than what you could achieve with a direct query to the DB.

[sunosoft]

Thanks a lot guys for your responses.

MF - You really gave very good information :) Now I come to know why those reports are taking so much time..actually application database is having very large data(in TBs). And as you said, its trying to bring all data first and then filtering it locally. Hence reports are taking more than 10 hrs  to execute.

Yeah, i have chance to create package. Will give a try for this, it will also help me to improve my development side :)

[BigChris]

Just wanted to add my thanks as well. I'd got a report that was taking a very long time to run without any obvious reason. The report was using a package from the Framework Manager, but that had been built over a hefty SQL statement in the FM itself. This morning I've taken the same SQL and transferred it to a view in the database, then used a "Select * from View" type expression in the FM. The report, which had been taking hours to complete, now runs in ten minutes or so.

So that's one lesson learnt which I won't forget in a hurry...now I just need to check for other instances in FM and convert them to views as well.

C