Optimize report

[Ning88]

Hi,All,

I am redesigning a report for end users.(IMR). The run time limit for report is 4 minutes. The table joints are fine, the data pull out is very fast. But when I start to add calculations or other functions in the report, it slows down untill the report can not finishing running in 4 minutes. I know this report can be finish running in 4 minutes. Is there some way to optimize the report to make it run faster? Any steps in the report design can optimize report? Do some calculations or functions can be done in database instead in report?

 

[griffindm]

Ning88,

For a fast-performing, high volume report, it's best to make sure the the database is doing the heavy work and Impromptu is just 'painting' the screen. If you are reporting off transactional sources, you are limited in your ability to create indices and summary tables. You can still make sure that you are taking advantage of what the database offers. Check your SQL against your calculations to determine if any are being done locally. Confirm whether this is the performance bottleneck by running the SQL directly through an SQL interpreter. Check the execution plan for the SQL on the database to confirm it is using available indices and not doing unnecessary table scans. Check for the presence of outer-joins. These can often be performance killers.

Hope this helps,

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[Ning88]

Hi,

When you say the database is doing the heavy work, what do you mean? When I add calculations, I do use the column from the catalog instead from the column of report, is that considering database doing the work? How can I do the sort, group and convert number-to-date in database?

 

[Bensta]

What Dave meant to say is that the make sure that you sql statement in executed on the database level not locally.

 

[cindyw]

There is a little icon the left of each function. Make sure that the functions you utilize do not have only the little 'i' (Impromptu only functions). Use of these functions will force data to be returned to your machine for local processing.

 

[griffindm]

cindyw,

The "i" icon can be a useful guide, but don't rely on it exclusively. In many cases it is misleading. The ultimate authority is whether the desired function appears in the final SQL that is sent to the database.

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com