Topic
IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
10 replies Latest Post - ‏2010-12-13T11:28:05Z by SystemAdmin
SystemAdmin
SystemAdmin
15496 Posts
ACCEPTED ANSWER

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
    ACCEPTED ANSWER

    Re: Slow single page pdf report

    ‏2010-11-25T21:58:34Z  in response to SystemAdmin
    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
      ACCEPTED ANSWER

      Re: Slow single page pdf report

      ‏2010-12-12T05:30:44Z  in response to SystemAdmin
      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
        ACCEPTED ANSWER

        Re: Slow single page pdf report

        ‏2010-12-12T05:39:19Z  in response to jyothiph9
        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
          ACCEPTED ANSWER

          Re: Slow single page pdf report

          ‏2010-12-12T23:08:05Z  in response to jyothiph9
          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
            ACCEPTED ANSWER

            Re: Slow single page pdf report

            ‏2010-12-13T07:05:41Z  in response to SystemAdmin
            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
              ACCEPTED ANSWER

              Re: Slow single page pdf report

              ‏2010-12-13T09:20:27Z  in response to jyothiph9
              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.
              • jyothiph9
                jyothiph9
                19 Posts
                ACCEPTED ANSWER

                Re: Slow single page pdf report

                ‏2010-12-13T10:54:23Z  in response to SystemAdmin
                Changed from local processing to Database only for all the queries then also the same problem.
                • SystemAdmin
                  SystemAdmin
                  15496 Posts
                  ACCEPTED ANSWER

                  Re: Slow single page pdf report

                  ‏2010-12-13T11:28:05Z  in response to jyothiph9
                  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.
            • SystemAdmin
              SystemAdmin
              15496 Posts
              ACCEPTED ANSWER

              Re: Slow single page pdf report

              ‏2010-12-13T09:56:22Z  in response to jyothiph9
              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
                ACCEPTED ANSWER

                Re: Slow single page pdf report

                ‏2010-12-13T10:53:47Z  in response to SystemAdmin
                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.