Problem with All Formats Except HTML

[Bonediggler]

Experts -

I have two identical queries which I am joining to create a 3rd query.  The amount of data in the root queries is very small (about 20 rows each).  The final query produces about 5 rows.  Running each query separately ("View Tabular Data") in HTML format takes one second.  Running the root queries in any other format takes the same amount of time.  Running the final query in any format besides HTML will take 2 hours.

This doesn't add up.  Any idea what is going on here?

Thank you!

[BigChris]

Do you get any clues from the SQL? Have you tried copying the SQL and running it on your database to see if you get the same performance issues?

[Bonediggler]

Hi Big Chris

I looked at the SQL - it seems fine.  Unfortunately I do not have privileges to run code directly against the database.

[cognostechie]

It's possible that the root queries are executed in the DB and that's why they are fast whereas the final query is executed locally in which case it can be slow. Also, Cognos does caching so when the final query is rendered in HTML, it does not require all of the data so it might be reading from a previously saved cache. Running any query in Excel/PDF requires full data extraction so it takes longer and in most cases it will also not read it from cache because the cache may not be for the full data (except when using DQM)

[Conan]

Cognostechie is right - One trick I have used to help speed up queries is to go into Query Explorer and go into the query properties and under Query Hints there is a "Use SQL With Clause". Set that to No on all 3 queries and retest. I find that sometimes this make quite a difference. Also are any of your source queries using a SQL object to pull data? I have seen situations where if one of the source queries is using SQL in a join and the SQL is not optimized that it can take an extraordinarily long time to run the report.  If this is the case then I am guessing one of the data sets has some null values and the join is behaving badly because of them.