Performance at report level

[rams143]

Hi All,

I have a report that has 5 queries and has some javascript to provide the tab interface in the report which is basically hiding and showing sections of the report by clicking on the tabs. The whole report uses couple of lists and mostly singletons to display the data. It was running fine till last week and was taking 10-15 secs to run the report. No idea what happened but started talking 1min 15 secs to run from past couple of days. As per I am concerned there are no changes in the DB or in the FM model. I recreated the queries in the query explorer but no improvement. All other reports using these queries are running fine. Even if I view the tabular form of the query it returns in seconds.

Any clues or thoughts from where I should start digging for the issue????

Thanks in advance.... very urgent...

[Prerna Chaudhary]

Hi,

Check query properties "Use local cache" is set to Yes or not
or unnecessary items are checked in List properties

"Use local cache"-Specifies whether a query is a candidate for query reuse. If set to Yes, the query engine can reuse an existing SQL result. If set to No, the query is executed rather than using cached results.

When query reuse is turned on (in Framework Manager or Report Studio) and you run your report for the first time, the result set is stored in the cache on the report server. From this moment on, each subsequent report execution will use the cache instead of sending a query request to the database.

The cache is stored for the duration of the user's session. The user's session ends if :

1.The user logs off on the Cognos portal
2.The user closes the Web browser without logging off
3.The session timeout, defined by the Web server or Cognos Configuration, has been reached

Steps: To enable the cache in a specific report:

1.Open the report in Report Studio
2.From the View menu, click Queries
3.Click the query for which to enable the use of local cache. Its properties appear in the Properties window
4.Find the property named Use Local Cache and set it to Yes to enable query caching

Hope this works for you :)

Thanks and regards
Prerna
---------------------------------------------------------------------------------------------------
pchaudhary@infocepts.com | www.infocepts.com
---------------------------------------------------------------------------------------------------

[rams143]

Hi Prerna,

Thanks for you response. I have the property Use local cache checked in governor settings in FM model and then it is set to default for the queries in the report. I guess that should be taking as YES by default but I tried to set it to yes and nothing is improved.... still taking 1+ min.

Thanks,
Rams.

[rams143]

Hi All,

I don't really understand whats happening but this is what we are experiencing.

I started recreating the report from scratch but it is still taking long time to run. The queries alone run faster and also if you build query studio report with all the query items used in the mail report that also runs fast..
Can anyone help me?????

Thanks,
Rams.

[blom0344]

You mentions no changes at the database. What do you mean? Same structure, same indices? Same amount of data?

If tabular queries are real quick, then execution plans are probably not the issue. You should check the BiBus process on the server when running a report. Does this start to grow very fast in size?
Check also for temp files written to the server. These may influence performance pretty badly.

The hard but proper way to isolate the cause is to simplify the report step by step till you find what is causing the perf. drag

[rams143]

Hi Blom,

Thanks for the response. When I say there were no changes in DB I meant to say YES same structure, same indices and same amount of data.

I checked the BiBus process on the server and looks like it doesn't go very fast in size while running the report. It grows gradually upto 200MB or so and there are no temp files return on the server. Our package is set to Database query processing...
I started recreating the report and looks like as I keep adding items(lists or singletons, etc.) into the report page it is slowing down the run time of the report.


Thanks,
Rams.

[rams143]

Hi All,

We enabled the performance trace in cognos and checked the xml file generated in the logs directory while running the report. We found that it is taking maximum time in the queries while performing "sqlPrepareWithOptions" .

Any clues on this???

Thanks,
Rams.

[rams143]

Finally got the culprit of the whole issue. It is Oracle VPD. We are using VPD for row level security in Oracle and for some reason the sql is taking long time to query the Active Directory tables for the user groups/accounts. Nothing wrong in the report.

Any ways thanks all.

Rams.

[bvk.cognoise]

Hi,

besides above instructions in query properties change Execution method property to concurrent.

thanks
bvk