Slow Report - tips on where to start looking

[Robl]

I have a couple of queries that auto-group to return 20 rows each.
They take under a second to run.
Q1 processes a few thousand rows returning 20 aggregated rows.
Q2 has just 20 rows.
They are outer joined.

When I join them in report studio it takes 8 seconds to return data.

My best guess is that there's a bottleneck on the server local processing but I'm not sure where to start looking.
I'm wondering if anyone can make any suggestions.

Thanks.

[BigChris]

8 seconds is slow?!?  :o

If you run the SQL directly on the server do you get the same sort of "slow" performance?

[Robl]

The SQL is pretty much instant.
Even if I 'view tabular data' I get an instant response on the base queries.

But when I view on the joined quiries it goes up to 3 seconds, and then up to 8-10 on the report page with the conditional formats and suchlike.

I'm sure the delay is on the cognos server.

[Lynn]

The SQL is pretty much instant.
Even if I 'view tabular data' I get an instant response on the base queries.

But when I view on the joined quiries it goes up to 3 seconds, and then up to 8-10 on the report page with the conditional formats and suchlike.

I'm sure the delay is on the cognos server.

When you view tabular data you are not necessarily retrieving ALL the rows that the query will ultimately return, so don't confuse the instant response you see with what the total execution time ultimately will require.

When you join two queries in Report Studio the complete result sets of each must be retrieved and then put together locally. This is why the design of data warehouses and FM models is so important...we want to avoid local processing because it is generally bajillion+7 times more efficient to execute the desired query in the database and return the result set to Cognos.

Having said that, I wouldn't expect that a local join of two queries with 20 rows each would be horrible.

Can you tell if your local processing is bringing back the few thousand rows and aggregating on the Cognos side to get the resulting 20? Or is that aggregation happening on the database side? You can look at the generated SQL to help determine this.

Can you remove all the "conditional formats and suchlike" to help focus your troubleshooting to the right problem?

[Robl]

On the 'view tabular' it returns in no time and I can 'page down' instantly with no pause.
On the joined query it takes about 4 seconds for even the first page.
Without any formatting it'll do 4 seconds onto the regular page too.

So, instants dB results.
Slow query join.
Even slower page format.

Unfortunately on this contract I don't own the servers to poke around myself, so I need to take a list of things to check to the server admin team.

(As a sidebar - totally agree on the FM/DB comment. But unfortunately someone else designed and owns the database.
I am stuck somewhat in the middle. :) )

[Lynn]

On the 'view tabular' it returns in no time and I can 'page down' instantly with no pause.
On the joined query it takes about 4 seconds for even the first page.
Without any formatting it'll do 4 seconds onto the regular page too.

So, instants dB results.
Slow query join.
Even slower page format.

Unfortunately on this contract I don't own the servers to poke around myself, so I need to take a list of things to check to the server admin team.

(As a sidebar - totally agree on the FM/DB comment. But unfortunately someone else designed and owns the database.
I am stuck somewhat in the middle. :) )

I hear ya....

I'm still not convinced about your assessment of instant db results. If you have to page down then how many pages are in the result set...how many rows in your result set? How big is that result set and how long does it take to generate the entire set? You can run to Excel or PDF to see this since those must have the complete result before rendering. Have you looked at the SQL to see where aggregation is occurring?

The joined query is also returning more than one page? How big is that result set? You've described 20 rows in two queries that get joined so that doesn't sound like more than could fit on a page. What are we missing in your explanation?

What do you mean by "the regular page"?

[Robl]

OK, let me try again.
1 query with 20 rows, on a screen based list it returns instantly
1 query with 200 rows, on a 200 row list it returns instantly.
Same query on a 20 row x 10 column crosstab it returns instantly.
I've made sure I'm returning the full result set.

When I join the two queries it takes 4 seconds.
When I add 4 conditional formats it takes 8-10 seconds.

When the Cognos server does nothing but run SQL and put the results on a page it's quick.
But as soon as it tries to do any sort of local processing is slows down.

If you can think of any other tests to check for bottleneck locations that would be great.

[sunny bachan prasad]

Hi Robl,
By looking in the discussion it seems you are repeating your.
Lynn asked you about the query when you are running the report but there is no any information regarding that.
So can you please give proper information regarding the generated query so that we can forward this discussion.

Thanks,
Sunny

[Robl]

I'm not really sure what to tell you about the queries.
There is nothing special about them, a few facts and a few dims selected.
A couple of filters.

They run instantly on the Cognos server.
The SQL runs instantly on the database.
That is to say the full result set returns instantly.

I've made sure all the aggregations and groupings happen DB server side.

Just to make sure I've created whole new queries from small tables and joined them - I get the same results when I join them or apply conditional formats.

[navissar]

A few ideas:
1. Is there a reason you are joining them locally? You can conduct the following experiment to see whether the join would work better on DB side: Create a view in the database which contains the results of your join, and create a report that just pulls the result of the view into a list. If it returns instantly on Cognos side, that means you probably have a local processing problem with the join, in which case you should alter your model to ensure joins are done on DB side. If it returns in 3-4 seconds or more, it is the join that's problematic - I'd try creating a materialized/indexed view for Q1, and index the join column.

2. Because you describe slowness in conditional styles rendering, my first suspects, in as much as there is anything wrong with the server (HTML doesn't render in 0 seconds, unfortunately), would be memory and/or network. Try having MIS monitor memory consumption and network I/O when the report runs.

[CognosPaul]

Is it still slow if you remove the local join and use conditional formatting?

[Robl]

If I remove the joins and just do a simple report in html it's almost instant.
Even with a bunch of graphics and HTML tags on the page it's instant.

If I add conditional formatting but no joined queries then it slows down to around 4-5 seconds.

[CognosPaul]

What kind of conditional formatting? Can you post the XML?

[Robl]

What kind of conditional formatting? Can you post the XML?

Can't post XML I'm afraid.
The formats are just basic stuff like;
Value >= target

[intrepid402]

Not sure if this might help but I tried this method when I had two queries and in a master detail relationship report, there was a performance hit... There was a method to force single database query from two queries. Take a look at this and see any of the "tricks" from this example can be applied on yours

http://www.ibm.com/developerworks/data/library/cognos/reporting/performance_and_tuning/page582.html