Re: Slow Crystal Reports with Oracle

On Tue, 14 Oct 2003 22:47:12 GMT, kristoff plasun <a_at_a.com> wrote:

>I have a problem with a C++ DCOM application that
>prints Crystal Reports with data from Oracle.
>
>The SQL query is relatively complex but when
>the report is printed from the Crystal Reports
>designer it shows up very fast. When the report
>is printed from my application it takes about
>ten times as long to get the report to appear.
>
>When printing straight from the Crystal Reports
>designer Oracle uses about 15% of CPU cycles. When
>the report is printed from my application Oracle
>uses about 95% of CPU cycles, or sometimes
>totally uses it, while my application sits basically
>idle throughout the entire operation until it shows
>the Crystal Reports control to display the report.
>
>Any idea why Oracle would be working so hard when
>the report is printed from my application and not
>when the report is printed from the Crystal Reports
>designer? How does the Crystal Reports DLL communicate
>with Oracle?

G'day Kristoff,

Does the report use multiple tables and join them itself to produce the result? Or does it select from a view on Oracle? Does it call a stored procedure to do the work?

At a rough guess, it probably has a bunch of tables that it joins and then groups to produce output. IME, Crystal does a bad job of optimising the data access, so you are best off sorting that out on the Oracle side by creating either a view or a stored procedure that Crystal uses.

My preference is for the stored procedure, because you can then effect changes within Oracle, often without needing to change the Crystal report.

To see what it is doing, open the report in the Crystal designer and select Show SQL Query from the Database menu.

regards,
Ross.

--
Ross McKay, WebAware Pty Ltd
"Words can only hurt if you try to read them. Don't play their game" - Zoolander