Performance issue with report

[Daljith]

There is a view behind a report, which when executed directly in SQL window of Prod environment returns 500K records in 10 min.
But when the report is executed which is using this view, it takes 2 hours to complete.
How to debug why Cognos is taking that time to execute the report because Database direct query is running very fast. It seems some problem at Cognos.
Can someone suggest what can be checked to understand where is the issue?

[MFGF]

There is a view behind a report, which when executed directly in SQL window of Prod environment returns 500K records in 10 min.
But when the report is executed which is using this view, it takes 2 hours to complete.
How to debug why Cognos is taking that time to execute the report because Database direct query is running very fast. It seems some problem at Cognos.
Can someone suggest what can be checked to understand where is the issue?

It could be any number of things here. We don't know what you are doing in your report. It's entirely possible you are doing things that result in local processing on the Cognos server, so it may be that the query returns the data to the Cognos server quickly but the rest of the time is spent in local processing? What happens in you build a new list report containing no calculations, sorting, grouping, summaries etc based on just the data from this view, then turn off the auto group and aggregate property on in the query properties? Does it run quickly?

MF.

[Daljith]

Hi MF,

Thanks for reply. This is a simple query with 5 columns and pulling 500K of data from DB2 database. There is no aggregation involved in it. I tried some of the changes as you mentioned above, but its the same thing, the report is not working even if I filter out all the columns and just keep one column. So, My question is where do I check what might be happening. There is not option of enabling trace at JDBC level currently because the cognos services cannot be bounced. If I check the view history option I just see its executing the query. Nothing else. So, looking for what other options are available for me to understand where is the issue? In case suggestion more options, that would be very helpful.

[MFGF]

...There is no aggregation involved in it...

Hi,

Aggregation happens automatically unless you turn it off in the properties of the query within the report. Have you set the Auto Group and Summarize property to 'No'?

MF.

[Lynn]

Did you look at the generated SQL to see if it is comparable to the SQL you are running directly? Can you compare the generated native SQL with the generated Cognos SQL to see if there are any differences? That can sometimes give an idea of what is happening locally. Are you trying to render in a particular format?

500K rows is going to be a rather large list, so perhaps a different tool that is intended for ETL or data dump purposes might be better suited than a BI tool.

[Daljith]

Thanks for more inputs.

Actually, what we noticed is that the query once fired on database, its running continuously. We also tried adding a filter with 100 rows then the report works fine. But the moment we add filter with 1000 rows, the report is just stuck. God knows where..........................  :-\
Then when we are checking with DBAs, they are telling that they cannot tell us why in database the query is taking time. They are suggesting us to tune the query.

I am looking into tuning the query.

[MFGF]

Thanks for more inputs.

Actually, what we noticed is that the query once fired on database, its running continuously. We also tried adding a filter with 100 rows then the report works fine. But the moment we add filter with 1000 rows, the report is just stuck. God knows where..........................  :-\
Then when we are checking with DBAs, they are telling that they cannot tell us why in database the query is taking time. They are suggesting us to tune the query.

I am looking into tuning the query.

To echo Lynn above, did you look at the generated SQL to see what it is doing? Does the generated native SQL look similar to how you would expect?

MF.

[Daljith]

Hi Lynn and MF,

The native query and generated query both are same. Apart from that,  I did below settings as well to check if it is impacting performance:

Auto Agggregation - No
Auto Sort - No
Processing - Database only
Execution optimization - All rows

It works fine but the moment I change the filter from 100 rows to 1000 rows.......... the query seems lost somewhere for hours ...... :(

[MFGF]

Hi Lynn and MF,

The native query and generated query both are same. Apart from that,  I did below settings as well to check if it is impacting performance:

Auto Agggregation - No
Auto Sort - No
Processing - Database only
Execution optimization - All rows

It works fine but the moment I change the filter from 100 rows to 1000 rows.......... the query seems lost somewhere for hours ...... :(

Just a quick sanity check - are you using a relational package (query subjects and query items) or a dimensional package (dimensions, hierarchies, levels, members, attributes etc)?

What is the syntax of your filter?

MF.

[Daljith]

Hi MF,

Its a relational package and the filter I am adding to the end of query is "fetch 100 rows only".

[Lynn]

Hi MF,

Its a relational package and the filter I am adding to the end of query is "fetch 100 rows only".

Does this mean you have hard coded SQL in your query?  :o

[sunosoft]

There can be the case of more time is being taken to transfer data from DB server to cognos server over the network. Your DBA team can find this. They can find when the query is completed at DB end and whats happening with it. They can easily find that query is completed and data is being transferred to cognos server.

[Francois51]

If it can help, it's just a guess because I'm not familiar at all with DB2 but if it were an oracle database I'd ask the DBA to check if the explain plan of the query is the same when you filter your query with 100 or 1000 rows.

If the statistics are misleading the optimizer, or if there is a phenomena like 'bind peaking' the plan can change drasticly and the query can last forever whereas it was taking seconds before.

[Daljith]

Hi MF, SK and Francois,

Thanks to all of you for valuable inputs. The query is with the DBAs currently for tuning. I guess once they send us the tuned query, I'll be trying to run the report again.
For me it was real surprise that the report was working fine till 02-Jan and suddenly after 3 weeks the query is not responding. There is no much difference in the volume of data though...... :(
I also feel explain plan might help to understand what is wrong with the query.

Regards
Ana

[Michael75]

the report was working fine till 02-Jan

This snippet of info opens up another possible explanation. Do you have partitioning by year in your DB? For how long had the report previously been running with acceptable performance?

[Daljith]

Hi Michael,

As this is a monthly report, so we have been executing it at the beginning of every month from past 5 months. Every time, it has finished in minutes 10 and maximum 30 minutes.
The query is suppose to bring some 500K records.  There is no partition of tables yet at the database level (Database used DB2). DB team confirmed they did not create any extra index with in past 5 months on this table. So basically there is no change at database structural level apart from the new data that inserted to the table.

[Blacksails]

I have a similar situation...same query (native and cognos sql) simple list.simple date filter.no aggregation on list and query.no ordering.but report is rendering much more slower than thr query on netezza.why ?network problems?report service not optimized??thanks