Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Is there a way to avoid massive SQL with poorly written RPTS?

Status
Not open for further replies.

SimplyStu

IS-IT--Management
Aug 22, 2006
9
US
My DB folks are starting to blame CE for some massive drag. I think what is happening is some users are writing some "bad" reports that are eating some CPU time. These same reports are also My question is this. In CE is there a way to avoid this with some set of parameters? Limits? I see some, but not sure the best way to handle this.
 
A way to avoid what, the users building inefficient reports?

Sure, don't give them access...

Prior to a report being published it should pass some basic sanity checks, which sounds like your problem.

The biggest and most common performance drain is that the record selection formulas aren't getting passed to the database for processing.

As for a setting, not sure what kind of miracle setting you expected, perhaps you should elaborate.

-k
 
Hi,
In Crystal Enterprise you can limit the # of records returned, but that affects all reports.

As synapse noted, there is no magic way to compensate for poor design except not allowing reports to be published before undergoing efficiency tests..



[profile]

To Paraphrase:"The Help you get is proportional to the Help you give.."
 
Let me ask it a different way. The DBAs are "hinting" that CE is the problem and that CE (Crystal) is creating huge hits on the DB. Since the idea is to take pressure off us writing reports CE works well. I was thinking that the DBAs should be able to control a run away SQL on the Oracle side....BUT... they say they can't so I was actually trying to see if CE has a way to control this. I was hoping there was a parm in CE that only let a report run for 5 min. If it was longer it would kill the report. Or what about a control on the number of records it reads??? I'm just searching for a suggestions.
 
Hi,
as I posted:
In Crystal Enterprise you can limit the # of records returned, but that affects all reports.
Look in the CMC, Pageserver properties for 1 place.
BUT, again as synapse noted, that is the backend of the problem...
With Oracle,your DBA can also configure a profile for CE's connections that limits resources used ( like CPU time), but that is no substtute for preventing badly designed reports from being published in the first place.




[profile]

To Paraphrase:"The Help you get is proportional to the Help you give.."
 
Hi,

I have heard of third part software, enterprise inspector, via softwareforces, which enables you to search multiple reports for any attribute. One of the attributes is the SQL string, so the problem is having your DBAa provide you with the sql that is creating the problem, then locate the report and fine tune the sql. You can download a demo from their site and see...


Another option would be to enable Auditor on your system and determine which reports run the longest and start to analyze if the sql could be tuned....

Good Luck,

CE 10 Admin... ;)

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top