Topic
  • 7 replies
  • Latest Post - ‏2011-03-21T21:28:35Z by mgibson
mgibson
mgibson
592 Posts

Pinned topic performance: Cognos taking much longer than SQL alone would suggest...

‏2011-03-16T03:50:30Z |
Hi Guys,

I think perhaps I'm expecting too much in hoping for any response that will be helpful, but I'll ask none the less.

I have an RS report in Cognos 8.4 that takes nearly exactly 2 hours to run - It does not operate over our DW, but uses one of our transaction system DBs.

When I get Cognos to generate the SQL and run it in an SQL tool via ODBC (using the exact same parameters), it only takes 24 minutes to return all data.

I have always understood that Cognos imposes it's own overheads, but this seems a little ridiculous, even when you allow for the complexities of the multiple queries that are joined within the Query Explorer; and the charts, etc that are rendered.

Can anyone suggest how I may be able to troubleshoot performance? Note that this reporting accesses an SQL Server DB.

As I said, I know I might be expecting too much, but it's worth asking.

Regards
Mick
Updated on 2011-03-21T21:28:35Z at 2011-03-21T21:28:35Z by mgibson
  • yangling
    yangling
    268 Posts

    Re: performance: Cognos taking much longer than SQL alone would suggest...

    ‏2011-03-16T04:46:38Z  
    You said - When I get Cognos to generate the SQL and run it in an SQL tool via ODBC (using the exact same parameters), it only takes 24 minutes to return all data
    so I wonder if you are using native connection to your SQL Server database or using ODBC?

    What is the version of your SQL Server?

    If possible, the native connection should be used.
  • mgibson
    mgibson
    592 Posts

    Re: performance: Cognos taking much longer than SQL alone would suggest...

    ‏2011-03-16T05:09:59Z  
    • yangling
    • ‏2011-03-16T04:46:38Z
    You said - When I get Cognos to generate the SQL and run it in an SQL tool via ODBC (using the exact same parameters), it only takes 24 minutes to return all data
    so I wonder if you are using native connection to your SQL Server database or using ODBC?

    What is the version of your SQL Server?

    If possible, the native connection should be used.
    Fair questions:

    The server install is on Linux, and is using a proprietary ODBC Linux compatible connector from a company called 'Easy soft'. I haven't created any of my own SQL in the report, and Cognos is generating it itself using Native SQL.

    When running the comparable SQL on my desktop, I'm obviously using the Windows compatible SQL Server native ODBC connector.

    And, I believe the SQL Server version is 2005.

    I doubt the differences in performance could be explained by the Linux ODBC connector - but I could be wrong.

    Any ideas?

    regards
    Mick
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: performance: Cognos taking much longer than SQL alone would suggest...

    ‏2011-03-16T06:33:11Z  
    There could be a few things happening here:

    1. You've taken the SQL from the query properties instead of using the Tools menu in Report Studio. This would give you an inaccurate view of the generated query because Cognos generates the query at run-time based on the items included in the report layout (not all of the items contained in the query).

    2. If you have multiple charts and query containers on the layout and you are using the same query reference for each container then you should note that sharing result sets is only applicable for list type queries with the same projected columns. If you have other query containers such as charts or crosstabs then each new query container will send a new query to the database. If you look at the SQL generated via the Tools menu you will see a query with multiple versions listed for the different containers on the layout. For example, a query named "Query1" which is used for a crosstab and a chart would show up as Query1.1 and Query1.2 in the generated SQL dialog. The multiple queries will execute in series by default but you can change them to execute concurrently using the properties of the query object (and some advanced settings listed in the Administration and Security Guide for more control of concurrency).

    3. Local processing could be taking place. The 24-minute SQL query may be creating a massive data set which will take a little while to transfer to the Cognos server. Additional time would then be required to do any of the local processing from this data set. You can try disabling local processing on the queries to get an indication of whether this might be a factor.

    4. Master-detail relationships may be issuing more queries than you expect. A detail query will typically be executed once for each master query record. This could multiply out the duration of the report execution. The only scenario where you can make use of a single result set for a master-detail relationship is when you have a list query inside another list query. In this case you can use the option to section the detail query (assuming that the master query is the same as the detail query) rather than creating an explicit data item link between the two query containers. There isn't much you can do to improve the performance if you are using master-detail relationships.

    5. Complex rendering may also add some overhead to report generation. Normally this would not account for the time difference that you are seeing but I have seen cases where reports have been created with nearly one hundred variables which were being applied to various crosstab cells, lists, charts, conditional blocks, etc. This caused report rendering time to nearly double. If you have extensive conditional formatting then try removing it to check if this is the factor increasing the run time of the report.

    If all this doesn't help you locate performance issues then I'd recommend contacting IBM support. The will be able to run a PERF trace which will log all the activities with timing information to identify where the time is being spent for the report execution.
  • mgibson
    mgibson
    592 Posts

    Re: performance: Cognos taking much longer than SQL alone would suggest...

    ‏2011-03-16T23:06:08Z  
    There could be a few things happening here:

    1. You've taken the SQL from the query properties instead of using the Tools menu in Report Studio. This would give you an inaccurate view of the generated query because Cognos generates the query at run-time based on the items included in the report layout (not all of the items contained in the query).

    2. If you have multiple charts and query containers on the layout and you are using the same query reference for each container then you should note that sharing result sets is only applicable for list type queries with the same projected columns. If you have other query containers such as charts or crosstabs then each new query container will send a new query to the database. If you look at the SQL generated via the Tools menu you will see a query with multiple versions listed for the different containers on the layout. For example, a query named "Query1" which is used for a crosstab and a chart would show up as Query1.1 and Query1.2 in the generated SQL dialog. The multiple queries will execute in series by default but you can change them to execute concurrently using the properties of the query object (and some advanced settings listed in the Administration and Security Guide for more control of concurrency).

    3. Local processing could be taking place. The 24-minute SQL query may be creating a massive data set which will take a little while to transfer to the Cognos server. Additional time would then be required to do any of the local processing from this data set. You can try disabling local processing on the queries to get an indication of whether this might be a factor.

    4. Master-detail relationships may be issuing more queries than you expect. A detail query will typically be executed once for each master query record. This could multiply out the duration of the report execution. The only scenario where you can make use of a single result set for a master-detail relationship is when you have a list query inside another list query. In this case you can use the option to section the detail query (assuming that the master query is the same as the detail query) rather than creating an explicit data item link between the two query containers. There isn't much you can do to improve the performance if you are using master-detail relationships.

    5. Complex rendering may also add some overhead to report generation. Normally this would not account for the time difference that you are seeing but I have seen cases where reports have been created with nearly one hundred variables which were being applied to various crosstab cells, lists, charts, conditional blocks, etc. This caused report rendering time to nearly double. If you have extensive conditional formatting then try removing it to check if this is the factor increasing the run time of the report.

    If all this doesn't help you locate performance issues then I'd recommend contacting IBM support. The will be able to run a PERF trace which will log all the activities with timing information to identify where the time is being spent for the report execution.
    Hi Phil, thanks again for your help - my feedback is;

    1. Yes, I was certain to obtain the SQL from the Tools menu - so I'm certain its the correct SQL
    2. There are indeed multiple objects within the report that use the same base query, so it is running multiple queries sequentially. I'll try the concurrent setting and see if this helps.
    3. I'll try this also - but I fear it may produce an error because it may require local processing to use some of the functions
    4. Thankfully there is none of this
    5. The charting is only moderately complex, and I don't think is contributing much.

    I'll try items 2 and 3 and I'll let you know what happens!

    Regards
    Mick
  • mgibson
    mgibson
    592 Posts

    Re: performance: Cognos taking much longer than SQL alone would suggest...

    ‏2011-03-17T22:22:06Z  
    There could be a few things happening here:

    1. You've taken the SQL from the query properties instead of using the Tools menu in Report Studio. This would give you an inaccurate view of the generated query because Cognos generates the query at run-time based on the items included in the report layout (not all of the items contained in the query).

    2. If you have multiple charts and query containers on the layout and you are using the same query reference for each container then you should note that sharing result sets is only applicable for list type queries with the same projected columns. If you have other query containers such as charts or crosstabs then each new query container will send a new query to the database. If you look at the SQL generated via the Tools menu you will see a query with multiple versions listed for the different containers on the layout. For example, a query named "Query1" which is used for a crosstab and a chart would show up as Query1.1 and Query1.2 in the generated SQL dialog. The multiple queries will execute in series by default but you can change them to execute concurrently using the properties of the query object (and some advanced settings listed in the Administration and Security Guide for more control of concurrency).

    3. Local processing could be taking place. The 24-minute SQL query may be creating a massive data set which will take a little while to transfer to the Cognos server. Additional time would then be required to do any of the local processing from this data set. You can try disabling local processing on the queries to get an indication of whether this might be a factor.

    4. Master-detail relationships may be issuing more queries than you expect. A detail query will typically be executed once for each master query record. This could multiply out the duration of the report execution. The only scenario where you can make use of a single result set for a master-detail relationship is when you have a list query inside another list query. In this case you can use the option to section the detail query (assuming that the master query is the same as the detail query) rather than creating an explicit data item link between the two query containers. There isn't much you can do to improve the performance if you are using master-detail relationships.

    5. Complex rendering may also add some overhead to report generation. Normally this would not account for the time difference that you are seeing but I have seen cases where reports have been created with nearly one hundred variables which were being applied to various crosstab cells, lists, charts, conditional blocks, etc. This caused report rendering time to nearly double. If you have extensive conditional formatting then try removing it to check if this is the factor increasing the run time of the report.

    If all this doesn't help you locate performance issues then I'd recommend contacting IBM support. The will be able to run a PERF trace which will log all the activities with timing information to identify where the time is being spent for the report execution.
    Hi Phil,

    The changes seems to have had some effect, but I've made other changes to the queries which may also have helped.

    Part of the issue is caused by objects appearing on separate pages, so it will only render the last query when the user chooses to move to the second page, and therefore won't run all 4 queries concurrently.

    But perhaps you can help with a related question. After reading the doco, I found that I had to 'enable' the concurrent querying, so I entered these parameters in the 'Report Service' Advanced settings;

    Parameter                                        Value
    RSVP.CONCURRENTQUERY.NUMHELPERSPERPROCESS           4  
    RSVP.CONCURRENTQUERY.MAXNUMHELPERSPERREPORT         4  
    RSVP.CONCURRENTQUERY.ENABLEDFORINTERACTIVEOUTPUT    true
    

    Does this look correct?

    Regards
    Mick
    Updated on 2014-03-25T08:12:04Z at 2014-03-25T08:12:04Z by iron-man
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: performance: Cognos taking much longer than SQL alone would suggest...

    ‏2011-03-21T11:44:52Z  
    • mgibson
    • ‏2011-03-17T22:22:06Z
    Hi Phil,

    The changes seems to have had some effect, but I've made other changes to the queries which may also have helped.

    Part of the issue is caused by objects appearing on separate pages, so it will only render the last query when the user chooses to move to the second page, and therefore won't run all 4 queries concurrently.

    But perhaps you can help with a related question. After reading the doco, I found that I had to 'enable' the concurrent querying, so I entered these parameters in the 'Report Service' Advanced settings;

    <pre class="java dw" data-editor-lang="java" data-pbcklang="java" dir="ltr">Parameter Value RSVP.CONCURRENTQUERY.NUMHELPERSPERPROCESS 4 RSVP.CONCURRENTQUERY.MAXNUMHELPERSPERREPORT 4 RSVP.CONCURRENTQUERY.ENABLEDFORINTERACTIVEOUTPUT true </pre>
    Does this look correct?

    Regards
    Mick
    If you render the entire report to Excel or PDF output then the concurrency settings will help you with objects on different pages.

    For the concurrency settings, I think that the NUMHELPERPROCESS should be a larger than the MAXNUMHELPERSPERREPORT value. Otherwise you could have a single report which consumes all of the available concurrency threads and other reports running on the report process will not be able to run queries concurrently.
  • mgibson
    mgibson
    592 Posts

    Re: performance: Cognos taking much longer than SQL alone would suggest...

    ‏2011-03-21T21:28:35Z  
    If you render the entire report to Excel or PDF output then the concurrency settings will help you with objects on different pages.

    For the concurrency settings, I think that the NUMHELPERPROCESS should be a larger than the MAXNUMHELPERSPERREPORT value. Otherwise you could have a single report which consumes all of the available concurrency threads and other reports running on the report process will not be able to run queries concurrently.
    I've changed it - thanks again Phil.