Topic
  • 10 replies
  • Latest Post - ‏2010-12-13T11:28:05Z by SystemAdmin
SystemAdmin
SystemAdmin
15496 Posts

Pinned topic Slow single page pdf report

‏2010-11-25T10:37:24Z |
Hi All,

I have a report which runs in 3 to 4 minutes with html output and produces a single page containing a header and a single crosstab.

If I try to execute this same report with pdf output, the report times out after an hour :(

As this is a single page report with a single crosstab I don't think this can be the normal 'For html it only renders first page but in pdf it renders everything' issue as the first page is everything.

Any ideas what may cause this issue and how I go about resolving it?

FYI we are running Cognos 8.4.1 FP2 on Windows 32bit with SQL Server 2005 datasource.

Regards
John
Updated on 2010-12-13T11:28:05Z at 2010-12-13T11:28:05Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: Slow single page pdf report

    ‏2010-11-25T21:58:34Z  
    The SQL for the two output formats will likely be different. HTML will restructure the query and add query planning hints to get the first set of records back faster. PDF will run the entire query with the expectation that the entire result set is required so the query will be structured for this instead. I would recommend that you take a look at the SQL through the SQL Server Profiler to see what is going on. Likewise, monitor the database execution time to see if the report time is being spent at the database or on the Cognos 8 server. In the end it would probably be best if you contacted IBM support to help understand the performance differences that you are experiencing.
  • jyothiph9
    jyothiph9
    19 Posts

    Re: Slow single page pdf report

    ‏2010-12-12T05:30:44Z  
    The SQL for the two output formats will likely be different. HTML will restructure the query and add query planning hints to get the first set of records back faster. PDF will run the entire query with the expectation that the entire result set is required so the query will be structured for this instead. I would recommend that you take a look at the SQL through the SQL Server Profiler to see what is going on. Likewise, monitor the database execution time to see if the report time is being spent at the database or on the Cognos 8 server. In the end it would probably be best if you contacted IBM support to help understand the performance differences that you are experiencing.
    Hi John Have u able to find the solution for this. if so plz share....

    @Phil can u please elaborate how to find this.

    Thanks
  • jyothiph9
    jyothiph9
    19 Posts

    Re: Slow single page pdf report

    ‏2010-12-12T05:39:19Z  
    • jyothiph9
    • ‏2010-12-12T05:30:44Z
    Hi John Have u able to find the solution for this. if so plz share....

    @Phil can u please elaborate how to find this.

    Thanks
    Hi Phil

    I have Oracle as backend so where should i look for the SQL to see the database execution time to see if the report time is being spent at the database or on the Cognos 8 server.
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: Slow single page pdf report

    ‏2010-12-12T23:08:05Z  
    • jyothiph9
    • ‏2010-12-12T05:39:19Z
    Hi Phil

    I have Oracle as backend so where should i look for the SQL to see the database execution time to see if the report time is being spent at the database or on the Cognos 8 server.
    If you are using Oracle then you can get the query information from your Enterprise Manager console by finding the active session associated with the report query. Another option is to contact IBM support to get help with enabling a PERF and QFW trace. These traces will give you more detail on where the time is being spent for the query.

    However, I would suggest that you start with the basics first and identify the time spent at the database. The easiest way to see the query time for the PDF query is to use the "Generate SQL/MDX" option from the Report Studio Tools menu. This will give you the native SQL used for the queries (note that interactive HTML may generate a different query in some circumstances). You can then test the native SQL using SQLPlus to see how long the full result set takes to come back from the database and also inspect the number of records being retrieved. A comparison of your output size and the the number of records fetched will give you a rough indication of the amount of local processing required for the query.
  • jyothiph9
    jyothiph9
    19 Posts

    Re: Slow single page pdf report

    ‏2010-12-13T07:05:41Z  
    If you are using Oracle then you can get the query information from your Enterprise Manager console by finding the active session associated with the report query. Another option is to contact IBM support to get help with enabling a PERF and QFW trace. These traces will give you more detail on where the time is being spent for the query.

    However, I would suggest that you start with the basics first and identify the time spent at the database. The easiest way to see the query time for the PDF query is to use the "Generate SQL/MDX" option from the Report Studio Tools menu. This will give you the native SQL used for the queries (note that interactive HTML may generate a different query in some circumstances). You can then test the native SQL using SQLPlus to see how long the full result set takes to come back from the database and also inspect the number of records being retrieved. A comparison of your output size and the the number of records fetched will give you a rough indication of the amount of local processing required for the query.
    In Oracle its taking more than 2hrs and returns 2,00,000 rows for native sql.In cognos HTML output is comming fine but when running in pdf after 1hour its showing error(RSV-SRV-042).
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: Slow single page pdf report

    ‏2010-12-13T09:20:27Z  
    • jyothiph9
    • ‏2010-12-13T07:05:41Z
    In Oracle its taking more than 2hrs and returns 2,00,000 rows for native sql.In cognos HTML output is comming fine but when running in pdf after 1hour its showing error(RSV-SRV-042).
    The large record count can be a fairly good indication of local processing. Disable local processing in the queries and see what errors you get during validation. This should give you a hint about where to start looking to tune the query so that more processing is done at the database.
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: Slow single page pdf report

    ‏2010-12-13T09:56:22Z  
    • jyothiph9
    • ‏2010-12-13T07:05:41Z
    In Oracle its taking more than 2hrs and returns 2,00,000 rows for native sql.In cognos HTML output is comming fine but when running in pdf after 1hour its showing error(RSV-SRV-042).
    Never got this sorted. Response from IBM support is poor, which seems to be the norm these days. They just state `the engine for generating PDF output is different so results may take longer to generate`.

    We have two problem reports both produce a crosstab with at most 3 rows and four columns, they both run in a few minutes to html and time out after an hour to pdf.

    Done lots of testing on this and discovered some unusual behaviour.

    For example, user `CognosAdministrator` is a member of the `System Administrators` and `UnitedKingdom` roles. The report has permissions to allow Read, Execute and Traverse for the role `UnitedKingdom`.
    User `CognosAdministrator` can run this report to html in about 2 minutes but the report times out after one hour to pdf. If I remove `CognosAdministrator` from the `UnitedKingdom` security role the user can run the report to both pdf and html in a couple of minutes.

    Permanently removing the `UnitedKingdom` role from all users who may need to run this report to pdf is not an option. So I am a bit lost with how to fix this. Any suggestions?

    Regards
    John
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: Slow single page pdf report

    ‏2010-12-13T10:53:47Z  
    Never got this sorted. Response from IBM support is poor, which seems to be the norm these days. They just state `the engine for generating PDF output is different so results may take longer to generate`.

    We have two problem reports both produce a crosstab with at most 3 rows and four columns, they both run in a few minutes to html and time out after an hour to pdf.

    Done lots of testing on this and discovered some unusual behaviour.

    For example, user `CognosAdministrator` is a member of the `System Administrators` and `UnitedKingdom` roles. The report has permissions to allow Read, Execute and Traverse for the role `UnitedKingdom`.
    User `CognosAdministrator` can run this report to html in about 2 minutes but the report times out after one hour to pdf. If I remove `CognosAdministrator` from the `UnitedKingdom` security role the user can run the report to both pdf and html in a couple of minutes.

    Permanently removing the `UnitedKingdom` role from all users who may need to run this report to pdf is not an option. So I am a bit lost with how to fix this. Any suggestions?

    Regards
    John
    John,

    I'd start with looking at the queries for each variation. You've got 2 variables: output format and role membership. This should give you 4 queries:
    1. HTML for the United Kingdom role
    2. HTML for members outside of the United Kingdom role
    3. PDF for the United Kingdom role
    4. HTML for members outside of the United Kingdom role

    You can get the query SQL with a QFW or UDA trace. If you are familiar with ipf tracing then there are sample UDA and QFS versions in the Cognos 8 configuration directory. You will need to have the system isolated to get meaningful trace information for just one report. However, if you run each scenario separately you can start to look at the differences in generated SQL which should lead you to tuning options for the query.

    For the security side of things it sounds like there may be some security filters which are modifying the query and impacting the speed with which the results can be returned when the entire result set needs to be fetched from the database. If there is a security filter then this may explain local processing or different database execution plans from one group to another.

    Normally, this would be done with customer support assisting. However, if you aren't getting this kind of help then I would recommend firing some requests higher up the chain to see if your problem can be escalated to another level within support. Sometimes it helps to be the squeaky wheel.
  • jyothiph9
    jyothiph9
    19 Posts

    Re: Slow single page pdf report

    ‏2010-12-13T10:54:23Z  
    The large record count can be a fairly good indication of local processing. Disable local processing in the queries and see what errors you get during validation. This should give you a hint about where to start looking to tune the query so that more processing is done at the database.
    Changed from local processing to Database only for all the queries then also the same problem.
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: Slow single page pdf report

    ‏2010-12-13T11:28:05Z  
    • jyothiph9
    • ‏2010-12-13T10:54:23Z
    Changed from local processing to Database only for all the queries then also the same problem.
    jyothiph9,

    This is where it becomes tricky to help you via a forum posting. There isn't enough information to identify the source of the issue and getting into it will take some in-depth tracing. Despite John's recent experiences I would recommend that you try contacting IBM support to get some help with setting up and interpreting the trace information.