How to minimize the run time in reports in cognos 10.1
And Suppose if the SQL query takes 5 sec when run directly against the database and 10 sec while running the report, then what is the error and what should be the solution to it.
Thanks in advance
Your hypothetical is entirely too vague for any kind of meaningful answer.
I was trying to say, we run the SQL queries against the database first to make sure that its correct and to decrease the run time in reports.
My question is what measures should we take to reduce the run time in reports and if there is adifference in run time of a SQL query against a database and it report what steps should we take to resolve it.
Am I able to make myself clear now
Quote from: cognos4321 on 14 Oct 2014 10:14:42 AM
I was trying to say, we run the SQL queries against the database first to make sure that its correct and to decrease the run time in reports.
My question is what measures should we take to reduce the run time in reports and if there is adifference in run time of a SQL query against a database and it report what steps should we take to resolve it.
Am I able to make myself clear now
It is still rather vague. It would help if you provided specifics about your report and your specific situation. Is it relational or a DMR package? What type of layout container is being used? Is there a lot of summary information or conditional formatting involved? Are you rendering to HTML or to some other format such as Excel or PDF? Is the 5 seconds in the database returning the full result set or is it just the time it takes to return the first rows? Is there just one query or multiple queries and are any of them being further joined within the report? What type of data volumes are we talking about within the database and what is the approximate size of your result set? What differences do you see between the native SQL and the Cognos SQL? Do you have sorting and grouping going on that isn't taking place on the database side? Etc. Etc. Etc. Etc. Etc. Etc.
Quote from: cognos4321 on 14 Oct 2014 10:14:42 AM
I was trying to say, we run the SQL queries against the database first to make sure that its correct and to decrease the run time in reports.
My question is what measures should we take to reduce the run time in reports and if there is adifference in run time of a SQL query against a database and it report what steps should we take to resolve it.
Am I able to make myself clear now
It depends on how much the difference is. If your example of 5 sec vs 10 sec is to be considered close to reality then a resolution is not necessary because Cognos dispatcher will take 2/3 seconds just to spawn a process. It also depends on what your approach is. Are you writing the SQL by hand, testing it in DB and then putting it in Report Studio bypassing the entire ETL and modeling? If so, we won't be able to help you much.