Performance issue when a value prompt is added

[fml55555]

Hi Gurus,

I have 4 barcharts on a single page. I have added a value prompt which gives users ability to see the charts filtered for various lines of business. The charts were all processing almost instantlybefore I added the value prompt (The values were for all the lines together). However, it is taking long time to process now.

->It takes a long time to run when I initially run the report
-> Each time I change the value prompt to another line of business, again it take a long time

My questions are

1) How can I improve the timing of the first time run (right now takes about 2 mins, I would like to keep it below 30 seconds)
2) How can I avoid processing time for all other changes in the Business Line changes (Considering all data is in there already)

Thank you!

[MFGF]

It sounds a lot like the item you are filtering on is not indexed, and it is taking the database a long time to locate the desired rows of data. Is it a description? If so, is there a corresponding code you could filter on that might be more efficient? You could change the prompt to have the code as the Use Value and the description as the Display Value.

I might be completely wrong - it's just an idea...

MF.

[bdbits]

MFGF is right you should look at indexing, but off the top of my head I will add a few other things to consider.

• What kind of model - relational or dimensional?
• Do you have any caching enabled?
• Are you running under DQM?
• Have you looked at the generated SQL to see if efficient SQL is being generated?
• Is local processing being used, or are your filters reflected in SQL and pushed to the server (usually more efficient)?
  
• How many rows are in the result sets?

[fml55555]

MFGF

Thank you so much for your reply, I tried to use the primary key as use value, but it did not create much of a difference in processing time

bits

This is a relational model

Yes I have caching enabled. The wired thing is that once the report runs(lets say for Business line xx). If I choose the xx business line xx again, it gives me the result almost immediately. If I close the report and start the process again, it runs for a while again.

The sql is efficient

The processing is local (I have no idea how to reflect filters in SQL)

There are not many. When I run tabular data for individual queries, it takes less than 10 seconds for each of them

Thank you very much

[MFGF]

MFGF

Thank you so much for your reply, I tried to use the primary key as use value, but it did not create much of a difference in processing time

bits

This is a relational model

Yes I have caching enabled. The wired thing is that once the report runs(lets say for Business line xx). If I choose the xx business line xx again, it gives me the result almost immediately. If I close the report and start the process again, it runs for a while again.

The sql is efficient

The processing is local (I have no idea how to reflect filters in SQL)

There are not many. When I run tabular data for individual queries, it takes less than 10 seconds for each of them

Thank you very much

Sounds like you are using CQM if closing the report and starting again re-introduces the lag?

Is the primary key indexed? You said you tried it as the Use Value. I guess this is from the table driving the prompt values? What about the table used in the main query of the report - I assume it can't be the PK there? How is it indexed in that table?

MF.

[bdbits]

By "filters reflected in SQL" I just meant checking that your filters are seen in where clauses in the SQL. Sorry if that was not clear.

MFGF is referring to the indexing in the database itself, something completely outside of Cognos. If you are not the DBA, ask whoever it is what columns have been indexed on the tables you are using. If the columns in your filters are not indexed, ask him if he can add the indexes. It may make a dramatic difference.

The fact that performance is great as you change the prompt selections shows that the cacheing is working. The issue is most definitely query performance.