RSS Feed

Method R Discussion Group

We use Crystal as our reporting tool.  The user community doen't necessarily write efficient reports.  Can the profiler be used to look at the reports and come up with a better written report?

Any ad-hoc reporting tool - like Crystal - can make it difficult, if not nearly impossible, to provide high performance.  However, the best luck I had with Crystal was having my developers write stored procedures that returned common result sets needed for reports the users were creating.  We'd make sure these procedures were then selected as the data source for the reports vs. having the users build queries on the fly.  That fixed many of our performance issues since we could then properly write the SQL within the procedure.

Also, if users build reports in Crystal that they wish to use over and over, you can have your developers review the SQL that Crystal creates and convert it to a procedure that you can use the harness to help you test.  Again, this way, you write the query yourself and don't let Crystal do it "on the fly".  It's been my experience that Crystal doesn't generate very robustly performing SQL in general and replacing it with proceduralized result sets is often the only/best way to control performance.

The bottom line is that if you allow the users to write queries via Crystal, you're going to have trouble.  Do as much of the query-writing and development as you can and use the harness to help you test it.  That's your best bet.