report vs query performance issue

[tyody]

I'm having a performance issue running a report in CRN.  In CRN the report runs a long time (never comes back), but I can run the query in the database in 10-20 seconds.  There is only one query in the report with prompt filters, but the prompts wont even come up.  Any ideas?

[Declan]

Can't help but we our reports (CRN8.2) take ages (up to 10 mins) to show prompts which are driven off a 1000 row store dimension.  We have noticed that the more complex the report the longer it takes to show the prompts.  Does anyone ealse have any infor on this? ???

[BIsrik]

Plz check the sql of the query..its joins, its calculation, its filters, its indexes etc..One query can't take 10 minutes to prompt for a prompt page. can you provide us more information regd the prompt page..i.e..are there any value prompts used..and if so how are they been called in query..

Srik

[Darek]

If there is more than 2048 values for a single value list prompt, it will never come back, as there is a physical limit on HTML SELECT item. Keep it in mind, that ReportNet and Cognos 8, are HTML based application on the front-end.

[BIsrik]

But Cognos says 5000..whats that then??

Srik

[Darek]

Maybe it is 5000 ... But does it really matter after say a hundred or two?

[mikegreen]

Can't help but we our reports (CRN8.2) take ages (up to 10 mins) to show prompts which are driven off a 1000 row store dimension.  We have noticed that the more complex the report the longer it takes to show the prompts.  Does anyone ealse have any infor on this? ???

Cognos is probably doing a select distinct on column for the entire table... If you run SELECT DISTINCT column_name FROM table_name - how long does that take? Often, getting a list of distinct values in a large table can take longer than the actual query to get the data back (depending on indexing, etc).

Regards,

Mike