ReportNet performane - SLOW!?

[greggranger]

Am in need of any information in regards to improving ReportNet performance. Using SQL Server 2000 db as the data source. Have one view as a fact table and several additional dimension tables (star schema design).

Any tips on how to improve performance is greatly appreciated...Thank you!

 

[KingCrab]

Ok the obvious, what are you doing when you say it is slow?

 

[greggranger]

Thanks for responding.

When running reports in Query/Report Studio it takes longer than expected to return results. When returning similar data using Query Analyzer the processing time is approx. 30 seconds, as compared to around 3 minutes with Report Studio.

I'm assuming the local processing is creating the increased processing time, but wanted to enquire of any performance tips others have utilized.

Any thoughts?

 

[xs4citrix]

What hardware is used? (cpu, memory etc)


Stefan

http://www.printingsupport.com

 

[greggranger]

Two, 3 Ghz Xeon Processors
4 GB RAM
3 disc partitions (C: 72 GB Raid 1, D: 98GB Raid 5, D: 312GB Raid 5)

Content store and source database are both located on D:

Queries run quickly when using SQL Query Analyzer but when running a similar query in Report Studio it's much slower.

 

[navshiv]

I don't know too much about this but I remember at my old place when we used ReportNet that we changed from CGI to ISAPI (can't remember the name of the setting) it improved the retrieval of data.

Does anyone know what settings I am talking about?!! I can't remember!

 

[KingCrab]

Are you saying that the SQL you get from a report create in Report Studio runs much faster in Query Analyzer?

I am talking like, copy the SQL from report studio and paste it into query analyzer.

It may be that the package(model) you have is incorrect.

 

[Draoued]

navshiv:
I think you are speaking about the web gateway.
It s true that using the proper gateway improves the response time in generating the HTML pages.

Isapi is the gateway type fine-tuned by cognos for IIS.
Nsapi is the gateway for Netscape Directory .
There are some other for Apache....

Cognos.cgi is a generic one which works with all Web servers, but the response time is not always here.

As a starting point for tuning I will go like KingCrab is saying checking if the SQL is correct.

the Web gateway is not query dependent.

 

[greggranger]

Pretty positive SQL is not the problem, but appreciate the troubleshooting recommendation.

In order to use ISAPI, what additional software is necessary?

 

[KingCrab]

So did you even try? You are basically shrugging off the suggestion of 2 people here.

Copy the SQL and run it from your workstation in Query Analyzer. See what the performance is like. Then if possible copy the same SQL and run it from your ReportnetServer and see what it does.

A can't imagine a 1000% decrease in spead between the CGI and ISAPI modules.

Anyways, open Config manager and change everything to isapi.

 

[greggranger]

I don't think it's fair to jump to that conclusion, KingCrab. Nothing in my response warranted that type of comment. In case you missed it, I thanked you for the recommendation.

Furthermore, where do you get the idea I'm looking for 100% increase in performance? Why even continue to respond to this thread if you think I'm "shrugging off [your] suggestion". Turn the egotism down a notch buddy.

As I stated earlier in the thread "When returning similar data using Query Analyzer the processing time is approx. 30 seconds, as compared to around 3 minutes with Report Studio", therefore, I already determined it is not the query, but thanks, again, for taking time to offer your heartfelt recommendation.

Can anyone provided me MEANINGFUL, non-vindictive type information in regards to switching from CGI to ISAPI?

THANKS!

 

[Draoued]

With a Cognos support valid account [url

http://support.cognos.com/supported/docs/en/reportnet/1.1mr2/inst_cr_winux.pdf

]try this link[/url] and then go to the page 117 : Change the reportnet Gateway.

That's it.

 

[NEAR]

A lot of things can affect performance within reportnet. One thing I found out was that if you are using multiple queries within several conditional blocks, all the queries will execute regardless of the condition. This is totally contrary to what Cognos support will tell you.

A good practice is to generate native SQL and test it with a 2nd party tool. Also very often in a development environment multiple developers may be testing reports at the same time. All it would take was for someone to cancel a report several times which could exhaust memory fast regardless of how much you have. This can especially have greater impact if the reports are complex.

Good luck and keep an open eye for the unexpected.

 

[veronicaq]

I had the same problem with performance when we first started wtih ReportNet and there ended up being a few things wrong:

I had not yet specified dimensional information. That made a HUGE difference.

Also, double check the SQL. If Cognos is generating overly verbose SQL, and you're using multiple "layers" in your Framework model, it can be the way your model.

Finally, improperly indexing for a star schema will kill your performance, especially if you're attempting SQL's version of partitioning.

 

[greggranger]

Could you provide more explanation regarding "specifying dimensional information" where is this done? Framework Manager?

The report I'm running is very basic and only pulling from one table in the schema, so I'm pretty sure the SQL is not the issue.

I'll look into indexing for star schema models.

Thank you veronicaq!